| |
Summing up the work often use some Oracle 11g user rights management study notes:
1 View all database users each have a role:
select * from (select distinct connect_by_root grantee username, granted_role
from dba_role_privs
connect by prior granted_role = grantee) a
where exists (select 1 from dba_users b where b.username = a.username)
order by 1,2
;
2, view the database system privileges for all users:
select d.username, d.privilege from
(Select a.username, b.privilege from
(Select distinct connect_by_root grantee username, granted_role
from dba_role_privs
connect by prior granted_role = grantee) a,
(Select grantee, privilege from dba_sys_privs) b
where a.granted_role = b.grantee
union
select grantee, privilege from dba_sys_privs) d
where exists ((select 1 from dba_users c where d.username = c.username))
order by 1,2;
3, view the database table permissions for all users
select d.username, d.privilege, d.owner, d.table_name from
(Select a.username, b.privilege, b.owner, b.table_name from
(Select distinct connect_by_root grantee username, granted_role
from dba_role_privs
connect by prior granted_role = grantee) a,
(Select grantee, owner, table_name, privilege from dba_tab_privs) b
where a.granted_role = b.grantee
union
select grantee, privilege, owner, table_name from dba_tab_privs) d
where exists ((select 1 from dba_users c where d.username = c.username))
order by 1,2;
4, how to view the user has the DBA role?
Conventional practice is generally directly query DBA_ROLE_PRIVS view. Query as follows:
select grantee, granted_role from dba_role_privs where granted_role = 'DBA';
This user will miss the test as follows:
Correct query statement queries shall be as follows:
select * from (select distinct connect_by_root grantee username, granted_role
from dba_role_privs
connect by prior granted_role = grantee) a
where a.granted_role = 'DBA';
5, view the user has the SELECT ANY TABLE privilege
For grant role, look at who has the authority to query all the tables
select distinct rp.grantee from dba_role_privs rp, dba_sys_privs sp
where rp.granted_role = sp.grantee
and sp.privilege like 'SELECT ANY TABLE%' |
|
|
|