角色的管理

一、什么是角色？

角色是数据库各种权限的集合，使用角色可以方便的管理数据库。角色可以赋予其他用户，权限也可以赋予其他角色。

角色的特点

1、使用crant和revoke授予和回收权限

2、可以授予任何用户或角色，但是不能授予角色自己或循环授予

3、角色包含系统权限和对象权限

4、允许启动或关闭授予用户的角色

5、允许使用密码启动一个角色

6、角色名是唯一的，不能和已存在的用户名和角色名相同

7、角色不被任何人拥有，也不属于任何模式

总结角色的优点

1、使得权限的管理更方便，角色可以赋予多个用户，使得相同的授权更容易实现，需要修改这些用户的权限，只需要修改为这些用户赋予角色所包含的权限就可以。

2、动态的权限管理，一旦角色中的某个权限被修改，则所有的被授予该角色的用户都自动获得修改的权限，并且立即生效。

3、权限可以激活和关闭，使得DBA可以方便的选择是否使用赋予用户的角色，临时的关闭或开启角色的作用。

4、可以通过操作系统授权角色，即角色可以通过操作系统指令或工具指定将角色赋予用户。

5、提高性能，使用角色减少了数据字典中授权记录的数量，通过关闭角色使得在语句执行过程中减少了权限的确认。

二、创建角色

创建角色的语法

CREATE ROLE role_name [NOT IDENTIFIED | IDENTIFIED { BY password |EXTERNALLY |GLOBALLY |USING package}]

参数解释

role_name：角色名字（唯一，不能与任何用户和角色名重复）

NOT IDENTIFIED：在激活角色时不需要密码验证

IDENTIFIED：在激活角色时需要密码验证

BY password：设置角色的验证密码

USING package：创建应用角色，该角色只能由应用通过授权的package（包）激活

EXTERNALLY：说明角色在激活前，必须通过外部服务如操作系统或者第三方服务授权

GLOBALLY：当使用SET ROLE激活角色是，用户必须通过企业路径服务授权来使用角色

创建测试

分别创建mk_clerk、at_clerk和manager

1、创建角色MK_CLERK，该角色不需要任何密码口令标识

create role mk_clerk;

2、创建角色AT_CLERK，角色在激活时需要密码口令标识rmb

create role at_clerk

identified by rmb;

3、创建角色MANAGER，角色使用外部服务标识

create role manager

identified by externally;

通过数据字典dba_roles查看所有关于角色的信息

SQL> select * from dba_roles

2 where role in ('MK_CLERK','AT_CLERK','MANAGER');

ROLE PASSWORD AUTHENTICAT

-------------------- ----------------- ------------------

AT_CLERK YES PASSWORD

MK_CLERK NO NONE

MANAGER YES PASSWORD

显示角色al_clerk和manager都需要密码（oracle 9i时密码有类型区分，oracle 10g后不再区分统一为YES）

三、修改角色

角色创建后可以修改，但oracle只允许修改它的验证方法。

修改角色语法

ALTER ROLE role_name {NOT IDENTIFIED | IDENTIFIFD {BY password | USING package | EXTERNALLY | GLOBALLY}}

参数含义与创建语法相同

修改角色测试

1、修改角色MK_CLERK的验证方法为外部标识

alter role mk_clerk

identified by externally;

2、修改角色MANAGER的验证方法为不需要任何密码口令标识

alter role manager

not identified;

2、修改角色AT_CLERK的验证方法为不需要任何密码口令标识

alter role at_clerk

not identified;

通过dba_roles查看修改结果

SQL> select * from dba_roles

2 where role in ('MK_CLERK','AT_CLERK','MANAGER');

ROLE PASSWORD AUTHENTICAT

-------------------- ----------------- ------------------

AT_CLERK NO NONE

MK_CLERK YES PASSWORD

MANAGER NO NONE

四、赋予角色权限

角色是权限的集合，所有创建角色后需要赋予相应的权限才能使角色有相应的作用。

权限分为两种，对象权限和系统权限

赋予角色权限的语法

1、授予对象权限 

GRANT object_privilege【,…..】| all 【privileges】 on 

< schema.> object_name 

to {user_name 【,…..】 | role_name【,….】| public} 

【with grant option】； 

参数含义

object_privilege:对象权限，可多个权限 

all 【privileges】:privileges可以省略，表示授予对象上的所有权限。 

schema：用户模式 

object_name：对象名称 

user_name ：被授予权限的用户 

role_name: 被授予权限的角色 

public：表示Oracle系统的所有用户 

with grant option:表示被授予权限的用户可以把该权限再次授予其他人

特别说明

1)无法使用WITH GRANT OPTION为角色授予对象权限

2)可以使用WITH ADMIN OPTION 为角色授予系统权限,取消时不是级联

