--================================
--Oracle 角色、配置文件
--================================
一、 角色
1. 角色
权限的集合，可以分配给一个用户或其他角色，但角色不能授予自己，也不能循环授予
角色的优点
可以先创建角色，向该角色赋予一系列权限，然后再将该角色授予多个用户或角色
增加或删除角色中的某一权限，被授予该角色的所有用户或角色自动地获得新增权限或删除旧的权限
可以为角色设置密码
2. 创建修改角色
CREATE ROLE role_name
[NOT IDENTIFIED( 默认 ) | IDENTIFIED BY password | EXTERNALLY |GLOBALLY];
注：同一个数据库中角色名称必须唯一，且不能使用已存在的用户名称
不支持 with grant option 为角色授予对象权限
支持 with admin option 为角色授予系统权限或另一个角色
使用 Enterprise Manager 创建某个用户时，该用户被自动授予了 CONNECT 角色，
即同时具有了该角色的所有权限
IDENTIFIED BY EXTERNALLY
意味着了启用一个角色，用户必须是某个操作系统组的一个成员，该操作系统组的名称应当与角色相对应。
当希望通过操作系统对角色进行身份认证，则需要设置 OS_ROLE 参数为 TRUE
且当设定了使用 IDENTIFIED BY EXTERNALLY 身份验证，必须在数据库驻留的服务器上按以下格式创建组
ora_ < SID > _<ROLE>[_[d][a]]
d: 指示 <ROLE> 部分指定的角色为用于用户的默认角色
a: 指示可以使用 with admin option 为用户授予 <ROLE> 部分所指定的角色
关于外部身份验证，请参考： Oracle 密码文件
常用的角色
角色 被授予的权限
DBA 几乎所有系统权限
SELECT_CATALOG_ROLE 数据字典上的对象权限，未被授予任何系统权限
EXECUTE_CATALOG_ROLE 数据字典上的程序包、过程、函数的对象权限
DELETE_CATALOG_ROLE DELETE ON SYS.AUD$
DELETE ON SYS.FGA_LOG$
EXP_FULL_DATABASE 从数据库中导出数据时查询任何表或序列、执行任何过程或类型以及修改
数据字典对象的权限
IMP_FULL_DATABASE 执行导入时，在数据库内除了 sys 模式之外的任何模式中创建对象的权限
CONNECT ALTER SESSION
CREATE CLUSTER
CREATE DATABASE LINK
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE VIEW
RESOURCE CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE (when granted)
AQ_ADMINISTRATOR_ROLE Advanced Queuing 对象上的对象权限
CREATE EVALUATION CONTEXT
CREATE RULE
CREATE RULE SET
DEQUEUE ANY QUEUE
ENQUEUE ANY QUEUE
MANAGE ANY QUEUE
AQ_USER_ROLE EXECUTE ON SYS.DBMS_AQ
EXECUTE ON SYS.DBMS_AQIN
EXECUTE ON SYS.DBMS_AQJMS_INTERNAL
EXECUTE ON SYS.DBMS_TRANSFORM
SCHEDULER_ADMIN CREATE ANY JOB
CREATE JOB
EXECUTE ANY CLASS
EXECUTE ANY PROGRAM
MANAGE SCHEDULE
( 使用 WITH ADMIN OPTION 授予上述所有权限 )
PUBLIC 不具有特殊的权限，不过为 public 角色授予权限时，所有用户都会继承该权限
-- 创建不要口令的角色 clerk
SQL> CREATE ROLE clerk;
-- 创建要口令的角色 sales
SQL> CREATE ROLE sales IDENTIFIED BY money;
-- 创建一个需要使用外部标识（如操作系统）的角色 manager
SQL> CREATE ROLE manager IDENTIFIED EXTERNALLY;
-- 创建后查看角色：
SQL> SELECT role,password_required FROM dba_roles;
ROLE PASSWORD
------------------------------ --------
CLERK NO
SALES YES
MANAGER EXTERNAL
角色修改：
ALTER ROLE rolename
[NOT IDENTIFIED | IDENTIFIED
BY password | EXTERNALLY | GLOBALLY];
一个角色在创建后可以修改，但只能修改它的验证方法。
但只有角色是使用带有 with ADMIN option 选项的 GRANT 语句授予的或者具
有 ALTER ANY ROLE 系统权限的用户时，才可以修改这个角色
-- 将角色 clerk 的验证方法改为使用外部（如操作系统）标识
SQL > ALTER ROLE clerk IDENTIFIED EXTERNALLY ;
-- 将角色 sales 的验证方法改为不使用任何标识方法
SQL > ALTER ROLE sales NOT IDENTIFIED ;
-- 将角色 manager 的验证方法改为使用口令标识，口令为 vampire
SQL > ALTER ROLE manager IDENTIFIED BY vampires ;
-- 再查询后即可看到变化
SELECT role , password_required FROM dba_roles
3. 为角色授予和取消权限
a. 角色授权
为角色授予系统权限语法
GRANT system_priv [, system_priv, ...]
TO role | PUBLIC [, role | PUBLIC, ...]
[WITH ADMIN OPTION];
为角色授予对象权限语法
GRANT ALL [PRIVILEGES] | object_priv [(column, column, ...)]
[, object_priv [(column, column, ...(] , ...]
ON [schema_name.]object_name
TO role | PUBLIC [, role | PUBLIC, ...];
-- 为角色赋予权限（ GRANT ）：
SQL > show user ;
USER is "SYSTEM"
SQL > CREATE ROLE manager ;
Role created .
-- 赋予系统权限
SQL > GRANT CREATE TABLE , CREATE VIEW , CREATE SESSION TO manager WITH ADMIN OPTION ;
Grant succeeded .
-- 赋予对象权限
SQL > GRANT SELECT , INSERT , UPDATE ON scott . emp TO manager ;
Grant succeeded .
-- 查看角色的系统权限（ role_sys_privs ）
SQL > SELECT * FROM role_sys_privs WHERE role = 'MANAGER' ;
ROLE PRIVILEGE ADM
------------------------------ -------------------- ---
MANAGER CREATE SESSION YES
MANAGER CREATE TABLE YES
MANAGER CREATE VIEW YES
-- 查看角色的对象权限 (role_tab_privs)
SQL > SELECT * FROM role_tab_privs WHERE role = 'MANAGER' ;
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA
------------------------- -------------------- -------------------- ----------- -------------------- ---
MANAGER SCOTT EMP UPDATE NO
MANAGER SCOTT EMP INSERT NO
MANAGER SCOTT EMP
b. 取消角色所拥有的权限
取消角色拥有的系统权限语法
REVOKE system_priv | role_name [, system_priv | role_name, ...]
FROM role | PUBLIC [,role | PUBLIC, ...];
取消角色用户的对象权限语法
REVOKE ALL [PRIVILEGES] | object_priv [, object_priv, ...]
ON [schema_name.]object_name
FROM role | PUBLIC [,role | PUBLIC, ...]
[CASCADE CONSTRAINTS]
-- 取消角色的系统权限
SQL > REVOKE CREATE VIEW FROM manager ;
Revoked succeeded .
-- 取消角色的对象权限
SQL > REVOKE INSERT , UPDATE ON scott . emp FROM manager ;
Revoked succeeded .
-- 查看被取消权限后所剩余的权限的集合
SQL > SELECT role , 'System_privs' owner , privilege
2 FROM role_sys_privs
3 WHERE role = 'MANAGER'
4 UNION
5 SELECT role , owner , privilege
6 FROM role_tab_privs
7 WHERE role = 'MANAGER' ;
ROLE OWNER PRIVILEGE
------------------------------ -------------------- --------------------
MANAGER SCOTT SELECT
MANAGER System_privs CREATE SESSION
MANAGER System_privs CREATE TABLE
4. 角色赋予与角色取消
a . 将角色赋予用户 （ grant ）：
语法：
GRANT role_name [, role_name, ...]
TO user_name | role | PUBLIC [, user_name | role | PUBLIC, ...]
[WITH ADMIN OPTION];
-- 将角色赋予 robinson 且使用了 WITH ADMIN OPTION
SQL > GRANT manager TO robinson WITH ADMIN OPTION ;
Grant succeeded .
--robinson 有权将角色授予 john ，如下
SQL > CONN robinson / lion ;
Connected .
SQL > GRANT manager TO john ;
Grant succeeded .
-- 查看角色授予了哪些用户 (dba_role_privs)
SQL > SELECT * FROM dba_role_privs WHERE granted_role = 'MANAGER' ;
GRANTEE GRANTED_ROLE ADM DEF
-------------------- ------------------------------ --- ---
SYSTEM MANAGER YES YES
JOHN MANAGER NO YES
ROBINSON MANAGER YES YES
-- 查看用户拥有哪些角色
SQL > CONN scott / tiger ;
Connected .
SQL > SELECT * FROM user_role_privs ;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT CONNECT NO YES NO
SCOTT RESOURCE NO YES NO
-- 查看用户拥有哪些角色 ( 使用 session_roles)
SQL > SELECT * FROM session_roles ;
ROLE
------------------------------
CONNECT
RESOURCE
b . 取消用户拥有的角色
语法：
REVOKE role_name [, role_name, ...]
FROM user_name | role | PUBLIC [, user_name | role | PUBLIC, ...];
-- 取消用户角色
SQL > REVOKE resource FROM scott ;
Revoke succeeded .
-- 查看 resource 角色已被取消
SQL > CONN scott / tiger ;
Connected .
SQL > SELECT * FROM user_role_privs ;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT CONNECT NO YES NO
-- 对于使用 WITH ADMIN OPTION 参数，收回 robinson 角色，并不影响 john 的级联角色，如下：
SQL > select * from dba_role_privs where grantee = 'JOHN' ;
GRANTEE GRANTED_ROLE ADM DEF
-------------------- ------------------------------ --- ---
JOHN MANAGER NO YES
SQL > revoke manager from robinson ;
Revoke succeeded .
SQL > select * from dba_role_privs where grantee = 'JOHN' ;
GRANTEE GRANTED_ROLE ADM DEF
-------------------- ------------------------------ --- ---
JOHN MANAGER NO YES
SQL > select * from dba_role_privs where grantee = 'ROBINSON' ;
no rows selected
5. 设置默认角色
可以将多个角色授予一个用户。默认角色是这些角色的一个子集，默认角色在用户登录系统时
自动激活（开启）。在默认情况下，所有赋予用户的角色在用户登录时不需要口令就被激活。
可以使用 ALTER USER 语句来限制用户有的默认角色。
语法：
ALTER USER username DEFAULT ROLE
role [, role, ...] | ALL [EXCEPT role [, role, ...]] | NONE;
ALTER USER 语句中的 DEFAULT ROLE 子句只适用于那些使用 GRANT 语句直接授予用户的角色。
DEFAULT ROLE 子句在下列情况下是不能使用的：
通过其它角色授予的角色
没有直接授予该用户的角色
通过外部服务（如操作系统）管理的角色
-- 创建用户 martin 并授予 resource,connect 角色
SQL> CREATE USER martin IDENTIFIED BY abc;
User created.
SQL> GRANT RESOURCE,CONNECT TO martin;
Grant succeeded.
SQL> CONN martin/abc;
Connected.
SQL> CONN system/redhat
Connected.
-- 设置缺省的角色为 NONE 后，无法登陆
SQL> ALTER USER martin DEFAULT ROLE NONE;
User altered.
SQL> CONN martin/abc;
ERROR:
ORA-01045: user MARTIN lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> CONN system/redhat;
Connected.
-- 重置角色后可以正常登陆
SQL> ALTER USER martin DEFAULT ROLE ALL;
User altered.
SQL> CONN martin/abc;
Connected.
-- 默认角色为除 resource 之外的所有角色
SQL> ALTER USER martin DEFAULT ROLE ALL EXCEPT RESOURCE;
User altered.
6. 激活和禁止角色（ SET ROLE ）
语法：
SET ROLE ALL [EXCEPT role_name [,role_name]] | NONE |
role_name [IDENTIFIED BY password] [, role_name [IDENTIFIED BY password, ...];
SQL > CONN robinson / lion
Connected .
-- 查看用户拥有的所有角色
SQL > SELECT * FROM user_role_privs ;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ROBINSON MANAGER NO YES NO
ROBINSON RESOURCE NO YES NO
-- 查看用户拥有的所有权限
SQL > SELECT * FROM session_privs ;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
9 rows selected .
-- 禁用所有的角色后，用户的权限无显示记录
SQL > SET ROLE NONE ;
Role set .
SQL > SELECT * FROM session_privs ;
no rows selected
-- 用户重新启用所有角色
SQL > SET ROLE ALL
Role set .
-- 启用一个 manager 角色
SQL > SET ROLE manager ;
Role set .
SQL > SELECT * FROM session_roles ;
ROLE
------------------------------
MANAGER
在这里不象一般的 ALTER 命令，没有用到 ENABLE 和 DISABLE 之类的选项。
7. 角色删除 ：
DROP ROLE role_name
-- 角色的删除需要适当的权限，如下提示没有权限删除角色
SQL > SHOW USER ;
USER is "ROBINSON"
SQL > DROP ROLE manager ;
DROP ROLE manager
*
ERROR at line 1 :
ORA - 01031 : insufficient privileges
-- 使用 system 帐户删除角色
SQL > CONN system / redhat ;
Connected .
SQL > DROP ROLE manager ;
-- 帐户 robisnon 唯一的角色被删除后，再次使用该帐户登陆已提示没有权限
-- 即角色的删除，原来所关联的用户会自动分离该角色及所有相关权限
SQL > CONN robinson / lion ;
ERROR :
ORA - 01045 : user ROBINSON lacks CREATE SESSION privilege ; logon denied
Warning : You are no longer connected to ORACLE .
Role dropped .
关于 connect 和 resource 预定义角色：
connect , resoure 是 role , 包含很多的权限的
其中 resource 具有创建表，索引，视图和其他的 Oracle 对象的能力，
同时默认带有 unlimited tablespace 权限
一般将 connect 授予所有的普通用户
connect 和 resource 授予开发人员
oracle 声称 connect 和 resource 角色是为了与它早期的版兼容而保留的，
劝告用户尽可能不要使用这两个角色，以避免产生安全漏洞。
二、 用户配置文件
配置文件实现中几类安全控制措施：
帐户上锁
资源限制
直接权限
角色权限
1. 资源限制：
利用配置文件来实现，可用以下两种方法之一来开启资源限制：
a . 在初始化参数文件中将 RESOURCE_LIMIT 设为 TRUE
b . 使用 ALTER SYSTEM 命令将 RESOURCE_LIMIT 设为 TRUE
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
配置文件的资源限制既可加在会话一级，也可加在调用一级，会话级设置的资源限制是强加在每一个
连接上的。当超过了会话级的资源限制时， ORACLE 系统将返回出错信息。服务器与用户的连接断开
在会话级可以设置的资源限制如下：
SESSION_PER_USER : 每个用户所允许的并行会话数
CPU_PER_SESSION : 总共的 CPU 时间，其单位是 % s
IDLE_TIME : 没有活动的时间，单位是分钟
CONNECT_TIME : 连接时间
LOGICAL_READS_PER_SESSION : 物理和逻辑读的数据块数。
创建资源限制文件：
CREATE PROFILE profile_name LIMIT
[SESSION_PER_USER max_value]
[CPU_PER_SESSION max_value]
[CPU_PER_CALL max_value]
[CONNECT_TIME max_value]
[IDLE_TIME max_value]
[LOGICAL_READS_PER_SESSION max_value]
[LOGICAL_READS_PER_CALL max_value]
-- 演示创建资源限制配置文件
SQL > CREATE PROFILE luckly_prof LIMIT
2 SESSIONS_PER_USER 8 -- 同一用户可打开个会话（连接）
3 CPU_PER_SESSION 168000 -- 每个会话最多可以使用的 CPU 时间为个 %s
4 LOGICAL_READS_PER_SESSION 21888 -- 每个会话最多可以读个数据块
5 CONNECT_TIME 180 -- 每个会话的连接时间最多为分钟
6 IDLE_TIME 10 ; -- 每个会话的没有活动时间不能超分钟
Profile created .
-- 查看刚刚创建的配置文件
SQL > SELECT * FROM dba_profiles WHERE profile = 'LUCKLY_PROF' ;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- -----------------------
LUCKLY_PROF COMPOSITE_LIMIT KERNEL DEFAULT
LUCKLY_PROF SESSIONS_PER_USER KERNEL 8
LUCKLY_PROF CPU_PER_SESSION KERNEL 168000
LUCKLY_PROF CPU_PER_CALL KERNEL DEFAULT
LUCKLY_PROF LOGICAL_READS_PER_SESSION KERNEL 21888
LUCKLY_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT
LUCKLY_PROF IDLE_TIME KERNEL 10
LUCKLY_PROF CONNECT_TIME KERNEL 180
LUCKLY_PROF PRIVATE_SGA KERNEL DEFAULT
LUCKLY_PROF FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_LIFE_TIME PASSWORD DEFAULT
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ------------------------
LUCKLY_PROF PASSWORD_REUSE_TIME PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_REUSE_MAX PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_LOCK_TIME PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_GRACE_TIME PASSWORD DEFAULT
2. 口令限制
FAILED_LOGIN_ATTEMPTS 帐户被锁之前可以尝试登录失败的次数
PASSWORD_LOCK_TIME 尝试指定次数失败后，账户被锁的天数
PASSWORD_LIFE_TIME 口令的生命周期（可使用的天数）
PASSWORD_GRACE_TIME 口令过期之后第一次成功地使用原口令登录后要改变口令的宽限天数
PASSWORD_REUSE_TIME 在一个口令可以重用之前的天数
PASSWORD_REUSE_MAX 在一个口令可以重用之前的最大变化数
PASSWORD_VERIFY_FUNCTION 在一个新的口令赋予一个用户之前，要验证口令的复杂性是否满足
安全要求的一个 PL / SQL 函数（该函数要 sys 用户运行 utlpwdmg . sql
脚本来生成）
在执行 utlpwdmg . sql 脚本文件期间， ORACLE 服务器将创建 VERIFY_FUNCTION 函数，并且使用如下
的 ALTER PROFILE 命令来修改 luckly_prof 概要文件：
-- 为 luckly_prof 配置文件增加口令限制
SQL > ALTER PROFILE luckly_prof LIMIT
2 PASSWORD_LIFE_TIME 60
3 PASSWORD_GRACE_TIME 10
4 PASSWORD_REUSE_TIME 1800
5 PASSWORD_REUSE_MAX UNLIMITED
6 FAILED_LOGIN_ATTEMPTS 3
7 PASSWORD_LOCK_TIME 1 / 1440 ;
Profile altered .
之后， ORACLE 服务器就要对所有用户提供的口令进行如下检查：
口令的最小长度为个字符
口令不应该与用户名相同
口令应该包含至少一个字符、一个数字和一个特殊字符
口令应该至少有个字母与以前的口令不同。除了 ORACLE 提供的默认复杂性检验函数外，
数据库管理员也可以自己写一个 PL / SQL 函数进行口令的复杂性检验。
用户提供的口令函数一定要在 SYS 模式下创建并且必须使用以下函数说明（即函数接口）
function_name ( userid_parameter IN VARCHAR2 ( 30 ),
password_parmeter IN VARCHAR2 ( 30 ),
old_password_parmeter IN VARCHAR2 ( 30 )
RETURN BOOLEAN
创建口令限制的概要文件：
同创建资源限制文件一样，只要把口令限制方面的内容加到 profile_name 中即可。
如：
CREATE PROFILE unluck_prof LIMIT
FAILED_LOGIN_ATTEMPTS 7
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 44
PASSWORD_REUSE_TIME 24
PASSWORD_GRACE_TIME 4 ;
-- 查看资源限制 (dba_profiles) ：
SQL > SELECT * FROM dba_profiles WHERE profile = 'LUCKLY_PROF' ;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------
LUCKLY_PROF COMPOSITE_LIMIT KERNEL DEFAULT
LUCKLY_PROF SESSIONS_PER_USER KERNEL 8
LUCKLY_PROF CPU_PER_SESSION KERNEL 168000
LUCKLY_PROF CPU_PER_CALL KERNEL DEFAULT
LUCKLY_PROF LOGICAL_READS_PER_SESSION KERNEL 21888
LUCKLY_PROF LOGICAL_READS_PER_CALL KERNEL DEFAULT
LUCKLY_PROF IDLE_TIME KERNEL 10
LUCKLY_PROF CONNECT_TIME KERNEL 180
LUCKLY_PROF PRIVATE_SGA KERNEL DEFAULT
LUCKLY_PROF FAILED_LOGIN_ATTEMPTS PASSWORD 3
LUCKLY_PROF PASSWORD_LIFE_TIME PASSWORD 60
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- -----------------------
LUCKLY_PROF PASSWORD_REUSE_TIME PASSWORD 1800
LUCKLY_PROF PASSWORD_REUSE_MAX PASSWORD UNLIMITED
LUCKLY_PROF PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
LUCKLY_PROF PASSWORD_LOCK_TIME PASSWORD .0006
LUCKLY_PROF PASSWORD_GRACE_TIME PASSWORD 10
16 rows selected .
RESOURCE_TYPE 为 KERNEL 表示这是一个资源限制，为 PASSWORD 表示为口令限制。
3. 修改配置文件：
ALTER PROFILE profile_name LIMIT
......
例： ALTER PROFILE luck_prof LIMIT
IDLE_TIME 40
4. 将 PROFILE 赋予用户：
CREATE USER user_name [PROFILE { profile_name | DEFAULT }]
ALTER USER user_name [PROFILE { profile_name | DEFAULT }]
请参照： Oracle 用户、对象权限、系统权限
SQL > CREATE USER andy IDENTIFIED BY oracle PROFILE luckly_prof ;
User created .
SQL > ALTER USER robinson PROFILE luckly_prof ;
User altered .
5. 删除配置文件：
DROP PROFILE profile_name [CASCADE]
如果一个概要文件已经赋予了用户，那么在 DROP PROFILE 时要用 CASCADE
无法删除 DEFAULT 配置文件，也无法删除 MONITORING_PROFILE 配置文件
-- 已被赋予给用的 profile 删除时需要使用 cascade ，如下：
SQL > DROP PROFILE luckly_prof ;
DROP PROFILE luckly_prof
*
ERROR at line 1 :
ORA - 02382 : profile LUCKLY_PROF has users assigned , cannot drop without CASCADE
SQL > DROP PROFILE luckly_prof CASCADE ;
Profile dropped .
三、 更多
SQL 基础--> ROLLUP 与CUBE 运算符 实现数据汇总
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
