管理角色（二）

 

6、将角色分配给用户

——default role：当用户建立session 时，用户所分配的role 上的权限会立刻生效。

（如果不显式指定，用户所分配的role都是该用户的default role，默认角色分配的权限一般都很少）

09:16:32 SQL> create user tom identified by tom;

User created.

09:16:36 SQL> create user rose identified by rose;

User created.

09:22:37 SQL> alter user tom quota 10m on users;

User altered.

09:22:44 SQL> alter user rose quota 10m on users;

User altered.

09:16:43 SQL> grant pub_role,prv_role to tom,rose; ——with admin option 用户有权将role 分配给其他用户

Grant succeeded.

——role 可以分配给用户，也可以分配其他role，不能分配给自己。

09:20:19 SQL> conn tom/tom

Connected.

SQL> select * from user_role_privs;        ——默认情况下，pub_role 和 prv_role 都是tom的 default role

USERNAME        GRANTED_ROLE                   ADMIN_OPTION    DEFAULT_ROLE    OS_GRANTE

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

TOM             PRV_ROLE                       NO              YES             NO

TOM             PUB_ROLE                       NO              YES             NO

TOM             RESOURCE                       NO              YES             NO

09:21:51 SQL> select * from scott.emp;  ——tom 继承了prv_role的object privilege

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7369 SMITH      CLERK           7902 17-DEC-80        800                    20

7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

7566 JONES      MANAGER         7839 02-APR-81       2975                    20

7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

7788 SCOTT      ANALYST         7566 19-APR-87       3000        100         40

7839 KING       PRESIDENT            17-NOV-81       5000                    10

7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

7900 JAMES      CLERK           7698 03-DEC-81        950                    30

7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

09:23:19 SQL> create table emp as select * from scott.emp;  ——tom 继承了pub_role的system privilege

Table created.

——显式指定默认 role（对于非default role 必须在启用后，用户才能继承role 所具有的权限）

SQL> conn /as sysdba

Connected.

SQL> alter user tom default role pub_role;

User altered.

SQL> conn tom/tom

Connected.

SQL> select * from user_role_privs;

USERNAME        GRANTED_ROLE                   ADMIN_OPTION    DEFAULT_ROLE    OS_GRANTE

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

TOM             PRV_ROLE                       NO              NO              NO

TOM             PUB_ROLE                       NO              YES             NO

TOM             RESOURCE                       NO              NO              NO

SQL> select * from scott.emp;

select * from scott.emp

*

ERROR at line 1:

ORA-01031: insufficient privileges

——因为prv_role 是非 default role，所以tom 在建立session 不具有prv_role 的权限

09:39:29 SQL> create table t1 (id int);

Table created.

09:39:52 SQL> set role prv_role;

set role prv_role

*

ERROR at line 1:

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

09:40:02 SQL> set role prv_role identified by oracle;   ——启用非默认角色，如果有口令，需通过password 启用

Role set.

USERNAME        GRANTED_ROLE                   ADMIN_OPTION    DEFAULT_ROLE    OS_GRANTE

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

TOM             ANNY_ROLE                      NO              NO              NO

TOM             PRV_ROLE                       NO              NO              NO

TOM             PUB_ROLE                       NO              YES             NO

TOM             RESOURCE                       NO              NO              N

09:40:17 SQL> select * from scott.emp;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7369 SMITH      CLERK           7902 17-DEC-80        800                    20

7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

7566 JONES      MANAGER         7839 02-APR-81       2975                    20

7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

7788 SCOTT      ANALYST         7566 19-APR-87       3000        100         40

7839 KING       PRESIDENT            17-NOV-81       5000                    10

7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

7900 JAMES      CLERK           7698 03-DEC-81        950                    30

7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

——启用非 default role 后，用户就具有了非default role 的权限

7、角色回收（revoke）

SQL> revoke pub_role ,prv_role from tom,rose;

Revoke succeeded.

8、删除角色（drop）

09:46:40 SQL> drop role pub_role;

Role dropped.

09:46:44 SQL> drop role prv_role;

Role dropped.

9、与角色有关的视图

DBA_ROLES:

DBA_ROLE_PRIVS:

ROLE_ROLE_PRIVS:

DBA_SYS_PRIVS:

ROLE_SYS_PRIVS:

ROLE_TAB_PRIVS:

SESSION_ROLES: