【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 bytes

Fixed Size bytes

Variable Size bytes

Database Buffers bytes

Redo Buffers 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

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 bytes

Fixed Size bytes

Variable Size bytes

Database Buffers bytes

Redo Buffers 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

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

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//viewspace-/，如需转载，请注明出处，否则将追究法律责任。

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

今天的文章【oracle DB安全性】保护数据字典参数O7_DICTIONARY_ACCESSIBILITY分享到此就结束了，感谢您的阅读。

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

相关推荐