I'm trying to solve a question, but I don't have any idea about how I can get the solution.
IT Audit is asking for those users who has permissions over a database.
I began to write a python script which connects with impala and list all roles, and one by one I'm asking if that role has permission on the database, but I don't know how can I list all users that have each role assigned.
Anyone know how can I get that information?
I'm considering the roles you have mentioned as Sentry roles.
Sentry works this way -
1. A Sentry role [R1] with some privileges (say - access to database) assigned.
2. This role is assigned to a group [G1].
3. Users [U1, U2, U3] who are a part of the group [G1] can access the database.
If you want to know the users who have access to the database, you need to find out the group [G1] that has been assigned with the Sentry role [R1] with privileges to access the database.
To list all the roles assigned to the given group name (only allowed for Sentry admin users and others users that are part of the group specified by group name):
"SHOW ROLE GRANT GROUP group name;"
Once you do that, you will have groups and the corresponding roles. Pick the group [G1] linked to Sentry role [R1] that has the privilege to access the database. The users in the group [G1] are the ones you are looking for.
Hope that helps!