联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
DBA_SYS_PRIVS 用户所拥有的系统权限
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 16 13:26:09 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create user xff identified by xifenfei; User created. SQL> grant connect to xff; Grant succeeded. SQL> select * from DBA_SYS_PRIVS where grantee='XFF'; no rows selected SQL> REVOKE CONNECT FROM XFF; Revoke succeeded. SQL> grant create session to xff; Grant succeeded. SQL> select * from DBA_SYS_PRIVS where grantee='XFF'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- XFF CREATE SESSION NO SQL> grant select on chf.t_1 to xff; Grant succeeded. SQL> select * from DBA_SYS_PRIVS where grantee='XFF'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- XFF CREATE SESSION NO --说明只能查询系统权限，不能查询角色，不能查询用户权限
DBA_SYS_PRIVS 用户所拥有的角色
SQL> grant resource to xff; 授权成功。 SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='XFF'; GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- XFF RESOURCE NO YES
ROLE_SYS_PRIVS 角色所拥有的系统权限
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='CONNECT'; ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CONNECT CREATE SESSION NO SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE'; no rows selected SQL> SELECT * FROM SESSION_ROLES; ROLE ------------------------------ CONNECT SQL> CONN / AS SYSDBA Connected. SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='RESOURCE'; ROLE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RESOURCE CREATE TRIGGER NO RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TYPE NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE INDEXTYPE NO RESOURCE CREATE TABLE NO 8 rows selected. --很多时候只能使用sysdba(或者具体特定权限)才能够查询角色有哪些系统权限
ROLE_ROLE_PRIVS: 角色被赋予的角色
SQL> SELECT *FROM ROLE_ROLE_PRIVS WHERE ROLE='DBA'; ROLE GRANTED_ROLE ADM ------------------------------ ------------------------------ --- DBA OLAP_DBA NO DBA SCHEDULER_ADMIN YES DBA DELETE_CATALOG_ROLE YES DBA EXECUTE_CATALOG_ROLE YES DBA WM_ADMIN_ROLE NO DBA EXP_FULL_DATABASE NO DBA SELECT_CATALOG_ROLE YES DBA JAVA_DEPLOY NO DBA GATHER_SYSTEM_STATISTICS NO DBA JAVA_ADMIN NO DBA XDBADMIN NO ROLE GRANTED_ROLE ADM ------------------------------ ------------------------------ --- DBA IMP_FULL_DATABASE NO DBA XDBWEBSERVICES NO 13 rows selected.
SESSION_PRIVS 当前用户所拥有的全部权限
SQL> conn xff/xifenfei Connected. SQL> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION SQL> conn / as sysdba Connected. SQL> revoke create session from xff; Revoke succeeded. SQL> grant connect to xff; Grant succeeded. SQL> conn xff/xifenfei Connected. SQL> select * from session_privs; PRIVILEGE ---------------------------------------- CREATE SESSION --只能查看系统权限或者角色中包含的系统权限，不能查看用户权限
SESSION_ROLES: 当前用户被激活的角色
SQL> SELECT * from SESSION_ROLES; no rows selected SQL> show user; USER is "SYS" SQL> conn xff/xifenfei Connected. SQL> SELECT *FROM SESSION_ROLES; ROLE ------------------------------ CONNECT --sysdba查询无role选项，全部是由系统权限构成
查询某用户的所有系统权限
SQL> SELECT PRIVILEGE, ADMIN_OPTION 2 FROM DBA_SYS_PRIVS 3 WHERE GRANTEE = &USERNAME 4 UNION 5 --角色转换为权限 6 SELECT PRIVILEGE, ADMIN_OPTION 7 FROM ROLE_SYS_PRIVS 8 WHERE ROLE IN 9 (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME) 10 UNION 11 --角色的角色转为权限 12 SELECT PRIVILEGE, ADMIN_OPTION 13 FROM ROLE_SYS_PRIVS 14 WHERE ROLE IN (SELECT GRANTED_ROLE 15 FROM ROLE_ROLE_PRIVS 16 WHERE ROLE IN (SELECT GRANTED_ROLE 17 FROM DBA_ROLE_PRIVS 18 WHERE GRANTEE = &USERNAME)); 输入 username 的值: 'XFF' 原值 3: WHERE GRANTEE = &USERNAME 新值 3: WHERE GRANTEE = 'XFF' 输入 username 的值: 'XFF' 原值 9: (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME) 新值 9: (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'XFF') 输入 username 的值: 'XFF' 原值 18: WHERE GRANTEE = &USERNAME)) 新值 18: WHERE GRANTEE = 'XFF')) PRIVILEGE ADM ---------------------------------------- --- CREATE CLUSTER NO CREATE INDEXTYPE NO CREATE OPERATOR NO CREATE PROCEDURE NO CREATE SEQUENCE NO CREATE SESSION NO CREATE TABLE NO CREATE TRIGGER NO CREATE TYPE NO UNLIMITED TABLESPACE NO 已选择10行。
表相关权限视图
SELECT *FROM TABLE_PRIVILEGES; SELECT * FROM dba_TAB_PRIVS; SELECT * FROM ROLE_TAB_PRIVS;
加我QQ
connect 在不同版本中权限区别
9i数据库中的connect角色
11g数据库中connect角色权限(10g就开始改变)
查看某用户系统权限
SELECT PRIVILEGE, ADMIN_OPTION FROM DBA_SYS_PRIVS WHERE GRANTEE = &USERNAME UNION --角色转换为权限 SELECT PRIVILEGE, ADMIN_OPTION FROM ROLE_SYS_PRIVS WHERE ROLE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME) UNION --角色的角色转为权限 SELECT PRIVILEGE, ADMIN_OPTION FROM ROLE_SYS_PRIVS WHERE ROLE IN (SELECT GRANTED_ROLE FROM ROLE_ROLE_PRIVS WHERE ROLE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = &USERNAME));