3)如果取消某个用户的对象权限，那么对于这个用户使用WITH GRANT OPTION授予权限的用户来说，同样还会取消这些用户的相同权限，也就是说取消授权时级联的。

2、授予系统权限 

grant system_privilege 【,…..】 to 

{user_name 【,…..】 | role_name【,….】| public} 

【with admin option】；

参数含义

system_privilege：系统权限，可以多个权限

user_name ：被授予权限的用户 

role_name：被授予权限的角色

public：表示Oracle系统的所有用户 

with admin option：表示被授予权限的用户可以把该权限再次授予其他人

特别说明：

1）如果使用WITH ADMIN OPTION为某个用户授予系统权限，那么对于被这个用户授予相同权限的所有用户来说，取消该用户的系统权限并不会级联取消这些用户的相同权限。

2）系统权限无级联，即A授予B权限，B授予C权限，如果A收回B的权限，C的权限不受影响；系统权限可以跨用户回收，即A可以直接收回C用户的权限。

常用系统权限分类：

DBA:：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE：拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect, resource权限。

对于DBA管理用户：授予connect，resource, dba权限。

[普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。]

connect、resource角色包含的权限

CONNECT角色： --是授予最终用户的典型权利，最基本的

        ALTER SESSION --修改会话

        CREATE CLUSTER --建立聚簇

        CREATE DATABASE LINK --建立数据库链接

        CREATE SEQUENCE --建立序列

        CREATE SESSION --建立会话

        CREATE SYNONYM --建立同义词

        CREATE VIEW --建立视图

RESOURCE角色： --是授予开发人员的

        CREATE CLUSTER --建立聚簇

        CREATE PROCEDURE --建立过程

        CREATE SEQUENCE --建立序列

        CREATE TABLE --建表

        CREATE TRIGGER --建立触发器

        CREATE TYPE --建立类型

在给用户（角色）授权或从用户（角色）回收权限的同时，权限开始生效。

角色则不同，只有用户下次登陆或者执行了set role语句才能使角色生效。

在使用该指令向角色授权时，可以将权限或角色赋予其他角色

赋予角色系统权限测试

1、测试将CREATE SESSION、SELECT ANY TABLE、CREATE VIEW权限赋予角色AT_CLERK

grant select any table,create view

to at_clerk;

select any table：可以查询任何用户的表

create view：创建当前用户下可以使用表的视图

通过ROLE_SYS_PRIVS数据字典，查看赋予角色的权限

SQL> select *

2 from role_sys_privs

3 where role='AT_CLERK';

ROLE PRIVILEGE ADMIN_OPTION

--------------------- --------------------------- ----------------

AT_CLERK CREATE VIEW NO

AT_CLERK SELECT ANY TABLE NO

ROLE：角色

PRIVLEGE：权限

ADMIN_OPTION：能否继续向下授权（yes可以向其它用户和角色授予，no不可以继续授予）

2、测试将CREATE ANY TABLE、CREATE SESSION权限和角色AT_CLERK授予角色MANAGER

grant create any table,create session,at_clerk

to manager;

create session：创建当前用户下的会话连接

& 解释一下create table与create any table的区别，简单来讲就是第一种只能在当前用户下创建，而第二种则可以在其他用户下创建此用户的表。SYS用户可以创建scott用户的表，但hr用户则不能创建scott用户的表。

查看manager角色包含的权限

SQL> select *

2 from role_sys_privs

3 where role='MANAGER';

ROLE PRIVILEGE ADMIN_OPTION

--------------------- --------------------------- ----------------

MANAGER CREATE ANY TABLE NO

MANAGER CREATE SESSION NO

被赋予的AT_CLERK角色没有查到

通过ROLE_SYS_PRIVS数据字典发现只能查看该角色被直接赋予的权限，但并不能查询到该角色被赋予的其他角色。需要通过DBA_ROLE_PRIVS据字典查看该用户包含的其他角色。

SQL> select *

2 from dba_role_privs

3 where grantee='MANAGER';

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

---------------- ------------------------ ----------------------- ----------------------

MANAGER AT_CLERK NO YES

GRANTEE ：角色被授予人

GRANTED_ROLE：用户或角色被授予的角色

ADMIN_OPTION：能否继续向下授权

DEFAULT_ROLE：角色的默认用户（YES表示AT_CLERK角色授予了MANAGER用户，并且用户创建的该角色所以直接为该角色的默认用户）

五、赋予用户角色

创建角色后，需要将各种角色赋予用户或所有用户（PUBLIC），使被赋予用户能够使用各种权限。

角色赋予用户语法

GRANT role [， role] ......

TO {user | role | public } | [， { user | role | public } ] ......

参数含义

role：赋予用户角色名（如多个角色，则用逗号隔开）

user：被赋予角色的用户名（如多个用户用逗号隔开）

