ORACLE 11g用户权限管理查询
时间：2021-07-01 10:21:17
帮助过：13人阅读
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、查看数据库所有用户的系统权限： 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、查看数据库所有用户的表权限 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、如何查看拥有DBA角色的用户？常规做法，一般是直接查询DBA_ROLE_PRIVS视图。查询语句如下： select grantee,granted_role from dba_role_privs where granted_role=’DBA’; //这会漏掉用户正确的查询应按如下语句查询：select * from (select distinct connect_by_root grantee username,granted_role from dba_role_privs connect by prior granted_role =grantee ) awhere a.granted_role=’DBA’;5、查看拥有SELECT ANY TABLE权限的用户对于授予角色的，看看都谁有查询所有表的权限 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%‘ORACLE 11g用户权限管理查询
标签：角色 style roo tab 查询 ted ant and 11g
