TYGER@ORCL>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- -------------------- --------------------
TYGER ROLE_01 YES
TYGER ROLE_02 YES
14. 查看当前会话的权限，已经没有 create table 权限
TYGER@ORCL>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE VIEW
TYGER@ORCL>create table t1(x int);
create table t1(x int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
15. 重新登录会话，连接用户 所有权限都恢复原样
TYGER@ORCL>conn tyger/tyger;
Connected.
TYGER@ORCL>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
总结：在oracle 10g 无论角色是否有口令，将角色赋予给用户后，用户具有角色的全部权限。
oracle 11g 中测试 拥有口令的角色
[oracle@ora11gr2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 19 15:28:13 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ORA11G>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
1. 创建角色 tyger_ro1 无密码 tyger_ro2 有密码
SYS@ORA11G>create role tyger_ro1;
Role created.
SYS@ORA11G>create role tyger_ro2 identified by oracle;
Role created.
SYS@ORA11G>grant connect,create table to tyger_ro1;
Grant succeeded.
SYS@ORA11G>grant connect,create view to tyger_ro2;
Grant succeeded.
SYS@ORA11G>create user tyger identified by tyger quota unlimited on users;
User created.
SYS@ORA11G>grant tyger_ro1,tyger_ro2 to tyger;
Grant succeeded.
SYS@ORA11G>conn tyger/tyger
Connected.
TYGER@ORA11G>create table t(x int);
Table created.
TYGER@ORA11G>insert into t values(1);
1 row created.
TYGER@ORA11G>commit;
Commit complete.
2. 此时就出现问题了，role_02 明明有 create view 而且赋予给了 tyger 为什么这里就没有呢？
TYGER@ORA11G>create view view_t as select * from t;
create view view_t as select * from t
*
ERROR at line 1:
ORA-01031: insufficient privileges
3. 查看 tyger_ro2 的default_role 为NO 难道 role_02 角色失效？？？
TYGER@ORA11G>col username for a10
TYGER@ORA11G>col granted_role for a20
TYGER@ORA11G>col default_role for a20
TYGER@ORA11G>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- -------------------- --------------------
TYGER TYGER_RO1 YES
TYGER TYGER_RO2 NO
4. 再查看当前会话的权限，果然没有 create view 权限
TYGER@ORA11G>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
5. 设置 tyger_ro2 权限生效
TYGER@ORA11G>set role tyger_ro2 identified by oracle;
Role set.
6. 当前用户具有的权限不变
TYGER@ORA11G>select username,granted_role,default_role from user_role_privs;
USERNAME GRANTED_ROLE DEFAULT_ROLE
---------- -------------------- --------------------
TYGER TYGER_RO1 YES
TYGER TYGER_RO2 NO
7.当前会话用了 create view 权限 却没有了create table 权限
TYGER@ORA11G>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE VIEW
TYGER@ORA11G>create view view_t as select * from t;
View created.
TYGER@ORA11G>select * from tab;
TNAME TABTYPE CLUSTERID
-------