【oracle DB安全性】保護資料字典引數O7_DICTIONARY_ACCESSIBILITY

不一樣的天空w發表於2016-10-20

保護資料字典
O7_DICTIONARY_ACCESSIBILITY引數控制對資料字典的訪問, 9i 及以後版本預設為 false,8i
及以前版本預設為 trueO7_DICTIONARY_ACCESSIBILITY 引數設定為 true,如果使用者被授予瞭如 select any tableany table 許可權,使用者即使不是 dba sysdba 使用者也可以訪問資料字典。 如果設定為 true 就可能會帶來安全上的一些問題。
這也就是為什麼 sys 不能以 normal 方式登入資料庫, 因為 O7_DICTIONARY_ACCESSIBILITY
9i 及以後預設設定為 false

1)建立使用者 xxf,授予 create session select any table 許可權

SYS@ORA11GR2>create user xxf identified by xxf;

 

User created.

 

SYS@ORA11GR2>grant create session,select any table to xxf;

 

Grant succeeded.

 

2) 檢視 o7_dictionary_accessibility 值(預設為 false

SYS@ORA11GR2>show parameter o7

 

NAME                                 TYPE        VALUE

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

O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

 

3) 連線到 xxf 使用者,既然有 select any table 許可權,那麼我們嘗試查詢字典表,顯然是無法查詢

SYS@ORA11GR2>conn xxf/xxf

Connected.

XXF@ORA11GR2>

XXF@ORA11GR2>select count(*) from dba_objects;

select count(*) from dba_objects

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

4) 回到 sys 使用者修改引數 o7_dictionary_accessibility 值為 true,由於是靜態引數,所以重啟後生效

XXF@ORA11GR2>conn / as sysdba

Connected.

 

SYS@ORA11GR2>alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;

 

System altered.

 

SYS@ORA11GR2>startup force;

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

Database mounted.

Database opened.

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter o7

 

NAME                                 TYPE        VALUE

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

O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE

 

5) 再次來到 xxf 使用者,查詢字典表,此時 select any table 許可權生效了已可以查詢字典表

SYS@ORA11GR2>conn xxf/xxf

Connected.

XXF@ORA11GR2>

XXF@ORA11GR2>select count(*) from dba_objects;

 

  COUNT(*)

----------

     86991

 

XXF@ORA11GR2>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@wang ~]$

 

6) o7_dictionary_accessibility 值為 true 時,sys 使用者可以以普通的使用者登入sqlplus

[oracle@wang ~]$ sqlplus sys/oracle

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 17:39:30 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ORA11GR2>

: o7_dictionary_accessibility 值為 false 時,sys 賬戶以普通使用者登入會報錯。

——檢視引數O7_DICTIONARY_ACCESSIBILITY值:

SYS@ORA11GR2>show parameter o7

 

NAME                                 TYPE        VALUE

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

O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE

SYS@ORA11GR2>alter system set O7_DICTIONARY_ACCESSIBILITY=false scope=spfile;

 

System altered.

 

SYS@ORA11GR2>startup force;

ORA-01031: insufficient privileges

(因為此時sys使用者是以普通使用者登入的沒有停啟庫許可權)

 

——重新以sysdba身份登入

SYS@ORA11GR2>conn  /  as sysdbasys使用者,以sysdba身份登入)

Connected.

SYS@ORA11GR2>startup force;  (這次以sysdba身份啟庫成功)

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

Database mounted.

Database opened.

SYS@ORA11GR2>show parameter o7

 

NAME                                 TYPE        VALUE

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

O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

SYS@ORA11GR2>

 
——退出後以sys使用者身份登入:

[oracle@wang ~]$ sqlplus sys/oracle

(報錯,因為O7_DICTIONARY_ACCESSIBILITY值已經為falsesys使用者只能以sysdba身份登入)

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 17:45:28 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

ERROR:

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

 

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

 

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

 

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

——sys使用者重新以sysdba身份登入:(成功)

[oracle@wang ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 17:46:14 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

實驗結束!!!!!!!!!!!!!1

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

相關文章