Oracle角色精簡總結

[i=s] 本帖最後由 zcs0237 於 2013-3-27 09:34 編輯

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';   //檢視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.

二、證明ROLE是一個Role Set(角色可以巢狀)

SQL> create role role5;

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

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

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>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的其他許可權。

[size=10.5pt]
************************************************
第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);

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from session_privs;

no rows selected     //查詢當前會話的最終許可權

2、恢復方法1：重新conn即恢復

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

2、恢復方法2：重新set role 即恢復

SQL> set role role1,role2;

Role set.            //使role1,role2生效

SQL> select * from session_privs;

PRIVILEGE            //查詢當前會話的最終許可權

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

CREATE SESSION

SELECT ANY TABLE

04.3-Role with Password

SQL> create role role5 identified by roles;

Role created.

SQL> alter role role5 not identified;

Role altered.

SQL> alter role role5 identified by role5;

Role altered.

SQL> set role role5;

ERROR at line 1:

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

SQL> set role role5 identified by role5;

Role set.  //使用帶有口令的role1生效

********************END***************************

oracle知識點精簡總結系列持續增加中：
Oracle使用者及使用者配置檔案精簡總結
http://www.itpub.net/thread-1775065-1-1.html
Oracle的登陸認證方式精簡總結
http://www.itpub.net/thread-1774785-1-1.html
例項恢復相關原理精簡總結
http://www.itpub.net/thread-1761630-1-1.html
Oracle許可權精簡總結
http://www.itpub.net/thread-1775562-1-1.html
Oracle角色精簡總結

http://www.itpub.net/thread-1775924-1-1.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/210154/viewspace-757221/，如需轉載，請註明出處，否則將追究法律責任。