总结工作中经常用到一些用户管理随笔记录：
1、查看数据库里所有用户各自拥有的角色：
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 ) a where a.granted_role=’DBA’;
5、查看拥有SELECT ANY TABLE权限的用户对于授予角色的，，看看都谁有查询所有表的权限select distinct rp.grantee from dba_role_privs rp ,dba_sys_privs spwhere rp.granted_role = sp.granteeand sp.privilege like ‘SELECT ANY TABLE%’
参考文章地址：
切忌贪婪，恨不得一次玩遍所有传说中的好景点，
