how to create common user with sysbackup privilege in oracle database by manish sharma

With the shift of architecture in Oracle Database 12c there has arisen a need for a USER/SCHEMA that has access to both the container database(CDB) and the pluggable database(PDB). With the launch of Oracle Database 12c, Oracle introduced a new concept which is called the “Common User“.

Every common user can connect and perform operations within the root container, and within any PDB where it has privileges. Let’s learn how to create a common user. And, how to assign SYSBACKUP privilege to it.

By assigning SYSBACKUP privilege to a COMMON USER you can have a dedicated schema for performing backup and recovery operations in your Oracle Database.

Characteristics of The Common User

Here are a few characteristics of a common user of Oracle Database

  1. A common user can log in to any container (including CDB$ROOT) in which it has the CREATE SESSION privilege.
  2. The name of every user-created common user must begin with the characters c## or C##.
  3. The names of common users must contain only ASCII or EBCDIC characters.
  4. Every common user is uniquely named across all containers.
  5. The schemas for a common user can differ in each container.

Now let’s create a common user for performing backup and recovery of Oracle Database using Recovery Manager (RMAN).

How To Create A Common User?

Creating a Common User for backup and recovery in Oracle Database is a very simple 2 step process. In step 1 we create a common user and in step 2 we grant it the necessary privileges.

Let’s start with step number 1

Step 1: Create The Common User

To create the user we will use “Create User” DDL command like this –

C:/> SQLPLUS / as sysdba

first connect with your root container database using SYS user. Now let’s quickly clear the screen and verify the user and the container.

SQL> cl scr

screen clear.

SQL> SHOW user;

We are connected with the database using sys user.

SQL> SHOW con_name

and we are connected with the root container as well. Next we will not be creating a normal user account rather create a common user

SQL> CREATE USER c##BackupAdmin 
     IDENTIFIED BY pass101
     DEFAULT TABLESPACE users 
     QUOTA 10M ON users 
     TEMPORARY TABLESPACE temp
     ACCOUNT UNLOCK;

On successful execution of the above CREATE USER DDL statement you will have your Common User with the name c##BackupAdmin created.

To better understand this CREATE USER statement you can refer to my tutorial on CREATE USER DDL – Just click here.

Step 2: Grant Privileges

Prior to Oracle 12c we had only one privilege that we could assign to the user for Backup and recovery operations, which was SYSDBA.

Since SYSDBA is a fully empowered database administrative privilege thus it’s not a good idea to assign it randomly to any user. It could lead to catastrophic events.

Keeping this glitch in mind Oracle Introduced a brand new privilege with the launch of Oracle Database 12c. And that was SYSBACKUP.

SYSBACKUP privilege had all the necessary privileges that were required to perform backup and recovery of the Oracle Database. And these privileges also happened to be a subset of SYSDBA privileges.

That meant the user which has SYSBACKUP privilege had all the power to perform backup and recovery but not the privileges that could make him or her a potential threat to the database.

Now let’s learn how to assign SYSBACKUP privilege to the common database user that we just created.

Assigning SYSBACKUP privilege to a user in Oracle Database needs only one simple statement. Like this –

SQL> GRANT SYSBACKUP TO c##BackupAdmin; 

That’s all you have to do –

In case you want to revoke SYSBACKUP privilege from the user then you can issue a REVOKE DCL statement like this –

SQL> REVOKE sysbackup FROM c##BackupAdmin; 

SYSBACKUP revoked successfully.

That is how we create a Common User and assign SYSBACKUP privilege to it. You can watch this live demonstration on my YouTube Video. And, For more such informational tutorials Subscribe to my YouTube channel.

Thanks and Have a Great Day!