My colleague having DBA ROLE has created a user for me with the following role. CONNECT,DBA,RESOURCE from his own user.

Then I login and check the detail by using the below query.

select * from DBA_ROLE_PRIVS where grantee = 'MURAHMAN';

It's fine and retrieve all the above three roles, but when I query the table dba_tab_privs.

select distinct grantee as "User_Name" from dba_tab_privs; 

This does not retrieve my user.

Then I run the below query.

SELECT distinct username, account_status,privilege,owner
 FROM dba_users 
   left outer join dba_tab_privs 
   on (dba_users.username = dba_tab_privs.grantee) 
where dba_users.account_status = 'OPEN'; 

I saw the privilege column against my user is null? So, could you please help to find the privilege information against each user? Also please explain why the privilege column is null against my user? All other user have the privilege information.


The DBA role includes system privileges (like SELECT ANY TABLE), not object privileges.

| improve this answer | |
  • Dear Balazs thank you, So could you please tell me how to retrieve the system and object privileges of all DB users? – Rahman Khost Jul 6 '17 at 8:10

Hi you can get the privs you need with queries like these:

select privilege, admin_option from dba_sys_privs
where grantee='USERNAME';


select granted_role, admin_option from dba_role_privs
where grantee='USERNAME';


select owner, table_name, grantor, privilege from dba_tab_privs
where grantee='USERNAME';

and for privs granted through roles:

select grantee, owner, table_name, privilege from dba_tab_privs
where grantee in 
  (select granted_role from dba_role_privs
   where grantee='USERNAME')
order by 2,3;

and for privs on an object:

select owner, grantee, grantor, privilege from dba_tab_privs a, dba_roles b 
where a.grantee=b.role and 
order by 1,2;
| improve this answer | |
  • Thank you, It means i don't have object privileges. – Rahman Khost Jul 6 '17 at 10:55

*_TAB_PRIVS, *_SYS_PRIVS, and *_ROLE_PRIVS shows direct grants. But if you have inherited a priv via a role (or via a role, granted to a role, granted to a role ....) you need to dig deeper. There is a classic script for this, written by Pete Finnigan. http://www.petefinnigan.com/find_all_privs.sql

| improve this answer | |
  • I need the information in this format: UserName|AccountStatus|OWNER|PRIVILIEGE|ROLE. The Privilege is System And Object Privilege: – Rahman Khost Jul 9 '17 at 10:10
  • That's an over-simplified format for what is a complex relationship. Sys privs don't have owners. A given priv may be granted directly, not through a role. Or it may come through a role that was granted to a role that was granted to a role that was granted to a role that was granted to the user. If you want to know everyone that has a particular priv, go back to Pete Finnigan's site and look for the 'who_has' script. – EdStevens Jul 9 '17 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.