public：将角色赋予所有用户

WITH ADMIN OPTION：继续授权（被赋予该角色的用户或角色可以继续将该角色赋予其他用户或角色）

将角色赋予用户前，先创建两个用户CLERK和MYMANAGER

1、创建用户CLERK、MYMANAGER，并设置口令oracle

create user clerk identified by oracle;

create user mymanager identified by oracle;

2、验证用户创建是否成功

SQL> select username,created

2 from dba_users

3 where username in ('CLERK','MYMANAGER');

USERNAME CREATED

---------------------- -----------------

MYMANAGER 23-AUG-17

CLERK 23-AUG-17

3、没有赋予新用户任何角色、权限，尝试使用新用户MYMANAGER连接数据库

SQL> conn mymanager/oracle

ERROR:

ORA-01045: user MYMANAGER lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

由于mymanager用户缺少create session权限，拒绝登录数据库

4、将角色MANAGER赋予用户MYMANAGER，并带有WITH ADMIN OPTION参数可以继续向下授权

grant manager to mymanager with admin option;

5、查看用户MYMANAGER当前会话包含的权限

SQL> select *

2 from session_privs;

PRIVILEGE

----------------------------------------

CREATE SESSION

CREATE ANY TABLE

SELECT ANY TABLE

CREATE VIEW

6、MYMANAGER用户使用USER_ROLE_PRIVS数据字典，查看该用户包含的角色信息

SQL> select *

2 from user_role_privs

3 where username=‘MYMANAGER’;

USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED

---------------- ------------------------ ----------------------- ---------------------- ----------------------

MYMANAGER MANAGER YES YES NO

可以查看已经将该角色授权MYMANAGER用户，并且ADMIN_OPTION为YES代表该用户可以继续向其他用户和角色授权 ，DEFAULT_ROLE为YES代表为该角色的默认用户，OS_GRANTED为NO代表该角色不是由操作系统授权。

7、查看MYMANAGER用户包含全部哪些权限？

在mymanager用户下执行，可以看到当前用户所拥有的角色和权限

select * from session_roles; 当前用户启用的角色

select * from session_privs; 当前用户启用的角色

select * from role_sys_privs; 当前用户启用的角色、权限

SYS用户查看其它用户包含哪些权限较为复杂

select * from dba_sys_privs where grantee= 查询用户被直接赋予的权限

select * from dba_role_privs where grantee= 查询用户包含的角色，然后通过这个角色可以查看角色是否包含其它角色

select * from role_sys_privs where role= 查询角色包含的权限

8、MYMANAGER用户下将其包含的MANAGER角色授予用户CLERK

grant manager to clerk;

9、使用新用户CLERK登录数据库

conn clerk/oracle

查看CLERK用户包含的角色信息

select * from user_role_privs;

USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED

---------------- ------------------------ ----------------------- ---------------------- ----------------------

CLERK MANAGER NO YES NO

CLERK用户由于没有with admin option参数，角色不能继续向下授权。default_role为yes表示该角色为用户的默认角色

虽然CLERK用户没有AT_CLERK角色，但是由于MANAGER角色中包含了AT_CLERK并且可以授权该角色，所以CLERK角色间接有了AT_CLERK角色的权限。但是如果通过revoke at_clerk from manager收回manager角色中at_clerk角色的授权，那此时clerk用户在下一次登陆就会没有at_clerk角色的相应权限。后面会有相应演示

六、默认角色

我们看到将角色MANAGER赋予用户MYMANAGER和用户CLERK后，角色MANAGER都是用户的默认角色，这是Oracle的默认设置。可以使用ALTER USER指令修改默认角色。

首先SYS用户将at_clerk角色授予manager角色修改为可以向下授权

SQL> grant at_clerk to manager with admin option;

SQL> select * from dba_role_privs where grantee='MANAGER';

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

----------------- ------------------------- ---------------------- ---------------------

MANAGER AT_CLERK YES YES

1、MYMANAGER用户下将AT_CLERK、MANAGER角色授予CLERK用户。

grant at_clerk to clerk;

grant manager to clerk;

2、将该用户CLERK的AT_CLERK角色设置为非默认角色

alter user clerk default role all except at_clerk;

再次查询可以看出default_role为NO

将用户下的所有角色都设置为非默认角色

alter user clerk default role none;

3、将用户的AT_CLERK角色设置为默认角色

alter user clerk default role at_clerk;

将赋予用户的所有角色都设置为默认角色

alter user clerk default role all;

七、禁止和激活角色

角色可以禁止和激活，禁止意味着用户不再具有该角色赋予的各种权限，即回收角色具有的权限，而激活意味着赋予用户角色的权限。

我们知道CLERK用户具有两个角色，其中AT_CLERK是通过授予MANAGER角色获得的。下面通过禁用AT_CLERK角色来查看对CLERK用户的影响。

