Password Files   «Prev 

Granting SYSDBA privileges to a user

DBA Roles

Scenario: I have the DBA role and am able to connect as SYSDBA, but I am not able to connect as SYSOPER:
SQL> connect pubs/pubs
 Connected.
SQL> drop user tom; 

User dropped. 

SQL> create user tom identified by ton1234; 
User created. 

SQL> grant dba to tom
SQL> connect tom/tom1234 as sysdba;
 Connected.
 
SQL> connect tom/tom1234 as sysoper;
ERROR:
ORA-01031: insufficient privileges 

Warning: You are no longer connected to ORACLE.
 SQL> 
SQL> connect pubs/pubs;
Connected.
SQL> 
SQL> grant sysoper to tom;
grant sysoper to tom
 *
ERROR at line 1:
ORA-01031: insufficient privileges 

SQL> 
SQL> connect tom/tom1234 as sysoper;
ERROR:
ORA-01031: insufficient privileges

Question: What is the difference between the dba roles
  1. sysdba system privilege and
  2. sysoper system privilege?

Answer: The DBA role does not include the SYSDBA or SYSOPER system privileges.
The SYSDBA and SYSOPER are special administrative privileges that allow an administrator to perform basic database administration tasks, such as
  1. creating the database,
  2. instance startup and shutdown,
  3. drop a database,
  4. open and mount a database, or
  5. place database in archivelog mode.

The SYSOPER privilege allows a user to perform the following, routine functions:
  1. Startup the database
  2. Shutdown the database
  3. Open and close the database
  4. Recover the database (not time-based)
  5. Connect when the database is in restricted session mode
  6. Stop and start archive logging
  7. Manually archive a log file
  8. View the current archive log status

The SYSDBA privilege allows a user to do anything, including the following:
  1. Anything that SYSOPER can do,
  2. time based recovery,
  3. create a new database

SYSDBA also covers all system privileges on the user.