【OBJ】角色ROLE

目录

定义

CREATE ROLE

NOT IDENTIFIED

IDENTIFIED

BY password

USING package

EXTERNALLY

GLOBALLY

CONTAINER

注意：

小结

ALTER ROLE

DROP ROLE

 

---

定义

oracle中的role可以算是系统权限和对象权限的一个集合，将系统权限和对象权限打包成一个角色

 

CREATE ROLE

使用 CREATE ROLE 语句创建角色，角色是一组可以授予用户或其他角色的权限。可以使用角色来管理数据库权限。您可以向角色添加权限，然后将角色授予用户。然后，用户可以启用该角色并行使该角色授予的权限。 角色包含授予该角色的所有权限以及授予该角色的其他角色的所有特权。新角色最初为空。使用 GRANT 语句向角色添加权限。 如果创建的角色为NOT IDENTIFIED，则 Oracle 数据库使用 ADMIN option授予该角色。但是，如果创建"IDENTIFIED GLOBALLY"的角色，则数据库不会授予该角色。不能将全局角（global role）色直接授予用户或角色。只能通过企业角色（enterprise roles）授予全局角色。

指定要创建的角色的名称。该名称必须满足"数据库对象命名规则"中列出的要求。无论数据库字符集是否也包含多字节字符，Oracle 建议该角色至少包含一个单字节字符。角色名称的最大长度为 30 字节。一次可为单个用户启用的用户定义角色的最大数量为 148

在非 CDB 中，角色名称不能以 C## 或 c## 开始。

在CDB中，角色名称的要求如下：

公共角色的名称必须以与COMMON_USER_PREFIX初始化参数指定的前缀不区分大小写匹配的字符开头。默认情况下，前缀是C##。

本地角色的名称不得以与COMMON_USER_PREFIX初始化参数指定的前缀不区分大小写匹配的字符开头。不管COMMON_USER_PREFIX的值是多少，本地角色的名称都不能以C##或C##开头。

 

若要指定 CONTAINER 子句，必须连接到CDB。若要指定容器 = ALL，当前容器必须是根。要指定CONTAINER = CURRENT，当前容器必须是PDB

 

NOT IDENTIFIED

指定"NOT IDENTIFIED"以指示此角色由数据库授权，并且无需密码即可启用该角色

IDENTIFIED

使用 IDENTIFIED 子句指示用户必须经过指定方法的授权，然后才能使用 SET ROLE 语句启用角色。

BY password

允许您建本地角色，并指示用户在启用该角色时必须向数据库指定密码。无论此字符集是否也包含多字节字符，密码只能包含数据库字符集中的单字节字符。

USING package

允许创建一个安全的应用程序角色，该角色只能由使用授权包的应用程序启用。如果未指定schema，则默认在当前schema中。

EXTERNALLY

创建外部角色。外部用户必须经过外部服务（如操作系统或第三方服务）的授权，然后才能启用该角色。 根据操作系统的不同，用户可能需要在启用角色之前指定操作系统的密码

GLOBALLY

创建全局角色。在登录时启用该角色之前，必须授权全局用户由企业目录服务使用该角色。 如果同时省略NOT IDENTIFIED和IDENTIFIED，则角色默认为"NOT IDENTIFIED"

CONTAINER

当连接到 CDB 时，容器子句适用。但是，不需要指定 CONTAINER 子句，因为它的默认值是唯一允许的值。

要创建公共角色，必须连接到根目录。您可以选择指定CONTAINER = ALL，默认是这个要创建本地角色，必须连接到 PDB。您可以选择指定CONTAINER = CURRENT，这是连到 PDB 时默认的。

 

创建测试用户u01/u01、u02/u02并指定使用表空间及配额

SQL> create user u01 identified by u01 default tablespace users quota 20m on users;   User created.   SQL> create user u02 identified by u02 default tablespace users quota 20m on users;   User created.

由sys用户创建角色worker，授予create session、create table、select用户scott下的dept权限

由sys用户创建角色manager，授予create session、create table、create view、select和update用户scott下的dept权限

worker

SQL> create role worker;   Role created.   SQL> grant create session,create table to worker;   Grant succeeded.   SQL> grant select on scott.dept to worker;   Grant succeeded.

manager

SQL> create role manager;   Role created.   SQL> grant create session,create table to manager;   Grant succeeded.   SQL> grant select on scott.dept to manager;   Grant succeeded.   SQL> grant update on scott.dept to manager;   Grant succeeded.

在dba_sys_privs和dba_tab_privs中查看他们拥有的系统权限和对象权限

SQL> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE='WORKER';   GRANTEE  PRIVILEGE -------- -------------------- WORKER   CREATE TABLE WORKER   CREATE SESSION   SQL> select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs where GRANTEE='WORKER';   GRANTEE  OWNER TABLE PRIVILEGE -------- ----- ----- -------------------- WORKER   SCOTT DEPT  SELECT

SQL> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE='MANAGER';   GRANTEE  PRIVILEGE -------- -------------------- MANAGER  CREATE TABLE MANAGER  CREATE SESSION   SQL> select GRANTEE,OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs where GRANTEE='MANAGER';   GRANTEE  OWNER TABLE PRIVILEGE -------- ----- ----- -------------------- MANAGER  SCOTT DEPT  SELECT MANAGER  SCOTT DEPT  UPDATE

将角色worker授予u01，manager授予u02

SQL> grant worker to u01;   Grant succeeded.   SQL> grant manager to u02;   Grant succeeded.

