DBA privileges information

Question

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.

Answer 1

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

Answer 2

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

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

and

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

and

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 
table_name='OBJECT'
order by 1,2;

Answer 3

*_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