0 votes
1 view
in SQL by (22.4k points)

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

1 Answer

0 votes
by (40.3k points)

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

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 31, 2019 in SQL by Tech4ever (22.4k points)
0 votes
1 answer
asked Jul 29, 2019 in SQL by Tech4ever (22.4k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...