Oracle Administrative Privileges for connection to the server

This topic contains information on what Administrative Privileges you can use when connecting to the Oracle server.

Some administrative actions may require connecting as SYSDBA or SYSOPER. SYS or SYSTEM users can connect only as SYSDBA or SYSOPER.

Note

To connect as SYSDBA or SYSOPER, you must have SYSDBA or SYSOPER system privilege. These privileges are not included in the DBA role.

To connect as SYSDBA or SYSOPER, you should select SYSDBA or SYSOPER in the Connect as box of the general tab of the Connection Properties dialog box when adding or modifying a connection.

Connecting as SYSDBA or SYSOPER enables you to perform certain database operations for which privileges cannot be granted in any other fashion.

SYSDBA

Connecting as SYSDBA allows you to perform the following operations:

  • STARTUP and SHUTDOWN
  • ALTER DATABASE: open, mount, back up, or change character set
  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SPFILE
  • ALTER DATABASE ARCHIVELOG
  • ALTER DATABASE RECOVER

When you connect as SYSDBA you also have RESTRICTED SESSION privilege.

SYSOPER

Connecting as SYSOPER allows you to perform the following operations:

  • STARTUP and SHUTDOWN
  • CREATE SPFILE
  • ALTER DATABASE OPEN/MOUNT/BACKUP
  • ALTER DATABASE ARCHIVELOG
  • ALTER DATABASE RECOVER (Complete recovery only).

When you connect as SYSOPER you also have RESTRICTED SESSION privilege. Connecting as SYSOPER allows a user to perform basic operational tasks, but without the ability to look at user data.

SYSBACKUP

This privilege allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus.

The privilege enables you to perform the following operations:

  • STARTUP
  • SHUTDOWN
  • ALTER DATABASE
  • ALTER SYSTEM
  • ALTER SESSION
  • ALTER TABLESPACE
  • CREATE CONTROLFILE
  • CREATE ANY DIRECTORY
  • CREATE ANY TABLE
  • CREATE ANY CLUSTER
  • CREATE PFILE
  • CREATE RESTORE POINT (including GUARANTEED restore points)
  • CREATE SESSION
  • CREATE SPFILE
  • DROP DATABASE
  • DROP TABLESPACE
  • DROP RESTORE POINT (including GUARANTEED restore points)
  • FLASHBACK DATABASE
  • RESUMABLE
  • UNLIMITED TABLESPACE
  • SELECT ANY DICTIONARY
  • SELECT ANY TRANSACTION
  • SELECT o X$ tables (that is, the fixed tables) o V$ and GV$ views (that is, the dynamic performance views) o APPQOSSYS.WLM_CLASSIFIER_PLAN o SYSTEM.LOGSTDBY$PARAMETERS
  • DELETE/INSERT o SYS.APPLY$_SOURCE_SCHEMA o SYSTEM.LOGSTDBY$PARAMETERS
  • EXECUTE o SYS.DBMS_BACKUP_RESTORE o SYS.DBMS_RCVMAN o SYS.DBMS_DATAPUMP o SYS.DBMS_IR o SYS.DBMS_PIPE o SYS.SYS_ERROR o SYS.DBMS_TTS o SYS.DBMS_TDB o SYS.DBMS_PLUGTS o SYS.DBMS_PLUGTSP
  • SELECT_CATALOG_ROLE

In addition, the SYSBACKUP privilege enables you to connect to the database even if the database is not open.

SYSDG

Connect with the SYSDG administrative privilege to perform Data Guard operations.

The SYSDG privilege enables the following operations:

  • STARTUP
  • SHUTDOWN
  • ALTER DATABASE
  • ALTER SESSION
  • ALTER SYSTEM
  • CREATE RESTORE POINT (including GUARANTEED restore points)
  • CREATE SESSION
  • DROP RESTORE POINT (including GUARANTEED restore points)
  • FLASHBACK DATABASE
  • SELECT ANY DICTIONARY
  • SELECT o X$ tables (that is, the fixed tables) o V$ and GV$ views (that is, the dynamic performance views) o APPQOSSYS.WLM_CLASSIFIER_PLAN
  • DELETE o APPQOSSYS.WLM_CLASSIFIER_PLAN
  • EXECUTE o SYS.DBMS_DRS

In addition, the SYSDG privilege enables you to connect to the database even if it is not open.

SYSKM

The SYSKM administrative privilege enables the SYSKM user to manage Transparent Data Encryption wallet operations.

The SYSKM administrative privilege enables the following operations:

  • ADMINISTER KEY MANAGEMENT
  • CREATE SESSION
  • SELECT (only when a database is open) o SYS.V$ENCRYPTED_TABLESPACES o SYS.V$ENCRYPTION_WALLET o SYS.V$WALLET o SYS.V$ENCRYPTION_KEYS o SYS.V$CLIENT_SECRETS o SYS.DBA_ENCRYPTION_KEY_USAGE

In addition, the SYSKM privilege enables you to connect to the database even if it is not open.