Oracle 用户、角色权限管理下的几个误区
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 CONNECT FLOWS_030000 HS_ADMIN_ROLE FLOWS_030000 RESOURCE FLOWS_030000 SELECT_CATALOG_ROLE SCOTT CONNECT SCOTT RESOURCE TEST DATAPUMP_EXP_FULL_DATABASE TEST DATAPUMP_IMP_FULL_DATABASE TEST DBA TEST DELETE_CATALOG_ROLE TEST EXECUTE_CATALOG_ROLE TEST EXP_FULL_DATABASE TEST GATHER_SYSTEM_STATISTICS TEST HS_ADMIN_ROLE TEST IMP_FULL_DATABASE TEST JAVA_ADMIN TEST JAVA_DEPLOY TEST OLAP_DBA TEST OLAP_XS_ADMIN TEST SCHEDULER_ADMIN TEST SELECT_CATALOG_ROLE TEST WM_ADMIN_ROLE TEST XDBADMIN TEST 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 DATABASE FLOWS_030000 ALTER SESSION FLOWS_030000 ALTER SYSTEM FLOWS_030000 ALTER USER FLOWS_030000 CREATE ANY CONTEXT FLOWS_030000 CREATE CLUSTER FLOWS_030000 CREATE DIMENSION FLOWS_030000 CREATE INDEXTYPE FLOWS_030000 CREATE JOB FLOWS_030000 CREATE MATERIALIZED VIEW FLOWS_030000 CREATE OPERATOR FLOWS_030000 CREATE PROCEDURE FLOWS_030000 CREATE PUBLIC SYNONYM FLOWS_030000 CREATE ROLE FLOWS_030000 CREATE SEQUENCE FLOWS_030000 CREATE SESSION FLOWS_030000 CREATE SYNONYM FLOWS_030000 CREATE TABLE FLOWS_030000 CREATE TABLESPACE FLOWS_030000 CREATE TRIGGER FLOWS_030000 CREATE TYPE FLOWS_030000 CREATE USER FLOWS_030000 CREATE VIEW FLOWS_030000 DROP PUBLIC SYNONYM FLOWS_030000 DROP TABLESPACE FLOWS_030000 DROP USER FLOWS_030000 EXECUTE ANY PROCEDURE FLOWS_030000 SELECT ANY TABLE FLOWS_030000 UNLIMITED TABLESPACE SCOTT CREATE CLUSTER SCOTT CREATE INDEXTYPE SCOTT CREATE OPERATOR SCOTT CREATE PROCEDURE SCOTT CREATE SEQUENCE SCOTT CREATE SESSION SCOTT CREATE TABLE SCOTT CREATE TRIGGER SCOTT CREATE TYPE SCOTT UNLIMITED TABLESPACE TEST ADMINISTER ANY SQL TUNING SET TEST ADMINISTER DATABASE TRIGGER TEST ADMINISTER RESOURCE MANAGER TEST ADMINISTER SQL MANAGEMENT OBJECT TEST ADMINISTER SQL TUNING SET TEST ADVISOR TEST ALTER ANY ASSEMBLY TEST ALTER ANY CLUSTER TEST ALTER ANY CUBE TEST ALTER ANY CUBE DIMENSION TEST ALTER ANY DIMENSION TEST ALTER ANY EDITION TEST ALTER ANY EVALUATION CONTEXT TEST ALTER ANY INDEX TEST ALTER ANY INDEXTYPE TEST ALTER ANY LIBRARY TEST ALTER ANY MATERIALIZED VIEW TEST ALTER ANY MINING MODEL TEST ALTER ANY OPERATOR TEST ALTER ANY OUTLINE TEST ALTER ANY PROCEDURE TEST ALTER ANY ROLE TEST ALTER ANY RULE TEST ALTER ANY RULE SET TEST ALTER ANY SEQUENCE TEST ALTER ANY SQL PROFILE TEST ALTER ANY TABLE TEST ALTER ANY TRIGGER TEST ALTER ANY TYPE TEST ALTER DATABASE TEST ALTER PROFILE TEST ALTER RESOURCE COST TEST ALTER ROLLBACK SEGMENT TEST ALTER SESSION TEST ALTER SYSTEM TEST ALTER TABLESPACE TEST ALTER USER TEST ANALYZE ANY TEST ANALYZE ANY DICTIONARY TEST AUDIT ANY TEST AUDIT SYSTEM TEST BACKUP ANY TABLE TEST BECOME USER TEST CHANGE NOTIFICATION TEST COMMENT ANY MINING MODEL TEST COMMENT ANY TABLE TEST CREATE ANY ASSEMBLY TEST CREATE ANY CLUSTER TEST CREATE ANY CONTEXT TEST CREATE ANY CUBE TEST CREATE ANY CUBE BUILD PROCESS TEST CREATE ANY CUBE DIMENSION TEST CREATE ANY DIMENSION TEST CREATE ANY DIRECTORY TEST CREATE ANY EDITION TEST CREATE ANY EVALUATION CONTEXT TEST CREATE ANY INDEX TEST CREATE ANY INDEXTYPE TEST CREATE ANY JOB TEST CREATE ANY LIBRARY TEST CREATE ANY MATERIALIZED VIEW TEST CREATE ANY MEASURE FOLDER TEST CREATE ANY MINING MODEL TEST CREATE ANY OPERATOR TEST CREATE ANY OUTLINE TEST CREATE ANY PROCEDURE TEST CREATE ANY RULE TEST CREATE ANY RULE SET TEST CREATE ANY SEQUENCE TEST CREATE ANY SQL PROFILE TEST CREATE ANY SYNONYM TEST CREATE ANY TABLE TEST CREATE ANY TRIGGER TEST CREATE ANY TYPE TEST CREATE ANY VIEW TEST CREATE ASSEMBLY TEST CREATE CLUSTER TEST CREATE CUBE TEST CREATE CUBE BUILD PROCESS TEST CREATE CUBE DIMENSION TEST CREATE DATABASE LINK TEST CREATE DIMENSION TEST CREATE EVALUATION CONTEXT TEST CREATE EXTERNAL JOB TEST CREATE INDEXTYPE TEST CREATE JOB TEST CREATE LIBRARY TEST CREATE MATERIALIZED VIEW TEST CREATE MEASURE FOLDER TEST CREATE MINING MODEL TEST CREATE OPERATOR TEST CREATE PROCEDURE TEST CREATE PROFILE TEST CREATE PUBLIC DATABASE LINK TEST CREATE PUBLIC SYNONYM TEST CREATE ROLE TEST CREATE ROLLBACK SEGMENT TEST CREATE RULE TEST CREATE RULE SET TEST CREATE SEQUENCE TEST CREATE SESSION TEST CREATE SYNONYM TEST CREATE TABLE TEST CREATE TABLESPACE TEST CREATE TRIGGER TEST CREATE TYPE TEST CREATE USER TEST CREATE VIEW TEST DEBUG ANY PROCEDURE TEST DEBUG CONNECT SESSION TEST DELETE ANY CUBE DIMENSION TEST DELETE ANY MEASURE FOLDER TEST DELETE ANY TABLE TEST DEQUEUE ANY QUEUE TEST DROP ANY ASSEMBLY TEST DROP ANY CLUSTER TEST DROP ANY CONTEXT TEST DROP ANY CUBE TEST DROP ANY CUBE BUILD PROCESS TEST DROP ANY CUBE DIMENSION TEST DROP ANY DIMENSION TEST DROP ANY DIRECTORY TEST DROP ANY EDITION TEST DROP ANY EVALUATION CONTEXT TEST DROP ANY INDEX TEST DROP ANY INDEXTYPE TEST DROP ANY LIBRARY TEST DROP ANY MATERIALIZED VIEW TEST DROP ANY MEASURE FOLDER TEST DROP ANY MINING MODEL TEST DROP ANY OPERATOR TEST DROP ANY OUTLINE TEST DROP ANY PROCEDURE TEST DROP ANY ROLE TEST DROP ANY RULE TEST DROP ANY RULE SET TEST DROP ANY SEQUENCE TEST DROP ANY SQL PROFILE TEST DROP ANY SYNONYM TEST DROP ANY TABLE TEST DROP ANY TRIGGER TEST DROP ANY TYPE TEST DROP ANY VIEW TEST DROP PROFILE TEST DROP PUBLIC DATABASE LINK TEST DROP PUBLIC SYNONYM TEST DROP ROLLBACK SEGMENT TEST DROP TABLESPACE TEST DROP USER TEST ENQUEUE ANY QUEUE TEST EXECUTE ANY ASSEMBLY TEST EXECUTE ANY CLASS TEST EXECUTE ANY EVALUATION CONTEXT TEST EXECUTE ANY INDEXTYPE TEST EXECUTE ANY LIBRARY TEST EXECUTE ANY OPERATOR TEST EXECUTE ANY PROCEDURE TEST EXECUTE ANY PROGRAM TEST EXECUTE ANY RULE TEST EXECUTE ANY RULE SET TEST EXECUTE ANY TYPE TEST EXECUTE ASSEMBLY TEST EXPORT FULL DATABASE TEST FLASHBACK ANY TABLE TEST FLASHBACK ARCHIVE ADMINISTER TEST FORCE ANY TRANSACTION TEST FORCE TRANSACTION TEST GLOBAL QUERY REWRITE TEST GRANT ANY OBJECT PRIVILEGE TEST GRANT ANY PRIVILEGE TEST GRANT ANY ROLE TEST IMPORT FULL DATABASE TEST INSERT ANY CUBE DIMENSION TEST INSERT ANY MEASURE FOLDER TEST INSERT ANY TABLE TEST LOCK ANY TABLE TEST MANAGE ANY FILE GROUP TEST MANAGE ANY QUEUE TEST MANAGE FILE GROUP TEST MANAGE SCHEDULER TEST MANAGE TABLESPACE TEST MERGE ANY VIEW TEST ON COMMIT REFRESH TEST QUERY REWRITE TEST READ ANY FILE GROUP TEST RESTRICTED SESSION TEST RESUMABLE TEST SELECT ANY CUBE TEST SELECT ANY CUBE DIMENSION TEST SELECT ANY DICTIONARY TEST SELECT ANY MINING MODEL TEST SELECT ANY SEQUENCE TEST SELECT ANY TABLE TEST SELECT ANY TRANSACTION TEST UNDER ANY TABLE TEST UNDER ANY TYPE TEST UNDER ANY VIEW TEST UNLIMITED TABLESPACE TEST UPDATE ANY CUBE TEST UPDATE ANY CUBE BUILD PROCESS TEST UPDATE ANY CUBE DIMENSION TEST 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_OBJECT
TEST ALTER XDB X$PT3APOVLDT3E865RG61F1J4MDR8
TEST ALTER XDB XDB$ACL
TEST ALTER XDB XDB$CHECKOUTS
TEST ALTER XDB XDB$CONFIG
TEST ALTER XDB XDB$D_LINK
TEST ALTER XDB XDB$H_INDEX
TEST ALTER XDB XDB$H_LINK
TEST ALTER XDB XDB$NLOCKS
TEST ALTER XDB XDB$RESCONFIG
TEST ALTER XDB XDB$RESOURCE
TEST ALTER XDB XDB$WORKSPACE
TEST ALTER XDB XS$DATA_SECURITY
TEST ALTER XDB XS$PRINCIPALS
TEST ALTER XDB XS$ROLESETS
TEST ALTER XDB XS$SECURITYCLASS
TEST 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 DBA SYS DBA TEST DBA SYSMAN DBA SYSTEM DBA SQL>
可以看到，USER_DBA用户具有DBA权限，但并没有出现在检索结果中。
SQL> conn user_dba/user_dba@test Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Connected as user_dba SQL> select * from session_roles; ROLE ------------------------------ TEST_ROLE DBA SELECT_CATALOG_ROLE HS_ADMIN_ROLE EXECUTE_CATALOG_ROLE DELETE_CATALOG_ROLE EXP_FULL_DATABASE IMP_FULL_DATABASE DATAPUMP_EXP_FULL_DATABASE DATAPUMP_IMP_FULL_DATABASE GATHER_SYSTEM_STATISTICS SCHEDULER_ADMIN WM_ADMIN_ROLE JAVA_ADMIN JAVA_DEPLOY XDBADMIN XDB_SET_INVOKER OLAP_XS_ADMIN OLAP_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 DBA
SYS DBA
SYSMAN DBA
TEST DBA
USER_DBA DBA
SYSTEM DBA
6 rows selected
SQL>
前面的查询结果之所以漏掉了数据，是因为dba_role_privs视图，只记录直接授权。我将具有DBA权限的角色TEST_ROLE，授予某用户时，dba_role_privs视图中就查不到DBA权限的授权记录，只能查到TEST_ROLE的授权记录。