Oracle角色精简总结

zcs0237 · 发表于 2013-3-27 09:35

本帖最后由 zcs0237 于 2013-5-19 19:23 编辑

a.欢迎对本帖补充、建议、更正
b.测试环境rhel5.4+Ora10.2.0.1.0
c.为节省篇幅，部分输出结果做了精简

************************************************

第01部分角色基础知识

角色是非模式对象,它不由任可用户拥有，也不属于任何方案。

01.1-角色是一个集合

一、证明ROLE是一个Privilege Set

SQL> select grantee,privilege from dba_sys_privs where grantee='RESOURCE';

GRANTEE PRIVILEGE//查看resource角色拥有的权限

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

RESOURCE CREATE TRIGGER

RESOURCE CREATE SEQUENCE

RESOURCE CREATE TYPE

RESOURCE CREATE PROCEDURE

RESOURCE CREATE CLUSTER

RESOURCE CREATE OPERATOR

RESOURCE CREATE INDEXTYPE

RESOURCE CREATE TABLE

8 rows selected.

二、证明ROLE是一个Role Set(角色可以嵌套)

SQL> create role role5;

Role created. //创建新的角色role5

SQL> select * from dba_roles where role='CONNECT';

ROLE PASSWORD //证明resource是一个role

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

RESOURCE NO

SQL> grant connect to role5;

Grant succeeded. //将connect角色关联到role5

01.2-角色用来高效管理权限

有一组人，他们的所需的权限是一样的，则可以：

一、首先将角色赋给组内的各个用户

SQL>grant role1 to zcs1;

SQL>grant role1 to zcs1 ;

……

二、其次针对角色进行管理(将权限、角色授给某个角色)

SQL>grant privilege1 to role1;

SQL>revoke privilege1 from role1;

……

************************************************

第02部分预定义角色

一、预定义角色是系统自动创建的一些常用的角色

SQL> select count(*) from dba_roles;

COUNT(*)

----------

33 //预定义角色的总数

二、角色所包含的权限可以用以下语句查询：

sql>select * from role_sys_privs where role='角色名';

三、兼容角色：CONNECT, RESOURCE, DBA

oracle建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。

1、主要是简化数据库管理而提供的

对于DBA用户：直接授予dba角色。

对于普通用户：一般授予connect, resource角色。

2、这些预定义角色主要是为了向后兼容，将来的版本中这些角色可能不会作为预定义角色

SQL> select * from dba_roles where role='DBA';

ROLE PASSWORD

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

DBA NO //通过此查询证明DBA是角色名

02.1-Connect Role

临时用户，通常只赋予他们CONNECT role。

一、查看CONNECT角色的权限

SQL> select grantee,privilege from dba_sys_privs where grantee='CONNECT';

CONNECT CREATE SESSION

二、新建用户zcs3

SQL> create user zcs3 identified by zcs3;

User created.

SQL> conn zcs3/zcs3

ERROR:

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

三、关联CONNECT角色到zcs3

角色就像权限：可将其授给用户或其它角色。

SQL> conn / as sysdba

Connected.

SQL> grant connect to zcs3;

Grant succeeded. //关联connect角色到zcs3

SQL> conn zcs3/zcs3

Connected.

SQL> select * from session_privs;

PRIVILEGE

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

CREATE SESSION

SELECT ANY TABLE //查询当前会话的最终权限

02.2-Resource Role

正式、可靠的用户，如开发人员正式的数据库用户可以授予RESOURCE role。一般创建用户时，如果没有特殊需求只要将resource和connect角色赋予用户即可。

一、提供创建对象的权限

SQL> select grantee,privilege from dba_sys_privs where grantee='RESOURCE';

GRANTEE PRIVILEGE

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

RESOURCE CREATE TRIGGER 触发器

RESOURCE CREATE SEQUENCE 序列

RESOURCE CREATE TYPE 类型

RESOURCE CREATE PROCEDURE过程

RESOURCE CREATE CLUSTER 簇

RESOURCE CREATE OPERATOR

RESOURCE CREATE INDEXTYPE索引

