SYSBACKUP and SYSDG permissions in Oracle 12c

    May 29, 2017 12:46:00 PM by Pini Dibask

    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. 

     

    Demo

    First, we can connect to a 12c instance and look for those accounts. Next step would be to connect / AS SYSBACKUP since I'm logged with a user that has OS permissions to connect without any username and password

    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"
    I can also create a new user and grant him the SYSBACKUP or SYSDG permissions
    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.

    Tags: Oracle

    Pini Dibask

    Written by Pini Dibask

    Pini Dibask is an Oracle Database Technologist and Architect with more than 10 years of experience. During these years Pini has worked as an Oracle DBA and Oracle DBA team leader. He is the Product Manager for Databases Solutions at Dell Software. Pini is an Oracle Database Certified Professional and also OPN Certified Specialist. See his blog at: OracleDBPro.BlogSpot.com Expertise: Performance Tuning Storage & ASM RAC & RAC One Node Failover Clusters Data Guard Golden Gate RMAN Flashback Features Installations & Upgrades Data Migrations Exadata Troubleshooting OTN Community forum page: http://community.oracle.com/people/Pini%20Dibask