在dba_role_privs中查看用户拥有的角色

SQL> select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTEE in ('U01','U02');   GRANTEE  GRANTED_ROLE -------- --------------- U02      MANAGER U01      WORKER

测试u01和u02是否具有相应的权限

SQL> conn u01/u01 Connected. SQL> create table test1 (time date);   Table created.   SQL> select * from scott.dept;    DEPTNO DNAME           LOC ------- --------------- ----------      10 ACCOUNTING      NEW YORK      20 RESEARCH        DALLAS      30 SALES           CHICAGO      40 OPERATIONS      BOSTON

SQL> conn u02/u02 Connected. SQL> create table test1 (time date);   Table created.   SQL> select * from scott.dept;    DEPTNO DNAME           LOC ------- --------------- ----------      10 ACCOUNTING      NEW YORK      20 RESEARCH        DALLAS      30 SALES           CHICAGO      40 OPERATIONS      BOSTON   SQL> update scott.dept set LOC='TJ CHINA' where deptno=10;   1 row updated.

 

注意：

以上授权用户权限的时候，已经将权限进行了封装，将角色授予用户，但是如果再单独对用户进行create table授权的时候会出现如下问题

SQL> grant create table to u01,u02;   Grant succeeded.

表面上看授权成功

在视图中也可以查到相应的权限

SQL> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE in('U01','U02');   GRANTEE PRIVILEGE ------- ------------- U02     CREATE TABLE U01     CREATE TABLE

但是这是如果回收他们的角色看一下他们的系统权限

SQL> revoke worker from u01;   Revoke succeeded.   SQL> revoke manager from u02;   Revoke succeeded.

SQL> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE in('U01','U02');   GRANTEE PRIVILEGE ------- ------------- U02     CREATE TABLE U01     CREATE TABLE

怎么权限没有回收吗？

SQL> select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTEE in ('U01','U02');     no rows selected

角色已经没有了

可以看出在重新赋予create table后，他们的权限并没有和合并到一起，还是需要单独回收的

SQL> revoke create table from u01;   Revoke succeeded.   SQL> revoke create table from u02;   Revoke succeeded.   SQL> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE in('U01','U02');   no rows selected

再次回收以后，对应的权限就没有了

 

创建角色boss指定密码boss

SQL> create role boss identified by boss;   Role created.

赋予角色boss create view、create session权限

SQL> grant create view，create session to boss;   Grant succeeded.

重新授予u01worker权限，并赋予u02manager和boss权限

SQL> grant worker to u01;   Grant succeeded.   SQL> grant manager，boss to u02;   Grant succeeded.

查看他们的角色及权限

SQL> select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTEE in ('U01','U02');   GRANTEE GRANTED ------- ------- U02     MANAGER U01     WORKER U02     BOSS SQL> select GRANTEE,PRIVILEGE from dba_sys_privs where GRANTEE in('WORKER','MANAGER','BOSS');   GRANTEE PRIVILEGE ------- -------------------- MANAGER CREATE SESSION BOSS    CREATE SESSION BOSS    CREATE VIEW MANAGER CREATE TABLE WORKER  CREATE TABLE WORKER  CREATE SESSION   6 rows selected.

连接u02 并创建一个视图

SQL> create view v_test1 as select * from test1; create view v_test1 as select * from test1             * ERROR at line 1: ORA-01031: insufficient privileges

将u02切换为boss身份后再次尝试

SQL> set role boss; set role boss * ERROR at line 1: ORA-01979: missing or invalid password for role 'BOSS'

提示输入密码

SQL> set role boss identified by boss;   Role set.

切换成功

再次创建视图

SQL> create view v_test01 as select * from test1;   View created.

创建成功

 

小结

在授予用户角色，单个权限时，之间重复的部分并不合并

在回收用户的单个权限时，只是将外层的权限回收回来，封装在角色中权限无法直接从用户回收

在角色中封装的权限，需先从角色中回收

授予用户角色后，用户需重新连接数据库才能生效

 

ALTER ROLE

对更改角色的限制 如果将not identified的角色授予其他角色，则不能将该角色更改为任何标识类型。 关于更改角色的注意事项

已启用角色的用户会话不受影响

如果将密码标识的角色更改为应用程序角色（ USING package子句），则与该角色关联的密码信息将丢失。下次启用角色时，Oracle 数据库将使用新的身份验证机制。

如果具有 ALTER ANY ROLE系统权限，并且将全局标识的角色更改为通过密码标识、外部标识或未识别标识，则 Oracle 数据库将授予您使用 ADMIN 选项更改的角色，就像创建了标识的非角色一样

 

修改boss为not identified

SQL> alter role boss not identified;   Role altered.

将boss授权给u01并查看他们的权限·

SQL> conn / as sysdba Connected. SQL> alter role boss not identified;   Role altered.   SQL> grant boss,worker to u01;   Grant succeeded.   SQL> select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTEE in ('U01','U02');   GRANTEE GRANTED ------- ------- U02     MANAGER U01     WORKER U01     BOSS U02     BOSS

切换到u01

创建视图

SQL> create view v_test1 as select * from test1;   View created.

创建成功至此谁都可以随意切换到boss了

 

此种情况适用于一个用户分配给多个人，而他们的权限又不同，这样可以用set role来切换相应的角色来实现权限管控

 

SQL> drop role boss;   Role dropped.

 

DROP ROLE

 

删除boss

 

还有一些role的高级用法，以后再说