RESOURCE CREATE TABLE 表

8 rows selected.

二、也会授予UNLIMITED TABLESPACE系统权限
1、创建用户，指定密码

SQL> create user zcs identified by zcs;

User created.

SQL> select username,default_tablespace from dba_users where username='ZCS';

USERNAME DEFAULT_TABLESPACE

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

ZCS USERS

2、关联connect、resource角色到zcs

SQL> grant connect,resource to zcs;

Grant succeeded.

SQL> select GRANTEE, GRANTED_ROLE from dba_role_privs where GRANTEE='ZCS';

GRANTEE GRANTED_ROLE //查看用户所具有的角色

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

ZCS RESOURCE

ZCS CONNECT

3、自动授予UNLIMITED TABLESPACE系统权限

此系统权限会覆盖所有的单个表空间限额，并向用户提供所有表空间（包括SYSTEM和SYSAUX）的无限制限额，这对数据库系统管理是一大隐患。

SQL> select GRANTEE, PRIVILEGE from dba_sys_privs where GRANTEE='ZCS';

GRANTEE PRIVILEGE //查询用户所具有的系统权限

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

ZCS UNLIMITED TABLESPACE

SQL> select TABLESPACE_NAME,USERNAME,BYTES,MAX_BYTES from dba_ts_quotas;

TABLESPACE_NAME USERNAME BYTES MAX_BYTES //查询表空间限制

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

SYSAUX OLAPSYS 16318464 -1

SYSAUX SYSMAN 50790400 -1

SYSAUX DMSYS 262144 209715200

SQL> conn zcs/zcs

Connected.

SQL> create table zcs(id int);

Table created.

02.3-DBA Role

除非真正需要，dba role(数据库管理员角色)权限不应随便授予那些不是很重要的用户。

SQL> SELECT GRANTEE, GRANTED_ROLE FROM dba_role_privs WHERE granted_role = 'DBA';

GRANTEE GRANTED_ROLE //查看角色授予了哪些用户

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

SYS DBA

SYSMAN DBA

ZCS1 DBA

SYSTEM DBA

SQL> select * from v$pwfile_users;

USERNAME SYSDBA SYSOPER //查SYSDBA/SYSOPER权限的用户

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

SYS TRUE TRUE

TEST TRUE FALSE

一、DBA role拥有所有的系统权限，可以操作全体用户的任意基表(包括删除)

SQL> select count(*) from dba_sys_privs where grantee='DBA';

COUNT(*)

----------

160

SQL> create user zcs1 identified by zcs1;

User created.

SQL> grant dba to zcs1;

Grant succeeded.

SQL> CONN ZCS1/ZCS1

Connected.

SQL> select count(*) from session_privs;

COUNT(*)

----------

161

SQL> SET PAGESIZE 999

SQL> select * from SESSION_ROLES order by ROLE;

ROLE

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

DBA

DELETE_CATALOG_ROLE

EXECUTE_CATALOG_ROLE

EXP_FULL_DATABASE

GATHER_SYSTEM_STATISTICS

HS_ADMIN_ROLE

IMP_FULL_DATABASE

JAVA_ADMIN

JAVA_DEPLOY

OLAP_DBA

SCHEDULER_ADMIN

SELECT_CATALOG_ROLE

WM_ADMIN_ROLE

XDBADMIN

XDBWEBSERVICES

15 rows selected.

二、DBA role包括无限制的空间限额

SQL> conn zcs1/zcs1

Connected.

SQL> select GRANTEE, PRIVILEGE from dba_sys_privs where GRANTEE='ZCS1';

GRANTEE PRIVILEGE

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

ZCS1 UNLIMITED TABLESPACE

SQL> select TABLESPACE_NAME,USERNAME,BYTES,MAX_BYTES from dba_ts_quotas where USERNAME='ZCS1';

no rows selected //zcs1无限额

三、给其他用户授予各种权限的能力

一个具有DBA角色的用户可以撤消任何别的用户甚至别的DBA的CONNECT、RESOURCE 和DBA的其他权限。

