ããè§è²æ¯ä¸ç»ç¸å
³æéçå½åéåï¼ä½¿ç¨è§è²æ主è¦çç®çæ¯ç®åæé管çèä¸æ¦è¿ä¸ªéåçæéè¶
è¿äºç¨æ·çæä½éæ±ï¼å°±å¯è½å¸¦æ¥æ°æ®åºçå®å
¨é£é©è§è²å£ä»¤æµè¯oracle 10gä¸ï¼æ 论è§è²æ¯å¦æå£ä»¤ï¼åªè¦ä½ å°è§è²grantç»æ个ç¨æ·ï¼é£ä¹ï¼é»è®¤çæ
åµä¸ï¼è¿äºè§è²ä¸çæéï¼ç¨æ·é½æ¥æã
ããoracle 11gä¸ï¼è§è²çå£ä»¤ç¥æä¿®æ£ï¼å½æ个è§è²æ¯æ¥æå£ä»¤çè¯ï¼å½ä½ å°å¸¦æå£ä»¤çè§è² grant ç»æ个ç¨æ·çè¯ï¼é£ä¹é»è®¤çæ
åµä¸ï¼è¿ä¸ªå¸¦å£ä»¤çè§è²ä¸çæææéï¼ç¨æ·æ¯æ æ³æ¥æçï¼åªæå½ set é£ä¸ªæ¥æå£ä»¤çè§è²åï¼é£ä¹ ï¼å¸¦å£ä»¤çè§è²ä¸çæéæå¨å½åä¼è¯ä¸æå¯ä»¥ä½¿ç¨ï¼ä¸è¿ï¼å
¶ä»çè§è²é½ææ¶å¤±æï¼ä¿®æ¹åªå¨å½åä¼è¯ææã
ãã\
ãã<strong>è¯æ³ï¼ </strong><strong> ------åªå¨å½åä¼è¯ææ</strong>
ããSET ROLE
ãã{ role [ <strong>IDENTIFIED BY</strong> password ]
ãã[, role [ <strong>IDENTIFIED BY</strong> password ] ]...
ãã| <strong>ALL</strong> [ <strong>EXCEPT</strong> role [, role ]... ]
ãã| <strong>NONE</strong>
ãã} ;
ããoracle 10g ä¸æµè¯å¸¦å£ä»¤çè§è²
ããSYS@ORCL>select * from v$version;
ããBANNER
ãã----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 Production
ããTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production1. å建两个è§è² role_01 没æå¯ç role_02 æå¯ç SYS@ORCL>create role role_01;Role created.
ããSYS@ORCL>create role role_02 identified by oracle;Role created.
ãã2. èµäºè§è² role_01 è¿æ¥ã建表æé
ããSYS@ORCL>grant connect,create table to role_01;Grant succeeded.
ãã3. èµäºè§è² role_02 è¿æ¥ãå建è§å¾æé
ããSYS@ORCL>grant connect,create view to role_02;Grant succeeded.
ãã4. å建æµè¯ç¨æ· tyger
ããSYS@ORCL>create user tyger identified by tyger quota unlimited on users;User created.
ãã5. å°ä¸¤ä¸ªè§è²èµäºtyger
ããSYS@ORCL>grant role_01,role_02 to tyger;
ããGrant succeeded.
ãã6. è¿æ¥å°ç¨æ·æµè¯
ããSYS@ORCL>conn tyger/tyger
ããConnected.
ããTYGER@ORCL>create table t(x int);
ããTable created.
ããTYGER@ORCL>insert into t values(1);
ãã1 row created.
ããTYGER@ORCL>commit;
ããCommit complete.
ããTYGER@ORCL>select * from t;
ããX
ãã----------
ãã1
ããTYGER@ORCL>create view view_t as select * from t;View created.
ããTYGER@ORCL>select * from tab;
ããTNAME TABTYPE CLUSTERID
ãã------------------------------ ------- ----------VIEW_T VIEWT TABLE
ãã7. æ¥çå½åç¨æ·çè§è²ï¼ä¸¤ä¸ªè§è²ç DEFAULT_ROLE é½ä¸º YES 说æï¼è¿ä¸¤ä¸ªè§è²é½çæTYGER@ORCL>desc user_role_privs;Name Null? Type
ãã----------------------------------------- -------- ----------------------------USERNAME VARCHAR2(30)GRANTED_ROLE VARCHAR2(30)
ããADMIN_OPTION VARCHAR2(3)
ããDEFAULT_ROLE VARCHAR2(3)
ããOS_GRANTED VARCHAR2(3)
ããTYGER@ORCL>col username for a10
ããTYGER@ORCL>col granted_role for a20
ããTYGER@ORCL>col default_role for a20
ããTYGER@ORCL>select username,granted_role,default_role from user_role_privs;USERNAME GRANTED_ROLE DEFAULT_ROLE---------- -------------------- --------------------TYGER ROLE_01 YESTYGER ROLE_02 YES
ãã8. æ¥çå½åä¼è¯å
·æçæé
ããTYGER@ORCL>select * from session_privs;
ããPRIVILEGE
ãã----------------------------------------
ããCREATE SESSION
ããCREATE TABLE
ããCREATE VIEW
ãã9. set çåºç¨ ----set å¨å½åä¼è¯ä¸è®¾ç½®è§è²ç¶æTYGER@ORCL>set role ROLE_01;Role set.
ãã10. æ¥çå½åç¨æ·çè§è²ï¼æ²¡åå
ããTYGER@ORCL>select username,granted_role,default_role from user_role_privs;USERNAME GRANTED_ROLE DEFAULT_ROLE---------- -------------------- --------------------TYGER ROLE_01 YESTYGER ROLE_02 YES
ãã11. æ¥çå½åä¼è¯æéï¼å·²ç»æ²¡æ create view æéï¼ åå ï¼set role role_01 åªærole_01 å¨å½åä¼è¯çæTYGER@ORCL>select * from session_privs;PRIVILEGE
ãã----------------------------------------
ããCREATE SESSION
ããCREATE TABLE
ããTYGER@ORCL>create view view_2 as select * from t;create view view_2 as select * from t*
ããERROR at line 1:
ããORA-01031: insufficient privileges
ãã12. åæ ·ä½¿ role_02 çæï¼åæ¶ role_01 失æï¼ä¸è¿è®¾ç½®çæ¶åéè¦æ们æä¾å¯ç ï¼å 为æ们å建è§è²æ¶ä½¿ç¨äºå¯ç TYGER@ORCL>set role role_02;set role role_02
ãã*
ããERROR at line 1:
ããORA-01979: missing or invalid password for role 'ROLE_02'
ããTYGER@ORCL>set role role_02 identified by oracle;Role set.
ãã13. æ¥çå½åç¨æ·ææ¥æçæéï¼è¿æ¯æ²¡åå
ããTYGER@ORCL>select username,granted_role,default_role from user_role_privs;USERNAME GRANTED_ROLE DEFAULT_ROLE---------- -------------------- --------------------TYGER ROLE_01 YESTYGER ROLE_02 YES
ãã14. æ¥çå½åä¼è¯çæéï¼å·²ç»æ²¡æ create table æéTYGER@ORCL>select * from session_privs;PRIVILEGE
ãã----------------------------------------
ããCREATE SESSION
ããCREATE VIEW
ããTYGER@ORCL>create table t1(x int);
ããcreate table t1(x int)
ãã*
ããERROR at line 1:
ããORA-01031: insufficient privileges
ãã15. éæ°ç»å½ä¼è¯ï¼è¿æ¥ç¨æ· æææéé½æ¢å¤åæ ·TYGER@ORCL>conn tyger/tyger;Connected.
ããTYGER@ORCL>select * from session_privs;
ããPRIVILEGE
ãã----------------------------------------
ããCREATE SESSION
ããCREATE TABLE
ããCREATE VIEW
ããæ»ç»ï¼å¨oracle 10g æ 论è§è²æ¯å¦æå£ä»¤ï¼å°è§è²èµäºç»ç¨æ·åï¼ç¨æ·å
·æè§è²çå
¨é¨æéã
ããoracle 11g ä¸æµè¯ æ¥æå£ä»¤çè§è²
ãã[oracle@ora11gr2 ~]$ sqlplus / as sysdba
ããSQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 19 15:28:13 2014Copyright (c) 1982, 2009, Oracle. All rights reserved.
ããConnected to:
ããOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@ORA11G>select * from v$version;BANNER
ãã--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 Production
ããTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production1. å建è§è² tyger_ro1 æ å¯ç tyger_ro2 æå¯ç SYS@ORA11G>create role tyger_ro1;Role created.
ããSYS@ORA11G>create role tyger_ro2 identified by oracle;Role created.
ããSYS@ORA11G>grant connect,create table to tyger_ro1;Grant succeeded.
ããSYS@ORA11G>grant connect,create view to tyger_ro2;Grant succeeded.
ããSYS@ORA11G>create user tyger identified by tyger quota unlimited on users;User created.
ããSYS@ORA11G>grant tyger_ro1,tyger_ro2 to tyger;Grant succeeded.
ããSYS@ORA11G>conn tyger/tyger
ããConnected.
ããTYGER@ORA11G>create table t(x int);
ããTable created.
ããTYGER@ORA11G>insert into t values(1);
ãã1 row created.
ããTYGER@ORA11G>commit;
ããCommit complete.
ãã2. æ¤æ¶å°±åºç°é®é¢äºï¼role_02 æææ create view èä¸èµäºç»äº tyger 为ä»ä¹è¿é就没æå¢ï¼
ããTYGER@ORA11G>create view view_t as select * from t;create view view_t as select * from t*
ããERROR at line 1:
ããORA-01031: insufficient privileges
ãã3. æ¥ç tyger_ro2 çdefault_role 为NO é¾é role_02 è§è²å¤±æï¼ï¼ï¼
ããTYGER@ORA11G>col username for a10
ããTYGER@ORA11G>col granted_role for a20
ããTYGER@ORA11G>col default_role for a20
ããTYGER@ORA11G>select username,granted_role,default_role from user_role_privs;USERNAME GRANTED_ROLE DEFAULT_ROLE---------- -------------------- --------------------TYGER TYGER_RO1 YESTYGER TYGER_RO2 NO
ãã4. åæ¥çå½åä¼è¯çæéï¼æç¶æ²¡æ create view æéTYGER@ORA11G>select * from session_privs;PRIVILEGE----------------------------------------
ããCREATE SESSION
ããCREATE TABLE
ãã5. 设置 tyger_ro2 æéçæ
ããTYGER@ORA11G>set role tyger_ro2 identified by oracle;Role set.
ãã6. å½åç¨æ·å
·æçæéä¸å
ããTYGER@ORA11G>select username,granted_role,default_role from user_role_privs;USERNAME GRANTED_ROLE DEFAULT_ROLE---------- -------------------- --------------------TYGER TYGER_RO1 YESTYGER TYGER_RO2 NO
ãã7.å½åä¼è¯ç¨äº create view æé å´æ²¡æäºcreate table æéTYGER@ORA11G>select * from session_privs;PRIVILEGE----------------------------------------
ããCREATE SESSION
ããCREATE VIEW
ããTYGER@ORA11G>create view view_t as select * from t;View created.
ããTYGER@ORA11G>select * from tab;
ããTNAME TABTYPE CLUSTERID
ãã------------------------------ ------- ----------T TABLEVIEW_T VIEW
ããTYGER@ORA11G>create table t1(x int);
ããcreate table t1(x int)
ãã*
ããERROR at line 1:
ããORA-01031: insufficient privileges
ãã8. éæ°ç»å½ä¼è¯ï¼æéæ¢å¤åæ ·
ããTYGER@ORA11G>conn tyger/tyger
ããConnected.
ããTYGER@ORA11G>create table t1(x int);
ããTable created.
ããTYGER@ORA11G>select username,granted_role,default_role from user_role_privs;USERNAME GRANTED_ROLE DEFAULT_ROLE---------- -------------------- --------------------TYGER TYGER_RO1 YESTYGER TYGER_RO2 NO
ããæ»ç»ï¼
ããå¨oracle 11g ä¸ï¼å¸¦æå£ä»¤çè§è²èµäºç¨æ·ï¼é»è®¤æ
åµä¸æ¯å¤±æçï¼å½ set role çæåï¼å
¶ä»è§è²æå
·æçæé失æï¼ åªå¨å½åä¼è¯ææ
温馨提示:答案为网友推荐,仅供参考