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.

3 Answers 3


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

  • Dear Balazs thank you, So could you please tell me how to retrieve the system and object privileges of all DB users? Jul 6, 2017 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;
  • Thank you, It means i don't have object privileges. Jul 6, 2017 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

  • I need the information in this format: UserName|AccountStatus|OWNER|PRIVILIEGE|ROLE. The Privilege is System And Object Privilege: Jul 9, 2017 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, 2017 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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