1、首先查询CLERK用户当前会话具有的权限

SQL> select * from session_privs;

PRIVILEGE

-----------------------------

CREATE SESSION

CREATE ANY TABLE

SELECT ANY TABLE

CREATE VIEW

2、CLERK用户禁用用户的所有角色

set role none;

此时查询用户的当前会话具有的权限，没有任何权限。此时系统回收了用户的所有角色包含的权限

3、 激活AT_CLERK角色

set role at_clerk;

再次查询可以发现其中AT_CLERK角色中的权限已经可以使用

4、SYS用户修改角色MANAGER的验证方式为口令验证

alter role manager identified by rmb;

5、CLERK用户启动MANAGER角色会要求需要口令验证

SQL> set role manager;

set role manager

*

ERROR at line 1:

ORA-01979: missing or invalid password for role 'MANAGER'

6、CLERK用户用口令验证的方式再次激活角色

SQL> set role manager identified by rmb;

Role set.

7、再次查询当前会话包含的角色权限

SQL> select * from session_privs;

PRIVILEGE

----------------------------------------

CREATE SESSION

CREATE ANY TABLE

SELECT ANY TABLE

CREATE VIEW

八、角色的回收和删除

既然权限、角色可以授予用户、角色，同样也可以回收和删除。Oracle允许使用REVOKE子句回收赋予某一用户的角色。

1、首先SYS用户查看AT_CLERK、MANAGER角色的信息

SQL> select * from

2 dba_roles

3 where role in ('AT_CLERK','MANAGER');

ROLE PASSWORD AUTHENTICAT

----------------- ---------------- ----------------

AT_CLERK NO NONE

MANAGER YES PASSWORD

2、SYS用户查看这两个角色赋予的用户信息，哪些用户被赋予了这些角色

SQL> select *

2 from dba_role_privs

3 where granted_role in ('AT_CLERK','MANAGER')

4 order by granted_role;

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

------------------ ------------------------- ---------------------- -------------------

MANAGER AT_CLERK YES YES

SYS AT_CLERK YES YES

CLERK MANAGER NO YES

MYMANAGER MANAGER YES YES

SYS MANAGER YES YES

3、回收CLERK用户的AT_CLERK角色

revoke at_clerk from clerk;

4、验证发现CLERK的AT_CLERK角色确实被收回

SQL> select *

2 from dba_role_privs

3 where granted_role='AT_CLERK';

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

------------------ ------------------------- ---------------------- -------------------

MANAGER AT_CLERK YES YES

SYS AT_CLERK YES YES

5、将角色授予所有用户

实际环境中可能有一种情况需要将角色授予所有用户，Oracle使用PUBLIC代表所有用户。可以使用这种方法将一个或多个角色授予所有用户。注意：有口令的角色不能这样授予所有用户

grant at_clerk to public;

SQL> select *

2 from dba_role_privs

3 where granted_role in ('MANAGER','PUBLIC');

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

------------------ ------------------------- ---------------------- -------------------

MANAGER AT_CLERK YES YES

SYS AT_CLERK YES YES

PUBLIC MANAGER NO YES

6、回收PUBLIC用户的AT_CLERK角色

revoke at_clerk from public

通过to public方式授予的角色会回收，但是单独授予的角色不会被回收

7、删除角色AT_CLERK

drop role at_clerk

通过数据字典DBA_ROLES查询是否成功删除角色AT_CLERK

SQL> select *

2 from dba_roles

3 where role in ('AT_CLERK','MANAGER');

ROLE PASSWORD AUTHENTICAT

---------------- -------------- -------------------

MANAGER YES PASSWORD

已经确认将AT_CLERK角色删除

九、Oracle预定义的角色

为了方便用户使用各种权限，Oracle预定义了一些很有用的角色。

下面是oracle预定义的部分角色列表

AQ_ADMINISTRATOR_ROLE：管理QUEUE的管理员角色

CONNECT：连接数据库权限

DBA：数据库管理员权限

EXP_FULL_DATABASE：导出数据库权限

IMP_FULL_DATABASE：导入数据库权限

OEM_ADVISOR：执行OEM顾问的权限

OEM_MONITOR：执行OEM监视的权限

RECOVERY_CATALOG_OWNER：恢复数据字典

RESOURCE：创建一系列数据库对象的权限

SCHEDULER_ADMIN：管理各种调度的权限，如创建任务，执行程序等

JAVADEBUGPRIV：调试Java程序权限

MGMT_USER：创建会话和创建触发器权限

OLTP_DBA：执行联机事务处理时的DBA权限

OLTP_USER：执行联机事务时的USER权限

可以通过数据字典DBA_ROLE_PRIVS查询用户包含的角色，通过数据字典ROLE_SYS_PRIVS查询角色包含的权限