角色的作用
简化权限管理 动态进行权限管理 有选择的使用权限
角色是一组权限的集合
将权限分配给角色及将角色分配给用户 角色和角色之间可以互相授权
预定义角色 任何一个用户的都会给其两个角色 connect和resource 授予resource就可以创建表索引等等
保护角色 角色可以是非默认角色
可以通过验证保护角色
还可以通过编程保护角色
将角色分配给用户
SQL> desc role_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> desc role_tab_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
这两个数据字典查看角色的相关信息
SQL> create role r1;
Role created.
SQL> desc dba_roles;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PASSWORD_REQUIRED VARCHAR2(8)
AUTHENTICATION_TYPE VARCHAR2(11)
SQL> select * from dba_roles where role='R1';
ROLE PASSWORD_REQUIRE
------------------------------------------------------------ ----------------
AUTHENTICATION_TYPE
----------------------
R1 NO
NONE
开始给R1授予权限
SQL> grant select any dictionary to R1;
Grant succeeded.
SQL> select * from role_tab_privs where role ='R1';
ROLE OWNER TABLE_NAME
-------------------- -------------------- --------------------
COLUMN_NAME PRIVILEGE GRANTA
-------------------- -------------------- ------
R1 SCOTT EMP
SELECT NO
SQL> grant r1 to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select count(*) from dba_tables;
COUNT(*)
----------
2855
SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> grant r1 to hr with admin option;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
USERNAME
------------------------------------------------------------
GRANTED_ROLE ADMIN_ DEFAUL
------------------------------------------------------------ ------ ------
HR
DBA NO YES
HR
R1 YES YES
HR
RESOURCE NO YES
SQL> grant r1 to oe; 把r1角色给oe
Grant succeeded.
SQL> conn oe/oe
Connected.
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
USERNAME
------------------------------------------------------------
GRANTED_ROLE ADMIN_ DEFAUL
------------------------------------------------------------ ------ ------
OE
R1 NO YES
OE
RESOURCE NO YES
OE
XDBADMIN NO YES
SQL> conn / as sysdba
Connected.
SQL>
SQL> revoke r1 from hr;
Revoke succeeded.
SQL> conn oe/oe
Connected.
SQL> select count(*) from scott.emp;
COUNT(*)
----------
14
不会因为r1角色从hr那回收而影响oe 不进行及联
SQL> conn / as sysdba
Connected.
SQL>
SQL> revoke r1 from oe
2 ;
Revoke succeeded.
SQL> grant r1 to hr with admin option;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> grant r1 to oe with admin option;
Grant succeeded.
SQL> conn oe/oe
Connected.
SQL> grant r1 to hr with admin option;
Grant succeeded.
角色和用户之间是可以循环授权的
创建一个R2角色一个R3角色
SQL> conn / as sysdba
Connected.
SQL>
SQL> create role r2;
Role created.
SQL> grant select any dictionary to r2; 能够查询所有的数据字典
Grant succeeded.
SQL> create role r3;
Role created.
SQL> grant update on scott.emp to r3; 能够update emp表
Grant succeeded.
SQL> revoke select any dictionary from r1;
Revoke succeeded.
SQL> revoke select on scott.emp from r1; 收回r1的所有权限
Revoke succeeded.
SQL> select privilege from role_tab_privs where role='R1';
no rows selected 查询R1没有任何权限
SQL> select privilege from role_sys_privs where role='R1';
no rows selected 查询R1也没有任何系统权限
SQL> grant r2 to r3;
Grant succeeded.
SQL> select privilege from role_sys_privs where role='R2';
PRIVILEGE
--------------------
SELECT ANY DICTIONAR
Y
SQL> select privilege from role_tab_privs where role='R2';
no rows selected
SQL> select privilege from role_sys_privs where role='R3';
no rows selected
SQL> select privilege from role_tab_privs where role='R3';
PRIVILEGE
--------------------
UPDATE
SQL> desc role_role_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
SQL> select role,granted_role from role_role_privs where role='R3';
ROLE
--------------------
GRANTED_ROLE
------------------------------------------------------------
R3
R2
把R2授权给了R3 代表了R3拥有这两个权限
SQL> grant r3 to r1;
Grant succeeded.
SQL> grant r1 to r2;
grant r1 to r2
*
ERROR at line 1:
ORA-01934: circular role grant detected
角色与角色之间不能循环授权 不能形成一个圈
SQL> conn / as sysdba
Connected.
SQL> revoke r3 from r1;
Revoke succeeded.
SQL> revoke r2 from r3;
Revoke succeeded.
SQL> grant r2 to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select count(*) from dba_tables;
COUNT(*)
----------
2855
SQL> desc role_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> select role,privilege from role_sys_privs where role='R2';
ROLE PRIVILEGE
-------------------- --------------------
R2 SELECT ANY DICTIONAR
Y
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
USERNAME
------------------------------------------------------------
GRANTED_ROLE ADMIN_ DEFAUL
------------------------------------------------------------ ------ ------
HR
DBA NO YES
HR
R2 NO YES
HR
R3 NO YES
USERNAME
------------------------------------------------------------
GRANTED_ROLE ADMIN_ DEFAUL
------------------------------------------------------------ ------ ------
HR
RESOURCE NO YES
改变用户的默认角色
SQL> alter user hr default role resource,r2;
User altered.
SQL> conn hr/hr
Connected.
SQL> select username,granted_role,admin_option,default_role from user_role_privs;
USERNAME
------------------------------------------------------------
GRANTED_ROLE ADMIN_ DEFAUL
------------------------------------------------------------ ------ ------
HR
DBA NO NO
HR
R2 NO YES
HR
R3 NO NO
USERNAME
------------------------------------------------------------
GRANTED_ROLE ADMIN_ DEFAUL
------------------------------------------------------------ ------ ------
HR
RESOURCE NO YES
SQL> select count(*) from dba_tables;
COUNT(*)
----------
2855
SQL> uptate scott.emp set sal=0;
SP2-0734: unknown command beginning "uptate sco..." - rest of line ignored.
SQL> update scott.emp set sal=0;
update scott.emp set sal=0
*
ERROR at line 1:
ORA-00942: table or view does not exist
默认角色被改变 不生效
如何生效?
SQL> set role r3;
Role set.
SQL> update scott.emp set sal=0;
14 rows updated.
SQL> roll
Rollback complete.
角色非常多的时候不用的时候打开 用不到的时候把它设置成非默认的role
对角色进行验证
SQL> drop role r2;
Role dropped.
SQL> create role r2 identified by a;
Role created.
SQL> grant select any dictionary to r2;
Grant succeeded.
SQL> grant r2 to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
USERNAME
------------------------------------------------------------
GRANTED_ROLE ADMIN_ DEFAUL
------------------------------------------------------------ ------ ------
HR
DBA NO NO
HR
R2 NO NO
HR
R3 NO NO
USERNAME
------------------------------------------------------------
GRANTED_ROLE ADMIN_ DEFAUL
------------------------------------------------------------ ------ ------
HR
RESOURCE NO YES
所有被密码保护的角色 它就不会在作为用户的默认角色
SQL> set role r2 identified by a; 密码保护登入
Role set.
SQL> select count(*) from dba_tables;
COUNT(*)
----------
2855
角色是各种权限的集合 既可以包含系统权限又可以对象角色,角色和角色之间不可以循环授权,角色和用户都可以,角色授给某个用户默认设置成默认角色 角色可以用密码保护,授给任何一个用户,就不会设置成默认角色,这样不会滥用权限。