在很多時(shí)候汤锨，希望給用戶最小的權(quán)限双抽，讓其訪問系統(tǒng)數(shù)據(jù)字典，檢查數(shù)據(jù)庫的運(yùn)行狀態(tài)闲礼。這種事情在乙方的工作中非常常見牍汹。下面介紹三種方法處理這個(gè)問題
0.select any table權(quán)限
這里說明select any table不能直接訪問數(shù)據(jù)字典
SQL> conn / as sysdba
Connected.
SQL> create user xff_any identified by xifenfei;
User created.
SQL> grant connect,select any table to xff_any;
Grant succeeded.
SQL> conn xff_any/xifenfei
Connected.
SQL> select count() from dba_users;
select count() from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from chf.t_xff;
COUNT(*)
2770
select any table默認(rèn)情況下，只能訪問業(yè)務(wù)的表柬泽，但是不能訪問數(shù)據(jù)字典的數(shù)據(jù)慎菲。所以單純的這個(gè)屬性不能滿足需求。
1.SELECT ANY DICTIONARY權(quán)限
SQL> conn / as sysdba
Connected.
SQL> create user xff_DICTIONARY identified by xifenfei;
User created.
SQL> grant connect to xff_DICTIONARY;
Grant succeeded.
SQL> conn xff_DICTIONARY/xifenfei
Connected.
SQL> select count() from dba_users;
select count() from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> grant SELECT ANY DICTIONARY to xff_DICTIONARY;
Grant succeeded.
SQL> conn xff_DICTIONARY/xifenfei
Connected.
SQL> select count(*) from dba_users;
COUNT(*)
32
SQL> select count() from chf.t_xff;
select count() from chf.t_xff
*
ERROR at line 1:
ORA-00942: table or view does not exist
這里可以看出SELECT ANY DICTIONARY權(quán)限只能訪問數(shù)據(jù)字典聂抢，不能訪問業(yè)務(wù)的表钧嘶，訪問業(yè)務(wù)的表需要另外授權(quán)
2.SELECT_CATALOG_ROLE角色
SQL> conn / as sysdba
Connected.
SQL> create user xff_CATALOG identified by xifenfei;
User created.
SQL> grant connect,SELECT_CATALOG_ROLE to xff_CATALOG;
Grant succeeded.
SQL> conn xff_CATALOG/xifenfei
Connected.
SQL> select count(*) from dba_users;
COUNT(*)
33
SQL> select count() from chf.t_xff;
select count() from chf.t_xff
*
ERROR at line 1:
ORA-00942: table or view does not exist
這里可以看出SELECT_CATALOG_ROLE權(quán)限只能訪問數(shù)據(jù)字典，不能訪問業(yè)務(wù)的表琳疏，訪問業(yè)務(wù)的表需要另外授權(quán)
3.O7_DICTIONARY_ACCESSIBILITY參數(shù)
SQL> conn / as sysdba
Connected.
SQL> create user xff_O7 identified by xifenfei;
User created.
SQL> grant connect to xff_o7;
Grant succeeded.
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true;
alter system set O7_DICTIONARY_ACCESSIBILITY=true
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1345016 bytes
Variable Size 306186760 bytes
Database Buffers 54525952 bytes
Redo Buffers 6205440 bytes
Database mounted.
Database opened.
SQL> conn xff_o7/xifenfei
Connected.
SQL> select count(*) from dba_users;
COUNT(*)
34
這里通過O7_DICTIONARY_ACCESSIBILITY和SELECT ANY TABLE權(quán)限有决，實(shí)現(xiàn)訪問業(yè)務(wù)數(shù)據(jù)和數(shù)據(jù)字典
