Special System Privileges for Administrators in Oracle

Back to: Oracle DBA Tutorials

Special System Privileges for Administrators in Oracle

In this article, I am going to discuss Special System Privileges for Administrators in Oracle with Examples. Please read our previous article where we discussed Oracle Supplied Administrator Accounts with Examples.

Special System Privileges for Administrators in Oracle

In the previous article, we learned about the special system administrator users like sys, system, sysdba, sysdg, sysbackup. We will learn about seven system privileges.

SYSDBA System Privileges in Oracle:

The Sys used has this privilege. When the user has SYSDBA privileges the user will perform the below tasks.

• Perform start-up and shutdown operations
• ALTER DATABASE: open, mount, backup, or change characters set.
• Create database
• Drop Database
• Create SP file
• Alter Database archive log
• Alter database Recover
• Included the RESTRICTED SESSION privilege

This administrative privilege allows most operations, including the ability to view user data. It is the most powerful privilege. When we log in as SYSDBA we can log in to any other pluggable database.

SYSOPER System Privileges in Oracle:

This user can perform the following operations.

• Perform Shutdown and Start-up Operations
• Create the SP file.
• ALTER DATABASE: open, mount, or backup
• Alter database Archive log
• Alter database Recover (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME| CHANGE|CANCEL| CONTROLFILE requires connecting as SYSDBA.
• Included the RESTRICTED SESSION privilege.

This privilege allows a user to perform basic operations tasks, but without the ability to view user data.

SYSBACKUP: This privilege allows a user to perform backup and recovery operations either from Oracle recovery manager (RMAN) or SQL*Plus. We will learn about this in later articles

SYSDG: This privilege allows a user to perform Data Guard Operations. You can use this privilege with Data Guard Broker or the DGMGRL Command line interface.

SYSKM: This privilege allows a user to perform Transparent Data Encryption Keystore operations.

SYSRAC: This privilege allows the Oracle agent of Oracle cluster ware to perform Oracle Real Application Clusters (Oracle RAC) operations.

SYSASM: This is a system privilege that enables the separation of the SYSDBA database administration privilege from the Oracle ASM storage administration privilege.

This information is to learn about the privileges provided by the oracle database to oracle created users.

We have learned about seven special privileges. Oracle also introduced special supplied roles. We have the roles below. There are four oracle supplied roles

• DBA
• RESOURCE
• SCHEDULER_ADMIN
• SELECT_CATALOG_ROLE

DBA: This includes most system privileges and several other roles. Do not grant this role to non-administrators. Users with this role can connect to the CDB or PDB only when it is open. This indicates that the DBA role has fewer privileges compared to SYSDBA. The best practice for the DBA role is by creating a separate admin user in the pluggable database and grant the DBA role to that user.

Note: The SYS and SYSTEM users already have a DBA role by default.

RESOURCE: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE.

SCHEDULER_ADMIN: This is related to oracle schedules. We have privileges like CREATE ANY JOB, CREATE EXTERNAL JOB, CREATE JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM, MANAGE SCHEDULER.

SELECT_CATALOG_ROLE: This role provides select privilege on data dictionary objects. This role is granted to Oracle developers who are working on the oracle tuning and execution plan generation. The users who work on tuning need access to all the tables in order to solve more problems in the query execution and then solve the long-running sessions.

The most important role is DBA. There are more roles present as we cannot provide all of them at a time. We can go ahead and visit the original Oracle website to get additional information about the oracle roles.

In the next article, I am going to discuss Special System Privileges for Administrators in Oracle with Examples. Here, in this article, I try to explain Special System Privileges for Administrators in Oracle with Examples. I hope you enjoy this Special System Privileges for Administrators in Oracle article.