Search code examples
sqloracle-databaserulesprivileges

How to show all privileges from a user in oracle?

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

Solution

  • You can try these below views.

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

    DBAs and other power users can find the privileges granted to other users with the DBA_ versions of these same views. They are covered in the documentation .

    Those views only show the privileges granted directly to the user. Finding all the privileges, including those granted indirectly through roles, requires more complicated 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;