AS SYSDBA 和 AS SYSOPER的理解
SYS用户连接ORACLE的时候,后面必须加上AS SYSDBA 或 AS SYSOPER,这是ORACLE的规定,没有为什么,个人理解为可能是为了区分普通用户和SYS用户连接ORACLE的区别。SYSDBA 和 SYSOPER是ORACLE里面的系统权限,只是这两个系统权限比较特殊,在连接ORACLE的时候必须得以AS SYSDBA或AS SYSOPER这样体现出来,在字典里可以查到这两个权限:
SQL> select * from system_privilege_map where name like '%SYSDBA%' OR name like '%SYSOPER%';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-83 SYSDBA 0
-84 SYSOPER 0
SQL>
其实SYS用户也可以用常规方式去连接ORACLE,不过这样得修改一个参数,如下:
SQL> show parameter o7
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
SQL>
此参数如果改为TRUE,则SYS用户在登录时可以省略后面的AS SYSDBA 或 AS SYSOPER,如下:
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2180544 bytes
Variable Size 599788096 bytes
Database Buffers 251658240 bytes
Redo Buffers 2355200 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
此时连接就可以不加AS SYSDBA 或 AS SYSOPER了。虽然这样比较方面,但存在一定的安全隐患,所以不建议这样做。
[oracle@redhat55 ~]$ sqlplus sys/oracle@prod
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 1月 31 11:53:18 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
以sysoper权限连接数据库时显示的schema是public,而以sysdba连接时显示的schema是我们众所周知的sys,这也是sysdba和sysoper的区别之一。
[oracle@redhat55 ~]$ sqlplus / as sysoper
SQL> show user;
USER is "PUBLIC"
SQL> exit
[oracle@redhat55 ~]$ sqlplus / as sysdba
SQL> show user;
USER is "SYS"