************************************************
第03部分自定义角色

修改（grant）角色关联的权限，则授予该角色所有的用户都会立即自动获得修过的权限。

自定义角色一般是有 dba 来完成的，如果一般的用户想建立，则需要有 create role的系统权限。

03.1-建用户ZCS1(zcs1无角色,无权)

SQL> create user zcs1 identified by zcs1;

User created. //创建用户

SQL> select USERNAME from dba_users where USERNAME='ZCS1';

ZCS1 //说明zcs1是否存在

SQL> select * from dba_sys_privs where GRANTEE='ZCS1';

no rows selected //新建用户默认不拥有任何权限

SQL> select * from dba_role_privs where GRANTEE='ZCS1';

no rows selected//新建用户默认不拥有任何角色

SQL> conn zcs1/zcs1

ERROR: //新建用户默认不能连接到数据

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

03.2-建角色ROLE1(ROLE1无权)

SQL> create role role1;

Role created. //创建无任何权限的角色

SQL> SELECT role,password_required FROM dba_roles where ROLE='ROLE1'; //验证新建角色是否已存在

ROLE PASSWORD

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

ROLE1 NO

SQL> select grantee,privilege from dba_sys_privs where grantee='ROLE1'; //ROLE1角色不包含任何权限

no rows selected

03.3-ROLE1联ZCS1(zcs1角色=role1,无权)

SQL> grant role1 to zcs1;

Grant succeeded. //将角色授矛给用户

SQL> select * from dba_role_privs where GRANTEE='ZCS1';

GRANTEE GRANTED_ROLE ADM DEF

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

ZCS1 ROLE1 NO YES //zcs1用户有角色

SQL> select * from dba_sys_privs where GRANTEE='ZCS1';

no rows selected //zcs1用户无权限

SQL> conn zcs1/zcs1

ERROR: //此时zcs1还是不能登陆

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

03.4-Role1联create session(zcs1=create session)

SQL> conn / as sysdba

Connected.

SQL> grant create session to role1;

Grant succeeded.

SQL> select grantee,privilege from dba_sys_privs where grantee='ROLE1';

GRANTEE PRIVILEGE

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

ROLE1 CREATE SESSION

SQL> conn zcs1/zcs1

Connected.

SQL> select * from SESSION_ROLES order by ROLE;

ROLE1 //查看当前会话拥有的角色

SQL> select * from USER_ROLE_PRIVS;

USERNAME GRANTED_ROLE ADM DEF OS_

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

ZCS1 ROLE1 NO YES NO //查看当前用户的角色

SQL> conn / as sysdba;

Connected.

SQL> drop role role1;

Role dropped. //删除角色

SQL> select * from dba_sys_privs where GRANTEE='ZCS1';

no rows selected //角色删除后，原来拥用该角色的用户不再拥有该角色及相应的权限。

************************************************

第04部分开关自定义角色

SQL> show parameter MAX_ENABLED_ROLES

max_enabled_roles integer 150 //最大可生效角色数

04.1-SYS永久减小ZCS的权限(默认角色)
1、核心命令
alter user zcs3 DEFAULT ROLE ALL EXCEPT RESOURCE;
alter user zcs3 DEFAULT ROLE ALL;
2、经测试有以下两点规律
Shutdown/startup以上结果不会自动恢复
必须用DBA用户alter user才能恢复正常
一、创建用户zcs3并授予resource,connect角色

SQL> grant resource,connect to zcs3;

Grant succeeded. //只影响使用GRANT直接授予用户的角色

SQL> conn zcs3/zcs3;

Connected.

SQL> conn / as sysdba

Connected.

SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';

GRANTEE GRANTED_ROLE DEF

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

ZCS3 RESOURCE YES

ZCS3 CONNECT YES

二、设置缺省的角色为NONE后，无法登陆

SQL> alter user zcs3 DEFAULT ROLE NONE;

User altered.

SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';

