当前位置:Oracle研究中心 > 运维DBA >
时间:2016-06-21 22:55 来源:Oracle研究中心 作者:惜分飞 点击: 次
[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 oracleplus.net; 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
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.
SQL> conn xff/oracleplus.net 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/oracleplus.net 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/oracleplus.net 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;
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之学习笔记:Oracle视图 收集Oracle常用查询用户权限视图
本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/455.html
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。