SYS vs SYSTEM , SYSDBA vs SYSOPER

缺省的，数据库中已经有了两位管理人员：SYS和SYSTEM，这两个用户在创建数据库时就被缺省创建了，并且都被赋予了DBA的角色，具有数据库中最高的权限。由于SYS用户具有管理数据库的所有权限，所以仅允许以SYSDBA或者SYSOPER身份登陆数据库：

sysdba的系统权限包括了完全的数据库管理权限，而sysoper系统权限允许用户执行基本的数据库管理任务。sysdba的缺省Schema是SYS，而sysoper的缺省Schema是PUBLIC.

AskTom中关于这几个容易混淆概念的解答，链接：regarding "SYS, SYSDBA, SYSOPER, SYSTEM"

sys and system are "real schemas", there is a user SYS and a user SYSTEM.

In general, unless the documentation tells you, you will NEVER LOG IN as sys or system, they are our internal data dictionary accounts and not for your use. You will be best served by forgetting they exist.

sysdba and sysoper are ROLES - they are not users, not schemas. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.

In real life, you hardly EVER need sysdba - typically only during an upgrade or patch.

sysoper is another role, if you connect as sysoper, you'll be in a schema "public" and will only be able to do things granted to public AND start/stop the database. sysoper is something you should use to startup and shutdown. You'll use sysoper much more often than sysdba.

do not grant sysdba to anyone unless and until you have absolutely verified they have the NEED for sysdba - the same with sysoper. 

System Privilege

Operations Authorized

SYSDBA

• Perform STARTUP and SHUTDOWN operations

• ALTER DATABASE: open, mount, back up, or change character set

• CREATE DATABASE

• DROP DATABASE

• CREATE SPFILE

• ALTER DATABASE ARCHIVELOG

• ALTER DATABASE RECOVER

• Includes the RESTRICTED SESSION privilege
  
  
   Effectively, this system privilege allows a user to connect as user SYS. 

SYSOPER

• Perform STARTUP and SHUTDOWN operations

• CREATE SPFILE

• ALTER DATABASE OPEN/MOUNT/BACKUP

• ALTER DATABASE ARCHIVELOG

• ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)

• Includes the RESTRICTED SESSION privilege

This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.

只要能以sysdba的角色连上库，最终实际用户都是sys。

C:\Users\TianPan>sqlplus system/welcome as sysdba
...
SQL> show user
USER is "SYS"
SQL>

C:\Users\TianPan>sqlplus sys/welcome as sysdba
...
SQL> show user
USER is "SYS"
SQL>

C:\Users\TianPan>sqlplus system/welcome

...
SQL> show user
USER is "SYSTEM"
SQL>

只要能以sysoper的角色连上库，最终实际用户都是public。

C:\Users\TianPan>sqlplus sys/welcome as sysoper
...
SQL> show user
USER is "PUBLIC"
SQL>

注意sys用户只能以sysdba的身份登陆。

C:\Users\TianPan>sqlplus sys/welcome

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 28 15:20:55 2014

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

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


Enter user-name: