How to show all privileges from a user in oracle?

Question

Can someone tell me how to show all privileges / rules from a specific user in the sql console?

Answer 1

You can try these views below.

SELECT * FROM USER_SYS_PRIVS; SELECT * FROM USER_TAB_PRIVS; SELECT * FROM USER_ROLE_PRIVS; 

Database administrators and other advanced users can find privileges granted to other users with DBA_ versions of the same views. They are described in the documentation .

These views show only the privileges granted to the user. Searching for all privileges, including those granted indirectly through roles, requires more complex recursive SQL statements:

 select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3; select * from dba_sys_privs where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3; select * from dba_tab_privs where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4; 

Answer 2

There are various scenarios floating around that will do this depending on how crazy you want to get. I would personally use the Pete Finnigan find_all_privs script .

If you want to write it yourself, the query becomes quite complicated. Users can be granted system privileges that are visible in DBA_SYS_PRIVS . They may be granted object rights that are visible in DBA_TAB_PRIVS . And they can be given roles that are visible in DBA_ROLE_PRIVS (roles can be by default or not by default and may also require a password, therefore, just because a user is given a role, this does not mean that the user can necessarily use privileges that he acquired by default roles). But these roles can, in turn, receive system privileges, object privileges, and additional roles that can be viewed by viewing ROLE_SYS_PRIVS , ROLE_TAB_PRIVS and ROLE_ROLE_PRIVS . The Pete script scans this relationship to show all privileges that are ultimately transferred to the user.

Answer 3

Another useful resource:

http://psoug.org/reference/roles.html

• DBA_SYS_PRIVS
• DBA_TAB_PRIVS
• DBA_ROLE_PRIVS

Answer 4

You can use the code below to get a list of all privileges from all users.

 select * from dba_sys_privs