Oracle 用户、角色权限管理上的几个误区
如果你想查看数据库中哪些用户具有修改SCOTT.DEPT表的权限，你会怎么查？
答：select * from dba_tab_privs where OWNER='SCOTT' and TABLE_NAME='DEPT';
这个语句结果是错误的.......如果角色A，有修改此表的权限，所有具有角色A的用户都被漏掉了。
如果你想查看哪些用户具有DBA权限，你会怎么查？你确定查询结果是正确的？
答：select grantee,granted_role from dba_role_privs where granted_role='DBA';
这个语句结果是错误的......，具体在第三部分测试。
如果你将CONNECT、RESOURCE角色授予某个用户，同时授予SELECT ON SCOTT.DEPT的权限，该如何查看这个用户拥有的权限明细？
答：不好查。
如何对比两个用户的表级权限有无差异？
答：select * from dba_tab_privs where grantee='A' ; select * from dba_tab_privs where grantee='B' ;一看就出来了。
这个语句结果是错误的，如果将角色C授予A，角色D授予B。dba_tab_privs中都不会有记录。如果角色C、D权限不一致，问题就出来了。
看完本文，你就明白为什么了。呵，相信本文可以让您对Oracle的权限管理更加得心应手。
一、通用查询
Oracle目前没有提供查看某个用户所有角色或所有权限的视图。要查看这类信息，一般通过如下SQL语句：
SQL> select count(*) from session_privs; COUNT(*)---------- 202 SQL> select count(*) from session_roles; COUNT(*)---------- 20 SQL>
这两个视图，只能查看当前连接用户拥有的权限和角色。
DBA如果需要查看其它用户的权限、角色，必须切换用户登入，再执行上述SQL。
二、推荐的查询实例
因近期工作需要，整理了下用户、角色信息的查看语句，在这里分享下：
1、查看数据库里所有用户各自拥有的角色：
SQL> 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 ; USERNAME GRANTED_ROLE------------------------------ ------------------------------......(省略部分)
FLOWS_030000 CONNECTFLOWS_030000 HS_ADMIN_ROLEFLOWS_030000 RESOURCEFLOWS_030000 SELECT_CATALOG_ROLESCOTT CONNECTSCOTT RESOURCETEST DATAPUMP_EXP_FULL_DATABASETEST DATAPUMP_IMP_FULL_DATABASETEST DBATEST DELETE_CATALOG_ROLETEST EXECUTE_CATALOG_ROLETEST EXP_FULL_DATABASETEST GATHER_SYSTEM_STATISTICSTEST HS_ADMIN_ROLETEST IMP_FULL_DATABASETEST JAVA_ADMINTEST JAVA_DEPLOYTEST OLAP_DBATEST OLAP_XS_ADMINTEST SCHEDULER_ADMINTEST SELECT_CATALOG_ROLETEST WM_ADMIN_ROLETEST XDBADMINTEST XDB_SET_INVOKER
......省略部分SQL>
2、查看数据库所有用户的系统权限：
SQL> 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; USERNAME PRIVILEGE------------------------------ ----------------------------------------......省略部分
FLOWS_030000 ALTER DATABASEFLOWS_030000 ALTER SESSIONFLOWS_030000 ALTER SYSTEMFLOWS_030000 ALTER USERFLOWS_030000 CREATE ANY CONTEXTFLOWS_030000 CREATE CLUSTERFLOWS_030000 CREATE DIMENSIONFLOWS_030000 CREATE INDEXTYPEFLOWS_030000 CREATE JOBFLOWS_030000 CREATE MATERIALIZED VIEWFLOWS_030000 CREATE OPERATORFLOWS_030000 CREATE PROCEDUREFLOWS_030000 CREATE PUBLIC SYNONYMFLOWS_030000 CREATE ROLEFLOWS_030000 CREATE SEQUENCEFLOWS_030000 CREATE SESSIONFLOWS_030000 CREATE SYNONYMFLOWS_030000 CREATE TABLEFLOWS_030000 CREATE TABLESPACEFLOWS_030000 CREATE TRIGGERFLOWS_030000 CREATE TYPEFLOWS_030000 CREATE USERFLOWS_030000 CREATE VIEWFLOWS_030000 DROP PUBLIC SYNONYMFLOWS_030000 DROP TABLESPACEFLOWS_030000 DROP USERFLOWS_030000 EXECUTE ANY PROCEDUREFLOWS_030000 SELECT ANY TABLEFLOWS_030000 UNLIMITED TABLESPACESCOTT CREATE CLUSTERSCOTT CREATE INDEXTYPESCOTT CREATE OPERATORSCOTT CREATE PROCEDURESCOTT CREATE SEQUENCESCOTT CREATE SESSIONSCOTT CREATE TABLESCOTT CREATE TRIGGERSCOTT CREATE TYPESCOTT UNLIMITED TABLESPACETEST ADMINISTER ANY SQL TUNING SETTEST ADMINISTER DATABASE TRIGGERTEST ADMINISTER RESOURCE MANAGERTEST ADMINISTER SQL MANAGEMENT OBJECTTEST ADMINISTER SQL TUNING SETTEST ADVISORTEST ALTER ANY ASSEMBLYTEST ALTER ANY CLUSTERTEST ALTER ANY CUBETEST ALTER ANY CUBE DIMENSIONTEST ALTER ANY DIMENSIONTEST ALTER ANY EDITIONTEST ALTER ANY EVALUATION CONTEXTTEST ALTER ANY INDEXTEST ALTER ANY INDEXTYPETEST ALTER ANY LIBRARYTEST ALTER ANY MATERIALIZED VIEWTEST ALTER ANY MINING MODELTEST ALTER ANY OPERATORTEST ALTER ANY OUTLINETEST ALTER ANY PROCEDURETEST ALTER ANY ROLETEST ALTER ANY RULETEST ALTER ANY RULE SETTEST ALTER ANY SEQUENCETEST ALTER ANY SQL PROFILETEST ALTER ANY TABLETEST ALTER ANY TRIGGERTEST ALTER ANY TYPETEST ALTER DATABASETEST ALTER PROFILETEST ALTER RESOURCE COSTTEST ALTER ROLLBACK SEGMENTTEST ALTER SESSIONTEST ALTER SYSTEMTEST ALTER TABLESPACETEST ALTER USERTEST ANALYZE ANYTEST ANALYZE ANY DICTIONARYTEST AUDIT ANYTEST AUDIT SYSTEMTEST BACKUP ANY TABLETEST BECOME USERTEST CHANGE NOTIFICATIONTEST COMMENT ANY MINING MODELTEST COMMENT ANY TABLETEST CREATE ANY ASSEMBLYTEST CREATE ANY CLUSTERTEST CREATE ANY CONTEXTTEST CREATE ANY CUBETEST CREATE ANY CUBE BUILD PROCESSTEST CREATE ANY CUBE DIMENSIONTEST CREATE ANY DIMENSIONTEST CREATE ANY DIRECTORYTEST CREATE ANY EDITIONTEST CREATE ANY EVALUATION CONTEXTTEST CREATE ANY INDEXTEST CREATE ANY INDEXTYPETEST CREATE ANY JOBTEST CREATE ANY LIBRARYTEST CREATE ANY MATERIALIZED VIEWTEST CREATE ANY MEASURE FOLDERTEST CREATE ANY MINING MODELTEST CREATE ANY OPERATORTEST CREATE ANY OUTLINETEST CREATE ANY PROCEDURETEST CREATE ANY RULETEST CREATE ANY RULE SETTEST CREATE ANY SEQUENCETEST CREATE ANY SQL PROFILETEST CREATE ANY SYNONYMTEST CREATE ANY TABLETEST CREATE ANY TRIGGERTEST CREATE ANY TYPETEST CREATE ANY VIEWTEST CREATE ASSEMBLYTEST CREATE CLUSTERTEST CREATE CUBETEST CREATE CUBE BUILD PROCESSTEST CREATE CUBE DIMENSIONTEST CREATE DATABASE LINKTEST CREATE DIMENSIONTEST CREATE EVALUATION CONTEXTTEST CREATE EXTERNAL JOBTEST CREATE INDEXTYPETEST CREATE JOBTEST CREATE LIBRARYTEST CREATE MATERIALIZED VIEWTEST CREATE MEASURE FOLDERTEST CREATE MINING MODELTEST CREATE OPERATORTEST CREATE PROCEDURETEST CREATE PROFILETEST CREATE PUBLIC DATABASE LINKTEST CREATE PUBLIC SYNONYMTEST CREATE ROLETEST CREATE ROLLBACK SEGMENTTEST CREATE RULETEST CREATE RULE SETTEST CREATE SEQUENCETEST CREATE SESSIONTEST CREATE SYNONYMTEST CREATE TABLETEST CREATE TABLESPACETEST CREATE TRIGGERTEST CREATE TYPETEST CREATE USERTEST CREATE VIEWTEST DEBUG ANY PROCEDURETEST DEBUG CONNECT SESSIONTEST DELETE ANY CUBE DIMENSIONTEST DELETE ANY MEASURE FOLDERTEST DELETE ANY TABLETEST DEQUEUE ANY QUEUETEST DROP ANY ASSEMBLYTEST DROP ANY CLUSTERTEST DROP ANY CONTEXTTEST DROP ANY CUBETEST DROP ANY CUBE BUILD PROCESSTEST DROP ANY CUBE DIMENSIONTEST DROP ANY DIMENSIONTEST DROP ANY DIRECTORYTEST DROP ANY EDITIONTEST DROP ANY EVALUATION CONTEXTTEST DROP ANY INDEXTEST DROP ANY INDEXTYPETEST DROP ANY LIBRARYTEST DROP ANY MATERIALIZED VIEWTEST DROP ANY MEASURE FOLDERTEST DROP ANY MINING MODELTEST DROP ANY OPERATORTEST DROP ANY OUTLINETEST DROP ANY PROCEDURETEST DROP ANY ROLETEST DROP ANY RULETEST DROP ANY RULE SETTEST DROP ANY SEQUENCETEST DROP ANY SQL PROFILETEST DROP ANY SYNONYMTEST DROP ANY TABLETEST DROP ANY TRIGGERTEST DROP ANY TYPETEST DROP ANY VIEWTEST DROP PROFILETEST DROP PUBLIC DATABASE LINKTEST DROP PUBLIC SYNONYMTEST DROP ROLLBACK SEGMENTTEST DROP TABLESPACETEST DROP USERTEST ENQUEUE ANY QUEUETEST EXECUTE ANY ASSEMBLYTEST EXECUTE ANY CLASSTEST EXECUTE ANY EVALUATION CONTEXTTEST EXECUTE ANY INDEXTYPETEST EXECUTE ANY LIBRARYTEST EXECUTE ANY OPERATORTEST EXECUTE ANY PROCEDURETEST EXECUTE ANY PROGRAMTEST EXECUTE ANY RULETEST EXECUTE ANY RULE SETTEST EXECUTE ANY TYPETEST EXECUTE ASSEMBLYTEST EXPORT FULL DATABASETEST FLASHBACK ANY TABLETEST FLASHBACK ARCHIVE ADMINISTERTEST FORCE ANY TRANSACTIONTEST FORCE TRANSACTIONTEST GLOBAL QUERY REWRITETEST GRANT ANY OBJECT PRIVILEGETEST GRANT ANY PRIVILEGETEST GRANT ANY ROLETEST IMPORT FULL DATABASETEST INSERT ANY CUBE DIMENSIONTEST INSERT ANY MEASURE FOLDERTEST INSERT ANY TABLETEST LOCK ANY TABLETEST MANAGE ANY FILE GROUPTEST MANAGE ANY QUEUETEST MANAGE FILE GROUPTEST MANAGE SCHEDULERTEST MANAGE TABLESPACETEST MERGE ANY VIEWTEST ON COMMIT REFRESHTEST QUERY REWRITETEST READ ANY FILE GROUPTEST RESTRICTED SESSIONTEST RESUMABLETEST SELECT ANY CUBETEST SELECT ANY CUBE DIMENSIONTEST SELECT ANY DICTIONARYTEST SELECT ANY MINING MODELTEST SELECT ANY SEQUENCETEST SELECT ANY TABLETEST SELECT ANY TRANSACTIONTEST UNDER ANY TABLETEST UNDER ANY TYPETEST UNDER ANY VIEWTEST UNLIMITED TABLESPACETEST UPDATE ANY CUBETEST UPDATE ANY CUBE BUILD PROCESSTEST UPDATE ANY CUBE DIMENSIONTEST UPDATE ANY TABLE
......省略部分 SQL>
3、查看数据库所有用户的表权限
SQL> 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; USERNAME PRIVILEGE OWNER TABLE_NAME------------------------------ ---------------------------------------- ------------------------------ -----------------------------TEST ALTER SYS AWSEQ$TEST ALTER SYS MAP_OBJECTTEST ALTER XDB X$PT3APOVLDT3E865RG61F1J4MDR8TEST ALTER XDB XDB$ACLTEST ALTER XDB XDB$CHECKOUTSTEST ALTER XDB XDB$CONFIGTEST ALTER XDB XDB$D_LINKTEST ALTER XDB XDB$H_INDEXTEST ALTER XDB XDB$H_LINKTEST ALTER XDB XDB$NLOCKSTEST ALTER XDB XDB$RESCONFIGTEST ALTER XDB XDB$RESOURCETEST ALTER XDB XDB$WORKSPACETEST ALTER XDB XS$DATA_SECURITYTEST ALTER XDB XS$PRINCIPALSTEST ALTER XDB XS$ROLESETSTEST ALTER XDB XS$SECURITYCLASSTEST DEBUG SYS AW$TEST DEBUG SYS AW_OBJ$TEST DEBUG SYS AW_PROP$TEST DEBUG SYS AW_TRACK$
其他衍生变化基本都类似了。比如查看某个用户的所有权限、某个用户的所有角色等等。
三、澄清一个误区
如何查看拥有DBA角色的用户？
常规做法，一般是直接查询DBA_ROLE_PRIVS视图。查询语句如下：
select grantee,granted_role from dba_role_privs where granted_role='DBA';
这会漏掉用户，测试如下：
SQL> create role test_role; Role created SQL> grant dba to test_role; Grant succeeded SQL> create user user_dba identified by user_dba; User created SQL> grant test_role to user_dba; Grant succeeded SQL> select grantee,granted_role from dba_role_privs where granted_role='DBA'; GRANTEE GRANTED_ROLE------------------------------ ------------------------------TEST_ROLE DBASYS DBATEST DBASYSMAN DBASYSTEM DBA SQL>
可以看到，USER_DBA用户具有DBA权限，但并没有出现在检索结果中。
SQL> conn user_dba/user_dba@testConnected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Connected as user_dba SQL> select * from session_roles; ROLE------------------------------TEST_ROLEDBASELECT_CATALOG_ROLEHS_ADMIN_ROLEEXECUTE_CATALOG_ROLEDELETE_CATALOG_ROLEEXP_FULL_DATABASEIMP_FULL_DATABASEDATAPUMP_EXP_FULL_DATABASEDATAPUMP_IMP_FULL_DATABASEGATHER_SYSTEM_STATISTICSSCHEDULER_ADMINWM_ADMIN_ROLEJAVA_ADMINJAVA_DEPLOYXDBADMINXDB_SET_INVOKEROLAP_XS_ADMINOLAP_DBA 19 rows selected SQL>
正确的查询应按如下语句查询：
SQL> 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'; USERNAME GRANTED_ROLE------------------------------ ------------------------------TEST_ROLE DBASYS DBASYSMAN DBATEST DBAUSER_DBA DBASYSTEM DBA 6 rows selected SQL>
前面的查询结果之所以漏掉了数据，是因为dba_role_privs视图，只记录直接授权。我将具有DBA权限的角色TEST_ROLE，授予某用户时，dba_role_privs视图中就查不到DBA权限的授权记录，只能查到TEST_ROLE的授权记录。