How to show all privileges from a user in oracle?

Question

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

Answer 1

Try using the below code for views.

SELECT * FROM USER_SYS_PRIVS; 

SELECT * FROM USER_TAB_PRIVS;

SELECT * FROM USER_ROLE_PRIVS;

DBAs and other users can find the privileges granted to other users with the DBA_ versions of these same views. This documentation covers all the information.

But, these views only include the privileges that are granted directly to the user. 

To find all the privileges, including those granted indirectly through roles, will require more complicated recursive SQL statements like this:

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

1 .To view directly granted system privileges:

 SELECT * FROM USER_SYS_PRIVS;

2.For object privileges:

SELECT * FROM USER_TAB_PRIVS;

3. To check roles assigned to the user:

SELECT * FROM USER_ROLE_PRIVS;

For a comprehensive view, including indirect privileges through roles, use the DBA_ views with recursive SQL queries:

SELECT * FROM dba_role_privs CONNECT BY PRIOR granted_role = grantee START WITH grantee = '&USER';

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');

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');