有關Oracle role的總結
oracle的role算是對sys privilege 和object privilege的打包。
今天深入的測試了下，還算有不少的東西。
role不是schema物件
像table等在一個schema裡面不能有同名的schema object,但是可以有同名的table和role，如下。
SQL> conn test1/test1
Connected.
SQL> create role testrole;
Role created.
SQL> create table testrole as select *from all_objects;
Table created.
SQL> grant select on testrole to testrole;
Grant succeeded.
role和schema不同，有全域性的意思。
--切換一個使用者，把許可權放進testrole裡面。
SQL> conn test2/test2
Connected.
SQL> create table a as select *from obj;
Table created.
SQL> grant select on a to testrole;
Grant succeeded.
--但是話說回來，role testrole是test1使用者建立的，用test2賦給其他使用者的話是不允許。
SQL> grant testrole to test3;
grant testrole to test3
*
ERROR at line 1:
ORA-01919: role 'TESTROLE' does not exist
--用owner使用者來賦予role testrole就沒問題。
SQL> conn test1/test1
Connected.
SQL> grant testrole to test3;
Grant succeeded.
role是基於session級的
可以開啟兩個視窗，使用使用者test1,test3來進行測試。
session1:
SQL> conn test1/test1
Connected.
SQL> create role testrole;
Role created.
session2:
SQL> conn test3/test3
Connected.
session1:
當前session啟用role
可以使用set role或者dbms_session.set_role來啟用。
role的限制
--> 在pl/sql中的限制
在動態sql中，如果呼叫某些表的時候，透過role，會有table or view not found這類的exception， 這時候需要透過直接賦予object privilege來修正。
-->建立檢視時的問題
建立檢視時，很可能會有insuffisicant privilege的錯誤。可以參見http://space.itpub.net/23718752/viewspace-762805/
role的加密
如果10個人用同一個db 賬號，但是每個人又需要有不同的許可權，可以對role加密。
這樣每個人在使用的時候都能夠相應的透過密碼來啟用屬於自己的role。
SQL> create role user1_role identified by test1;
Role created.
SQL> create role user2_role identified by test2;
Role created.
SQL> create role user3_role identified by test3;
Role created.
SQL> create table t1 as select *from obj;
Table created.
SQL> create table t2 as select *from tab;
Table created.
SQL> create table t3 as select *from syn;
Table created.
SQL> grant select on t1 to user1_role;
Grant succeeded.
SQL> grant select on t2 to user2_role;
Grant succeeded.
SQL> grant select on t3 to user3_role;
Grant succeeded.
SQL> grant user1_role,user2_role,user3_role to test3;
Grant succeeded.
---初始化test3
SQL> conn / as sysdba
Connected.
--一下這句很關鍵，只指定使用者test3有connect的角色
SQL> alter user test3 default role connect;
User altered.
--使用test3來連入，
SQL> conn test3/test3
Connected.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
--先檢視是否可以訪問test1.t1
SQL> desc test1.t1
ERROR:
ORA-04043: object test1.t1 does not exist
--嘗試無密碼直接啟用role user1_role
SQL> set role user1_role ;
set role user1_role
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'USER1_ROLE'
--使用密碼啟用role user1_role
SQL> set role user1_role identified by test1;
Role set.
SQL> select *from session_roles;
ROLE
------------------------------
USER1_ROLE
SQL> desc test1.t1
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
使用另外一個session，使用同樣的賬號test3
SQL> conn test3/test3
Connected.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
SQL> set role user2_role identified by test2;
Role set.
SQL> desc test1.t2
Name Null? Type
----------------------------------------- -------- ----------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
secure application role
關於secure applicaton role可以是對role的高階使用，比如你可以限制某些Ip的機器才能啟用某些許可權。某些特定條件的使用者才能啟用某些許可權，甚至指定在每天的每個時間段才能啟用某些許可權。這些複雜的需求直接透過external role，dbms_session.set_role, set role等操作就不能實現，需要更細粒度的控制。
下面就舉一個例子，來讓指定的使用者才能啟用某些許可權。
SQL> select *from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
DIP 20 06-NOV-13
TSMSYS 22 06-NOV-13
TEST1 26 07-NOV-13
TEST2 30 10-NOV-13
DBSNMP 25 07-NOV-13
SYS 0 06-NOV-13
SYSTEM 5 06-NOV-13
OUTLN 11 06-NOV-13
8 rows selected.
test1下有個表mv_test， 只能透過使用者test2才能訪問。
(注意：要實現這個需求，如果對於dba賬戶來說沒有任何限制，因為dba賬戶本來就可以訪問test1.mv_test)
步驟如下：
建立role,指定透過內嵌dbms_session.set_role的包來啟用
SQL> conn test1/test1
Connected.
SQL> create role r1 identified using test1.setroles;
Role created.
SQL> grant select on test1.mv_test to r1;
Grant succeeded
SQL> grant r1 to test2;
Grant succeeded.
--注意一定要制定authid current_user
SQL> create or replace package setroles authid current_user as
2 procedure setrole;
3 end;
4 /
Package created.
--建立包體
SQL> create or replace package body setroles as
2 procedure setrole as
3 begin
4 if(sys_context('USERENV','current_user')='TEST2') then
5 dbms_session.set_role('R1');
6 end if;
7 end;
8 end;
9 /
--如果可以，可以把執行這個包的許可權開放，但是隻有合適的使用者才能啟用。
今天深入的測試了下，還算有不少的東西。
role不是schema物件
像table等在一個schema裡面不能有同名的schema object,但是可以有同名的table和role，如下。
SQL> conn test1/test1
Connected.
SQL> create role testrole;
Role created.
SQL> create table testrole as select *from all_objects;
Table created.
SQL> grant select on testrole to testrole;
Grant succeeded.
--切換一個使用者，把許可權放進testrole裡面。
SQL> conn test2/test2
Connected.
SQL> create table a as select *from obj;
Table created.
SQL> grant select on a to testrole;
Grant succeeded.
--但是話說回來，role testrole是test1使用者建立的，用test2賦給其他使用者的話是不允許。
SQL> grant testrole to test3;
grant testrole to test3
*
ERROR at line 1:
ORA-01919: role 'TESTROLE' does not exist
--用owner使用者來賦予role testrole就沒問題。
SQL> conn test1/test1
Connected.
SQL> grant testrole to test3;
Grant succeeded.
可以開啟兩個視窗，使用使用者test1,test3來進行測試。
session1:
SQL> conn test1/test1
Connected.
SQL> create role testrole;
Role created.
session2:
SQL> conn test3/test3
Connected.
session1:
SQL> grant select on t1 to testrole;
Grant succeeded.
SQL> grant select on testrole to test3;
Grant succeeded.
ERROR:
ORA-04043: object test1.t1 does not exist
session2 再次登入：
SQL> conn test3/test3
Connected.
SQL> desc test1.t1
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
還有一點需要說明一下，如果直接賦予object privilege，在當前session就會生效，這也是和role的一個不同之處。
Grant succeeded.
SQL> grant select on testrole to test3;
Grant succeeded.
session2:
SQL> desc test1.t1ERROR:
ORA-04043: object test1.t1 does not exist
session2 再次登入：
SQL> conn test3/test3
Connected.
SQL> desc test1.t1
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
還有一點需要說明一下，如果直接賦予object privilege，在當前session就會生效，這也是和role的一個不同之處。
當前session啟用role
可以使用set role或者dbms_session.set_role來啟用。
role的限制
--> 在pl/sql中的限制
在動態sql中，如果呼叫某些表的時候，透過role，會有table or view not found這類的exception， 這時候需要透過直接賦予object privilege來修正。
-->建立檢視時的問題
建立檢視時，很可能會有insuffisicant privilege的錯誤。可以參見http://space.itpub.net/23718752/viewspace-762805/
role的加密
如果10個人用同一個db 賬號，但是每個人又需要有不同的許可權，可以對role加密。
這樣每個人在使用的時候都能夠相應的透過密碼來啟用屬於自己的role。
SQL> create role user1_role identified by test1;
Role created.
SQL> create role user2_role identified by test2;
Role created.
SQL> create role user3_role identified by test3;
Role created.
SQL> create table t1 as select *from obj;
Table created.
SQL> create table t2 as select *from tab;
Table created.
SQL> create table t3 as select *from syn;
Table created.
SQL> grant select on t1 to user1_role;
Grant succeeded.
SQL> grant select on t2 to user2_role;
Grant succeeded.
SQL> grant select on t3 to user3_role;
Grant succeeded.
Grant succeeded.
SQL> conn / as sysdba
Connected.
--一下這句很關鍵，只指定使用者test3有connect的角色
SQL> alter user test3 default role connect;
User altered.
--使用test3來連入，
SQL> conn test3/test3
Connected.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
--先檢視是否可以訪問test1.t1
SQL> desc test1.t1
ERROR:
ORA-04043: object test1.t1 does not exist
--嘗試無密碼直接啟用role user1_role
SQL> set role user1_role ;
set role user1_role
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'USER1_ROLE'
--使用密碼啟用role user1_role
SQL> set role user1_role identified by test1;
Role set.
SQL> select *from session_roles;
ROLE
------------------------------
USER1_ROLE
SQL> desc test1.t1
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
使用另外一個session，使用同樣的賬號test3
SQL> conn test3/test3
Connected.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
SQL> set role user2_role identified by test2;
Role set.
SQL> desc test1.t2
Name Null? Type
----------------------------------------- -------- ----------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
secure application role
關於secure applicaton role可以是對role的高階使用，比如你可以限制某些Ip的機器才能啟用某些許可權。某些特定條件的使用者才能啟用某些許可權，甚至指定在每天的每個時間段才能啟用某些許可權。這些複雜的需求直接透過external role，dbms_session.set_role, set role等操作就不能實現，需要更細粒度的控制。
下面就舉一個例子，來讓指定的使用者才能啟用某些許可權。
SQL> select *from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
DIP 20 06-NOV-13
TSMSYS 22 06-NOV-13
TEST1 26 07-NOV-13
TEST2 30 10-NOV-13
DBSNMP 25 07-NOV-13
SYS 0 06-NOV-13
SYSTEM 5 06-NOV-13
OUTLN 11 06-NOV-13
8 rows selected.
test1下有個表mv_test， 只能透過使用者test2才能訪問。
(注意：要實現這個需求，如果對於dba賬戶來說沒有任何限制，因為dba賬戶本來就可以訪問test1.mv_test)
步驟如下：
建立role,指定透過內嵌dbms_session.set_role的包來啟用
SQL> conn test1/test1
Connected.
SQL> create role r1 identified using test1.setroles;
Role created.
SQL> grant select on test1.mv_test to r1;
Grant succeeded
SQL> grant r1 to test2;
Grant succeeded.
--注意一定要制定authid current_user
SQL> create or replace package setroles authid current_user as
2 procedure setrole;
3 end;
4 /
Package created.
SQL> create or replace package body setroles as
2 procedure setrole as
3 begin
4 if(sys_context('USERENV','current_user')='TEST2') then
5 dbms_session.set_role('R1');
6 end if;
7 end;
8 end;
9 /
--如果可以，可以把執行這個包的許可權開放，但是隻有合適的使用者才能啟用。
SQL> grant execute on test1.setroles to public;
--用測試使用者連入
SQL> conn test2/test2
Connected.
--檢視當前持有的role
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> desc test1.mv_test
ERROR:
ORA-04043: object test1.mv_test does not exist
--執行指定的包以後，來驗證
SQL> exec test1.setroles.setrole;
PL/SQL procedure successfully completed.
SQL> select *from session_roles;
ROLE
------------------------------
R1
SQL> desc test1.mv_test
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
OBJECT_NAME NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
選擇另外一個使用者來比較
--新建立一個使用者。
SQL> conn / as sysdba
Connected.
SQL> create user test3 identified by test3;
User created.
SQL> grant connect,resource to test3;
Grant succeeded.
SQL> conn test3/test3
Connected.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> desc test1.mv_test
ERROR:
ORA-04043: object test1.mv_test does not exist
--執行指定的包，來驗證。
SQL> exec test1.setroles.setrole;
PL/SQL procedure successfully completed.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> desc test1.mv_test
ERROR:
ORA-04043: object test1.mv_test does not exist
還有像external role,global之類的role可能使用不是很廣泛，先說到這。後面後加以補充。
--用測試使用者連入
SQL> conn test2/test2
Connected.
--檢視當前持有的role
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> desc test1.mv_test
ERROR:
ORA-04043: object test1.mv_test does not exist
--執行指定的包以後，來驗證
SQL> exec test1.setroles.setrole;
PL/SQL procedure successfully completed.
SQL> select *from session_roles;
ROLE
------------------------------
R1
SQL> desc test1.mv_test
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
OBJECT_NAME NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
選擇另外一個使用者來比較
--新建立一個使用者。
Connected.
SQL> create user test3 identified by test3;
User created.
SQL> grant connect,resource to test3;
Grant succeeded.
SQL> conn test3/test3
Connected.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> desc test1.mv_test
ERROR:
ORA-04043: object test1.mv_test does not exist
--執行指定的包，來驗證。
SQL> exec test1.setroles.setrole;
PL/SQL procedure successfully completed.
SQL> select *from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
SQL> desc test1.mv_test
ERROR:
ORA-04043: object test1.mv_test does not exist
還有像external role,global之類的role可能使用不是很廣泛，先說到這。後面後加以補充。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-776238/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 有關role的一點總結!
- 有關dblink有域名的總結：
- ORACLE關於NULL的總結OracleNull
- 關於ORACLE鎖的總結Oracle
- 關於oracle裡的process總結Oracle
- 關於ORACLE的一點總結Oracle
- 關於oracle synonym 的總結整理Oracle
- oracle set roleOracle
- 有關lock的一點測試總結！
- 有關ADDM試驗總結:
- Oracle OCP(30)：ROLEOracle
- 聊聊Oracle Default RoleOracle
- 面試總結:Promise有關面試的知識點。面試Promise
- 有關temp表空間的一點總結！
- 關於oracle中session跟蹤的總結OracleSession
- 關於Oracle塊的一些總結Oracle
- 關於Oracle Timezone的一點總結Oracle
- 關於ORACLE的鎖表與解鎖總結Oracle
- 有關連結串列的小技巧，我都給你總結好了
- 【NUMBER】有關Oracle NUMBER型別定義中precision和scale的測試和總結Oracle型別
- 有關10g em登陸的問題總結
- oracle: default role 詳解(轉)Oracle
- Oracle 角色 自定義角色 RoleOracle
- 關於oracle閃回資料歸檔的總結Oracle
- 關於oracle可傳輸表空間的總結Oracle
- 關於oracle 11g acs的一點總結：Oracle
- 自己做oracle試驗的相關總結之二Oracle
- 關於drop操作對role的影響
- ORACLE鎖的總結Oracle
- Oracle 總結Oracle
- 關於近期的總結
- 關於UIWebView的總結UIWebView
- 關於BeautifulSoup的總結
- 關於HTML的總結HTML
- Oracle OCP(31)：USER & ROLE & PRIVILEGE 其它Oracle
- ORACLE EXPLAIN PLAN的總結OracleAI
- ORACLE中impdp的總結Oracle
- ORACLE中的EXPDP總結Oracle