I'm sure this has been asked before but I can't seem to find the relevant details for the following.
Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it is limited and does not meet all my needs), if not does anyone have some queries for answering the following?
- List all users who have been assigned a particular role?
- List all roles given to a user?
- List all privileges given to a user?
- List which tables a certain role gives SELECT access to?
- List all tables a user can SELECT from?
- List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.
Best Answer
List all users who have been assigned a particular role
List all roles given to a user
List all privileges given to a user
Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
List which tables a certain role gives SELECT access to?
List all tables a user can SELECT from?
List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.