oracle dba小记第十一篇 管理角色roles
管理角色(roles)：
a name set privileges(权限集合）
先把权限赋予角色，再把角色赋予用户.
roles can be granted to and revoked from users with the same commands that are used to grant and
revoke system privileges.
roles can be granted to any user or role. however,a role canot be granted to itself and cannot be
granted circulry.
a role can consist of both system and object privileges.
a role can be enabled or disabled for each user who is granted the role.
a role can require a password to be enabled.
each role name must be unique among existing usernames and role names.
roles are not owned by anyone; and the are not in any schema.
roles have their descriptions stored in the data dictionary.
benefits of roles：
easier privilege management
dynamic privilege management
selective availability of privileges
can be granted throuth the operating system.
表空间：
dba_roles;
session_roles;
role_role_privs;（角色里包含了哪些角色）
实验一：创建角色授权
1.
SQL> conn /as sysdba;
Connected.
SQL> create user u1 identified by bbk_12345 default tablespace assm quota unlimited on assm;
User created.
SQL> create user u2 identified by bbk_12345 default tablespace assm quota unlimited on assm;
User created.
SQL> create user u3 identified by bbk_12345 default tablespace assm quota unlimited on assm;
User created.
(以dba登录数据库，在assm表空间中创建u1/u2/u3密码为bbk_12345的三个用户，无配额限制）
2.
SQL> create role r1;
Role created.
SQL> create role r2 identified by bbk_12345;
Role created.
SQL> desc dba_roles;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLE NOT NULL VARCHAR2(30)
PASSWORD_REQUIRED VARCHAR2(8)
SQL> set linesize 1000;
SQL> select * from dba_roles order by role;
ROLE PASSWORD_REQUIRED
------------------------------------------------------------------------------------------
AQ_ADMINISTRATOR_ROLE NO
AQ_USER_ROLE NO
AUTHENTICATEDUSER NO
CONNECT NO
CTXAPP NO
DBA
NO
DELETE_CATALOG_ROLE NO
EJBCLIENT NO
EXECUTE_CATALOG_ROLE NO
EXP_FULL_DATABASE NO
GATHER_SYSTEM_STATISTICS NO
ROLE PASSWORD_REQUIRED
------------------------------------------------------------------------------------------ ------------------------
GLOBAL_AQ_USER_ROLE GLOBAL
HS_ADMIN_ROLE NO
IMP_FULL_DATABASE NO
JAVADEBUGPRIV NO
JAVAIDPRIV NO
JAVASYSPRIV NO
JAVAUSERPRIV NO
JAVA_ADMIN NO
JAVA_DEPLOY NO
LOGSTDBY_ADMINISTRATOR NO
MGMT_USER NO
ROLE PASSWORD_REQUIRED
------------------------------------------------------------------------------------------ ------------------------
OEM_ADVISOR NO
OEM_MONITOR NO
OLAP_DBA NO
OLAP_USER NO
PLUSTRACE NO
R1 NO
R2 YES
RECOVERY_CATALOG_OWNER NO
RESOURCE NO
SCHEDULER_ADMIN NO
SELECT_CATALOG_ROLE
NO
ROLE PASSWORD_REQUIRED
------------------------------------------------------------------------------------------ ------------------------
WM_ADMIN_ROLE NO
XDBADMIN NO
XDBWEBSERVICES NO
36 rows selected.
(创建两个角色r1/r2 一个有密码一个没有密码)
3.
SQL> grant create session to r1;
Grant succeeded.
SQL> grant r1 to u1;
Grant succeeded.
（给角色的r1一个CREATE SESSION的权限，然后把r1的权限赋予给u1,这时u1就可以登录了）
SQL> conn u1/bbk_12345;
Connected.
SQL> select * from session_roles;
ROLE
------------------------------------------------------------------------------------------
PLUSTRACE
R1
SELECT * FROM session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
4.
SQL> show user;
USER is "U1"
SQL>
CREATE TABLE m(id int); 此时建表是失败的，因为没有create table权限
SQL> create table m(id int);
create table m(id int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba;
Connected.
SQL> grant create table to r2;
Grant succeeded.
GRANT CREATE TABLE TO r2; 给角色r2一个建表的权限，然后再把角色分配给t1;
SQL> grant r2 to u1;
Grant succeeded.
cSQL> conn u1/bbk_12345;
Connected.
SQL> select * from session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE SESSION
CREATE TABLE
SQL> select * from session_roles;
ROLE
------------------------------------------------------------------------------------------
PLUSTRACE
R1
R2
(此时 u1就有了两个角色的权限）
SQL> show user;
USER is "U1"
SQL> create table t(id int);
Table created.
SQL> insert into t values(0);
1 row created.
SQL> insert into t values(1);
1 row created.
SQL> select * from t;
ID
----------
0
1
SQL> commit;
Commit complete.
实验二：数据字典
role_role_privs(角色中包含哪些角色）
1.
GRANT r2 to r1;(把角色 r2授权给r1)
SQL>
Grant succeeded.
SELECT * FROM role_role_privs where role='R1';（角色里包含哪些角色）
ROLE GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
R1 R2 NO
2.
role_sys_privs; （查询角色里包含哪些系统特权）
SELECT * FROM ROLE_SYS_PRIVS WHERE role like 'R%';
ROLE PRIVILEGE
ADM
------------------------------ ---------------------------------------- ---
R1 CREATE SESSION NO
R2 CREATE TABLE
eg:
SQL> SET LINESIZE 50;
SQL> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE like 'CONNECT';
ROLE
--------------------------------------------------
PRIVILEGE
--------------------------------------------------
ADMIN_OPT
---------
CONNECT
CREATE SESSION
NO
3.
role_tab_privs;（查询角色里包含哪些object特权）
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)
实验三：predefined roles
connect：priilege to connect to the database,to create a cluster,a databae link,a sequence,
a sequence,a synonym, a table, and a view, and to alter a session.
resource: privilege to create a cluster,a table, and a sequence, and to create programmatic
objects such as procedures,functions,packages,indextypes,types,triggers,and operators.
DBA: all system privileges with the admin option,so the system privileges can be granted
to other users of the database or to roles.
select_catalog_role: ability to query the dictionary views and tables.
execute_catalog_role: privilege to execute the dictionary packages (sys-owned packages).
delete_catalog_role: ability to drop or re-create the dictionary packages.
also, when you run the catproc.sql script as part of the database creation,the script executes
catexp.sql,which creates two more roles:
exp_full_database: ability to make full and incremental exports of the database using the export
utility.
imp_full_database: abiltiy to perform full databae imports using the import utility. this is a
very powerful role.
connect;连接数据库特权
resource; 一般用户只要connect,resource就够了
dba;
SQL> set linesize 10000;
SQL> select * from role_sys_privs where role like 'CONNECT';
ROLE PRIVILEGE ADMIN_OPT
---------------------------------------------------------------------------------------
CONNECT CREATE SESSION
NO
establishing default roles:
a user can be assigned many roles.
a user can be assigned a defalut role.
limit the number of default roles for a user.
实验四：default role 和有passwd 的role:
(查看U1所拥有的权限，证明是可以登录数据库的，现以重新登录U1）
SQL> conn u1/bbk_12345;
Connected.
SQL>
SQL> select * from session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE SESSION
CREATE TABLE
EXIT;
2.
conn /as sysdba;
SQL> alter user u1 default role none;
User altered.
(取消u1的所有的默认权限，现在u1,登录失败）
SQL> conn u1/bbk_12345;
ERROR:
ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
3.
SQL> conn /as sysdba;
Connected.
SQL> alter user u1 default role r1;
User altered.
(现在给u1一个r1的角色，则登录成功）
conn t1/bbk_12345;
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
(因为有grant r2 to r1 ,所以r1也有create table 的权限，现在我们回收）
SQL> conn /as sysdba;
Connected.
SQL> revoke r2 from r1;
Revoke succeeded.
SQL> conn u1/bbk_12345;
Connected.
SQL> select * from session_privs;
PRIVILEGE
------------------------------------------------------------------------------------------------------------------------
CREATE SESSION
4.
SQL> conn u1/bbk_12345;
Connected.
SELECT * FROM session_privs;(CREATE SESSION只有这权限，所以不能创建表）
SQL> create table m(id int);
create table m(id int)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SET ROLE r1,r2 IDENTIFIED BY bbk_12345;(缺省是r1角色，让加密码的r2角色加入，则建表成功）
SQL> set role r1,r2 identified by bbk_12345;
Role set.
SQL> create table m(id int);
Table created.
enable and disable roles:
disable a role to temporarily revoke the role from a user.
enable a role to grant it temporarily.
the set role command enabled for a user at login.
default roles are enabled for a user at login.
a password may be required to enable a role.
getting role information:
information about roles can be obtained by querying the following views:
DBA_ROLES: all roles that exist in the database.
DBA_ROLE_PRIVS: Roles granted to users and roles.
ROLE_ROLE_PRIVS: Roles that are granted to roles.
DBA_SYS_PRIVS: system privileges granted to users and roles.
ROLE_SYS_PRIVS: object privileges granted to roles.
SESSION_ROLES: Roles that the usr currently has enabled.