Differences between sysdba, sysoper, and dba

Differences between sysdba, sysoper, and dba

For sysdba, sysoper, and dba, many terms may be used at work. If the environment is a server, sysdba may be frequently used. DBAs are required permissions for database maintenance.

The official Oracle documents show the differences between sysdba and sysoper. On this basis, I have added some additional things.

The yellow part is the difference between sysdba and sysoper. These two special system permissions differ significantly from dba permissions.

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 Specified tively, 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

#1 sys and system users are all built-in users during database creation. Sys is bound with sysdba system permissions, and system is bound with dba roles.
The default password of sys is CHANGE_ON_INSTALL, while that of system is MANAGER.

#2 If you directly use sys to log on without using sysdba, the following error will occur.
[Ora11g @ rac1 ~] $ Sqlplus sys/oracle
SQL * Plus: Release 11.2.0.3.0 Production on Sat Nov 22 04:39:21 2014
Copyright (c) 1982,201 1, Oracle. All rights reserved.
ERROR:
ORA-28009: connection as SYS shocould be as SYSDBA or SYSOPER

#3. sysdba's user is SYS, and sysoper's user is PUBLIC.
SQL> conn/as sysoper
Connected.
SQL> show user
USER is "PUBLIC"
SQL> conn/as sysdba
Connected.
SQL> show user
USER is "SYS"

#4 if you log on to the server, use a single user. Because operating system-level verification is set, you can use sysdba.
SQL> conn test/test as sysdba
Connected.

If the tns connection is used. The test permission is verified. Sysdba system permissions are not available.
SQL> conn test/tets @ test01 as sysdba
ERROR:
ORA-01031: insufficient privileges

#5 for sys, you have to mention the password file. If no password file exists. The following error is reported.

[Ora11g @ rac1 dbs] $ sqlplus sys/oracle @ test01 as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Sat Nov 22 05:40:03 2014

Copyright (c) 1982,201 1, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges
After the password file is created, no problem occurs.
 

[Ora11g @ rac1 dbs] $ orapwd password = oracle file = orapwTEST01 entries = 2
[Ora11g @ rac1 dbs] $ sqlplus sys/oracle @ test01 as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Sat Nov 22 05:40:37 2014

Copyright (c) 1982,201 1, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

#6 The following example shows the differences between sysdba and dba.
User n1 is a dba user. Let's take a look at the differences when using sysdba and dba permissions.

[Ora11g @ rac1 dbs] $ sqlplus n1/n1 as sysdba

SQL * Plus: Release 11.2.0.3.0 Production on Sat Nov 22 05:56:40 2014

Copyright (c) 1982,201 1, Oracle. All rights reserved.
Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count (*) from cat;

COUNT (*)
----------
4809

SQL> conn n1/n1
Connected.
SQL> select count (*) from cat;

COUNT (*)
----------
406

#7 sysdba has far higher permissions than dba. Common dba roles include detailed permissions for creating tables, views, indexes, etc. However, sysdba can perform any database-level operations.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm