O7_DICTIONARY_ACCESSIBILITY参数控制对数据字典的访问.设置为true,如果用户被授予了如select any table等any table权限,用户即使不是dba或sysdba用户也可以访问数据字典.在9i及以上版本默认为false,8i及以前版本默认为true.如果设置为true就可能会带来安全上的一些问题.这也就为什么O7_DICTIONARY_ACCESSIBILITY 设置为false时,sys不能以normal方式登录的原因.实验如下:
1.以sysdba身份进入oracle
[oracle@oracle11g ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 19 15:27:01 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
2.查看o7默认设置
SQL> show parameter o7;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
3.退出sysdba身份
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
4.使用hr用户登录oracle
[oracle@oracle11g ~]$ sqlplus hr/wwwwww
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 19 15:27:22 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
5.查看datafile
SQL> select count(*) from v$datafile;
select count(*) from v$datafile
*
ERROR at line 1:
ORA-00942: table or view does not exist
6.退出hr用户
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
7.以sysdba身份进入oracle
[oracle@oracle11g ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 19 15:27:42 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
8.给hr用户授权所有可查询
SQL> grant select any table to hr;
Grant succeeded.
9.退出sysdba身份
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10.使用hr用户登录oracle
[oracle@oracle11g ~]$ sqlplus hr/wwwwww
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 19 15:28:26 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
11.再次查询，发现还是失败，这是为什么？？因为O7_DICTIONARY_ACCESSIBILITY 一直是false
SQL> select count(*) from v$datafile;
select count(*) from v$datafile
*
ERROR at line 1:
ORA-00942: table or view does not exist
12.退出hr用户
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
13.以sysdba身份进去oracle
[oracle@oracle11g ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 19 15:28:45 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
14.修改O7_DICTIONARY_ACCESSIBILITY参数为true
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
15.退出oracle
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
16.使用hr用户登录oracle
[oracle@oracle11g ~]$ sqlplus hr/wwwwww
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 19 15:29:01 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
17.继续查看，还是不成功？为什么？？重启Oracle试试
SQL> select count(*) from v$datafile;
select count(*) from v$datafile
*
ERROR at line 1:
ORA-00942: table or view does not exist
18.退出Oracle
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
19.以sysdba身份进入Oracle
[oracle@oracle11g ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 19 15:29:53 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
20.关闭oracle
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
21.启动Oracel
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 1023413712 bytes
Database Buffers 570425344 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
22.退出Oracle
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
23.以hr用户登录oracle
[oracle@oracle11g ~]$ sqlplus hr/wwwwww
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 19 15:30:52 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
24.查看v$datafile参数
SQL> select count(*) from v$datafile;
COUNT(*)
----------
5
阅读(916) | 评论(0) | 转发(0) |
