Types of privileges of ‘SYSOPER’ in Oracle DBA

Oracle Database Privileges

A user can connect with different levels of privileges:

• namely for the database:

SYSDBA (the root or administrator of the database) and

SYSOPER - SYSOPER allows a user to perform basic operational tasks but without the ability to look at user data.

• for Automatic Storage Management:

SYSASM (the root or administrator of Automatic Storage Management)

• Operating system accounts (user) must be members of the OSDBA or OSOPER groups to connect to the database with administrator privileges.
• When you connect with a privilege issuing a 'CONNECT / AS SYSDBA' (OR SYSOPER), Oracle checks if your account is a member of the corresponding OS group (for Windows: 'ORA_sid_DBA' or 'ORA_DBA' group).

Administrative Privileges

Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER.  You must have one of these privileges granted to you, depending upon the level of authorization you require.

SYSDBA and SYSOPER

The following operations are authorized by the SYSDBA and SYSOPER system privileges:

The privileges of SYSOPER are:

• 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.

Inclined to build a profession as Online Oracle DBA Training? Then here is the blog post on, explore Online Oracle DBA Training

The privileges of SYSDBA are:

• 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.

Nonsecure Remote Connections

To connect to Oracle Database as a privileged user over a non-secure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database usernames that have been granted the SYSDBA or SYSOPERsystem privilege. This form of authentication is discussed in "Using Password File Authentication".

Local Connections and Secure Remote Connections

You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:

• If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file.
• If the server is not using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA  A similar group, OSOPER, is used to grant SYSOPER privileges to users.

SYSDBA and SYSOPER System Privileges

SYSDBA and SYSOPER are administrative privileges required to perform high-level administrative operations such as creating, starting up, shutting down, backing up, or recovering the database. The SYSDBA system privilege is for fully empowered database administrators and the SYSOPER system privilege allows a user to perform basic operational tasks but without the ability to look at user data.

The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is therefore completely outside of the database itself. This control enables an administrator who is granted one of these privileges to connect to the database instance to start the database.

You can also think of the SYSDBA and SYSOPER privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the SYSDBA privilege, then you can connect to the database using AS SYSDBA.

The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as an SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. EM Express allows you to log in as user SYS and connect as SYSDBA or SYSOPER.

When you connect with the SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.

For in-depth knowledge on Oracle DBA click on 

• Oracle Service Bus
• Working with Flashback Table in Oracle DBA
• Oracle DBA Interview Questions