Oracle Database - SYSDBA privilege

> Database > Oracle Database

1 - About

A user can connect with different levels of privileges, namely:

  • SYSDBA (root or administrator)
  • and SYSOPER. SYSOPER allows a user to perform basic operational tasks, but without the ability to look at user data.

By default, all members with the SYSDBA privilege have all privileges granted by the SYSOPER privilege.

SYSDBA is used internally and has specialized functions. Its behavior is not the same as for general users.

Advertising

3 - SYSDBA privileges

  • 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. (SYSOPER is limited to complete recovery only)
  • Includes the RESTRICTED SESSION privilege

Effectively, this system privilege allows a user to connect as user SYS.

4 - Connect as Sysdba references the SYS schema

When you connect using

CONNECT sys/passwd AS sysdba

your connecting as the SYS user and requesting SYSDBA privs.

Example :

CONNECT oe/oe
CREATE TABLE admin_test(name VARCHAR2(20));
 
 
Later, USER oe issues these statements:
 
CONNECT oe/oe AS SYSDBA
SELECT * FROM admin_test;
 
 
USER oe now receives the following error:
 
ORA-00942: TABLE OR VIEW does NOT exist 
Advertising

5 - Documentation / Reference