【oracle DB安全性】保护数据字典参数O7_DICTIONARY_ACCESSIBILITY

保护数据字典：
O7_DICTIONARY_ACCESSIBILITY参数控制对数据字典的访问， 9i 及以后版本默认为 false,8i
及以前版本默认为 true。O7_DICTIONARY_ACCESSIBILITY 参数设置为 true,如果用户被授予了如 select any table等 any 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 sysdba（sys用户，以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值已经为false，sys用户只能以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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2126839/