SYSBACKUP and SYSDG permissions in Oracle 12c

Introduction

In many companies there is a clear separation of duties for various Oracle Database related tasks such as administering ASM and backing up/restoring Oracle databases.

In the past, DBAs used SYSDBA permission for administering ASM and RMAN. As you probably know, SYSDBA is the most powerful permission in Oracle Database which even allows viewing all the application data.

Oracle realized that they need to address the separation of duties requirement of many customers and therefore they have provided in Oracle 11g a dedicated permission for administering ASM – I've written a dedicated blog post in the past for this matter. The SYSASM permission cannot access application data, but it can perform various ASM related management tasks (such as altering diskgroup, adding disks, etc.)

What about RMAN?

Until Oracle Database version 12cR1, there wasn't a good solution from a separation of duties when it comes to RMAN backups as users had to use SYSDBA which also allows them to access any application data (as well as other strong permissions).

In Oracle 12cR1, Oracle introduced the SYSBACKUP permission which allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus.

You can view here the full list of operations allowed by this administrative privilege

And what about Data Guard?

Very similar to RMAN, Oracle also introduced in version 12cR1 a dedicated privilege named SYSDG which can be used with the Data Guard Broker and the DGMGRL command-line interface. 

SQL> SELECT username, account_status
  FROM dba_users
 WHERE username LIKE '%SYS%';

USERNAME   ACCOUNT_STATUS
---------- --------------------------------
SYS        OPEN
SYSTEM     OPEN
SYS$UMF    EXPIRED & LOCKED
APPQOSSYS  EXPIRED & LOCKED
GGSYS      EXPIRED & LOCKED
WMSYS      EXPIRED & LOCKED
SYSBACKUP  EXPIRED & LOCKED
SYSRAC     EXPIRED & LOCKED
AUDSYS     EXPIRED & LOCKED
SYSKM      EXPIRED & LOCKED
SYSDG      EXPIRED & LOCKED

SQL> connect / as sysbackup
Connected.
SQL> show user
USER is "SYSBACKUP"

SQL> connect / as sysdba
Connected.

SQL> create user C##PINI identified by PINI;
User created.

SQL> grant SYSBACKUP to C##PINI;
Grant succeeded.

SQL> select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;

USERNAME   SYSBA SYSDG
---------- ----- -----
SYS        FALSE FALSE
SYSDG      FALSE TRUE
SYSBACKUP  TRUE  FALSE
SYSKM      FALSE FALSE
C##PINI    TRUE  FALSE

Note that in order to connect to the database as either SYSDG or SYSBACKUP using a password, there must be a password file for it because it is possible to connect even when the database is not up and running, as follows

SQL> connect / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  8793400 bytes
Variable Size             989856456 bytes
Database Buffers          637534208 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> connect c##pini/pini
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Warning: You are no longer connected to ORACLE.

SQL> connect c##pini/pini as SYSBACKUP;
Connected.

Summary

In this post we've reviewed the SYSDG and SYSBACKUP users and permissions in Oracle 12c which could be useful in case that in your company there is a requirement to have a separation of duties for backup/recovery as well as for Data Guard related administration tasks. I hope you find it useful for you.

Share on social media

If you think your colleagues would benefit from this article, share it now on social media with the buttons located at the top of this blog post. Thanks!