GRANTEE  GRANTED_ROLE  DEFAULT_ROLE
------------ ------------       ------------
ZCS3       RESOURCE    NO //resource已禁用
ZCS3       CONNECT       NO //connect已禁用
SQL> conn zcs3/zcs3;
ERROR:
ORA-01045: user ZCS3 lacks CREATE SESSION privilege; logon denied
SQL> conn /  as sysdba;
Connected.
三、重置角色后可以正常登陆
1、重启数据不会自动恢复正常
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL>select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';
GRANTEE    GRANTED_ROLE DEFAULT_ROLE
------------ ------------ ------------
ZCS3       RESOURCE    NO
ZCS3       CONNECT    NO
2、alter user手动重置正常
SQL> conn / as sysdba
Connected.
SQL> alter user zcs3 DEFAULT ROLE ALL;
User altered.
';SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';

GRANTEE    GRANTED_ROLE DEFAULT_ROLE
------------ ------------ ------------
ZCS3       RESOURCE    YES
ZCS3       CONNECT    YES
SQL> CONN zcs3/zcs3;
Connected.
四、默认角色为除resource之外的所有角色
SQL> alter user zcs3 DEFAULT ROLE ALL EXCEPT RESOURCE;
User altered.

SQL> select GRANTEE,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where GRANTEE='ZCS3';
GRANTEE    GRANTED_ROLE DEFAULT_ROLE
------------ ------------ ------------
ZCS3       RESOURCE    NO //resource已禁用
ZCS3       CONNECT    YES;

04.2-ZCS给自已临时减小权限
1、核心命令

set role none;

set role all;

set role role1,role2;

2、经测试有以下两点规律

自已禁用自已的ROLE

临时:set role all 或重新conn即恢复

一、用SYS操作：关联权限到角色role1
1、创建role1,并关联create session系统权限

SQL> create role role1;

Role created.

SQL> grant create session to role1;

Grant succeeded.

SQL> select grantee,privilege from dba_sys_privs where grantee='ROLE1';

GRANTEE PRIVILEGE

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

ROLE1 CREATE SESSION

2、创建role2,并关联select any table系统权限

SQL> create role role2;

Role created.

SQL> grant select any table to role2;

Grant succeeded.

SQL> select grantee,privilege from dba_sys_privs where grantee='ROLE2';
GRANTEE PRIVILEGE
------------ -----------------
ROLE2 SELECT ANY TABLE
二、用SYS操作：关联角色role1到用户zcs

SQL> create user ZCS identified by ZCS;

User created. //创建用户ZCS

SQL> grant role1,role2 to ZCS;

Grant succeeded. //关联role1,role2到ZCS

SQL> select * from dba_role_privs where GRANTEE='ZCS';

GRANTEE GRANTED_ROLE ADM DEF //查看ZCS拥有的角色

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

ZCS ROLE1 NO YES

ZCS ROLE2 NO YES

三、用ZCS操作：二.结果的验证

SQL> conn ZCS/ZCS

Connected.

SQL> conn ZCS/ZCS

Connected.

SQL> select empno,ename,deptno,mgr from scott.emp where empno in(7499,7788);

EMPNO ENAME DEPTNO MGR

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

7499 ALLEN 30 7698

7788 SCOTT 20 7566

SQL> select * from SESSION_ROLES order by ROLE;

ROLE //查看当前会话拥有的角色

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

ROLE1

ROLE2

SQL> select USERNAME, GRANTED_ROLE from USER_ROLE_PRIVS;
USERNAME    GRANTED_ROLE  //查看当前会话拥有的角色
------------ ------------
ZCS       ROLE1
ZCS       ROLE2

SQL> select * from user_sys_privs;

no rows selected //直接授权为空

SQL> select * from session_privs;

PRIVILEGE

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

CREATE SESSION

SELECT ANY TABLE //查询当前会话的最终有效权限

四、用ZCS操作：开关角色可暂时开关权限
1、set role禁用本用户拥有的角色

SQL> conn ZCS/ZCS

SQL> set role none;

Role set. //none=所有失效, all=所有生效

SQL> select empno,ename,deptno,mgr from scott.emp where empno in(7499,7788);