How to use the new SYSBACKUP privilege to perform backup and recovery tasks without accessing to table data

Tweet

The SYSBACKUP is a new privilege introduced in the Oracle Database 12c release that allows a user to perform any backup or recovery operations.

It is heartily suggested to create a new user with the SYSBACKUP privilege granted and perform daily backup activities with that new user, instead of using directly the SYSBACKUP user created by default during the database installation: the SYSBACKUP user should even remain locked.

Before proceeding I will need to create a new user at the operating system level, but not belonging to the dba OS group: in this way, using the password file, I will able to assign the sysbackup privilege to a normal database user and forcing him to log in using a password.

Indeed if I try to log into the database when I'm already authorized at the OS level because I'm the oracle OS user, I am allowed to connect with the SYSBACKUP privilege (and of course as SYSDBA, SYSOPER, SYSDG and SYSKM) even with a wrong user and/or password.
Let's see few examples considering that when you are connecting AS SYSDBA the current schema is SYS (independently from the username you specify in the sqlplus connection) and the session USER is SYS; when you are connecting AS SYSBACKUP the current schema is again SYS, but the session user is identified as SYSBACKUP.

[oracle@localhost ~]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed May 7 06:12:19 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema from dual;

CURRENT_SCHEMA
------------------
SYS

SQL> select sys_context('USERENV', 'SESSION_USER') session_user from dual;

SESSION_USER
------------------
SYS

SQL> exit

In the previous example I was able to connect as SYSDBA because the user was "already" verified by the operating system; when I try to connect using a normal database user, in this case specifying marcov and its right password, it happens the same thing that is I'm not connected as the specified user, but as SYS user

[oracle@localhost ~]$ sqlplus marcov/marcov as sysdba
SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

CURRENT_SCHEMA         SESSION_USER
-------------------- --------------------
SYS             SYS

What does it happens when I try to connect AS SYSDBA using a normal database user and a wrong password ?
Again I'm able to connect to the database as the SYS user.

[oracle@localhost ~]$ sqlplus marcov/m as sysdba
SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

CURRENT_SCHEMA         SESSION_USER
-------------------- --------------------
SYS             SYS

What does it happens when I try to connect AS SYSDBA using a non existing database user and a password ?
Again I'm able to connect to the database as the SYS user.
All this happens because I'm using the oracle user at the operating system level which belongs to the dba operating system group and so it's allowed
to estabilish a connection to the local database. There is a correspondence between the OS groups and the database system privileges: the dba OS group maps to the SYSDBA database system privilege (as previously seen), the oper OS group (when created and specified during the installation process) maps to the SYSOPER database system privilege, the oinstall OS group doesn't map to any database system privilege.

[oracle@localhost ~]$ sqlplus m/m as sysdba

SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

CURRENT_SCHEMA         SESSION_USER
-------------------- --------------------
SYS             SYS

So the first step to test a connection AS SYSBACKUP database system privilege is to avoid a connection using the oracle operating system user: I need to create another user at the operating system level without special group. I'm going to create marcov OS user:

[root@localhost home]# useradd marcov -p marcov
[root@localhost home]# id marcov
uid=54322(marcov) gid=54323(marcov) groups=54323(marcov)

Now you have to configure this account to let it connect to the database: I simply copied the TNS file located at
/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora, not accessible by the new user, under the default directory of marcov OS user (/home/marcov) and modified the /home/marcov/.bash_profile to add few environment variables to marcov's profile:

[root@localhost home]# cp /app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora /home/marcov
[root@localhost home]# chown marcov.marcov /home/marcov/tnsnames.ora
[root@localhost home]# cat /home/marcov/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
TNS_ADMIN=/home/marcov
ORACLE_HOME=/app/oracle/product/12.1.0/dbhome_1
ORACLE_SID=CDB001
export TNS_ADMIN
export ORACLE_HOME
export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin

export PATH

I've already a database schema named MARCOV. From the marcov OS user I'm now able to create a connection with the local database using that schema name and password specifying the tns entry of a pluggable database.

root@localhost ~]# su - marcov
[marcov@localhost ~]$ sqlplus marcov/marcov@PDB001
SQL> show user
USER is "MARCOV"

I'm not of course able to connect using the system privilege AS SYSBACKUP. If I try the error "ORA-01017: invalid username/password; logon denied" is raised.

[marcov@localhost ~]$ sqlplus marcov/marcov@PDB001 as sysbackup

SQL*Plus: Release 12.1.0.1.0 Production on Wed May 7 06:47:42 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 

I need to explicity grant the SYSBACKUP system privilege to the MARCOV database user:

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> alter session set container=PDB001;

Session altered.

SQL> grant sysbackup to marcov;

Grant succeeded.

Once the GRANT command is assigned to MARCOV database user, you can see a new entry in the V$PWFILE_USERS view: the view says that the username MARCOV is into the orapwd file and can connect to the local database using the SYSBACKUP system privilege.

SQL> select * from v$pwfile_users;

USERNAME               SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                   TRUE  TRUE  FALSE FALSE FALSE FALSE        0
MARCOV                   FALSE FALSE FALSE TRUE  FALSE FALSE        3

After having received the system privilege, MARCOV database user is now able to connect AS SYSBACKUP. Have a look at the CURRENT_SCHEMA and at the SESSION_USER values.

[marcov@localhost ~]$ sqlplus marcov/marcov@PDB001 as sysbackup
SQL> col current_schema format a10
SQL> col session_user format a20
SQL> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

CURRENT_SC SESSION_USER
---------- --------------------
SYS       SYSBACKUP

The main goal of the system privilege SYSBACKUP is to implement the so called separation of duties: you can grant SYSBACKUP privilege to a user allowed only to perform backup or recovery operation using the RMAN command line, but without possibility to see or access the data of the database.
Let's first see what are the default system privileges and roles associated with the SYSBACKUP privilege.

SQL> show user;
USER is "SYSBACKUP"
SQL> set pages 999
SQL> col grantee format a25
SQL> col granted_role format a25
SQL> select * from dba_sys_privs where grantee = 'SYSBACKUP';

GRANTEE           PRIVILEGE                   ADMIN_OPTION COMMON
------------------------- ---------------------------------------- ------------ ------
SYSBACKUP          ALTER SYSTEM                   NO           YES
SYSBACKUP          AUDIT ANY                   NO           YES
SYSBACKUP          SELECT ANY TRANSACTION           NO           YES
SYSBACKUP          SELECT ANY DICTIONARY            NO           YES
SYSBACKUP          RESUMABLE                   NO           YES
SYSBACKUP          CREATE ANY DIRECTORY               NO           YES
SYSBACKUP          UNLIMITED TABLESPACE               NO           YES
SYSBACKUP          ALTER TABLESPACE               NO           YES
SYSBACKUP          ALTER SESSION                NO           YES
SYSBACKUP          ALTER DATABASE               NO           YES
SYSBACKUP          CREATE ANY TABLE               NO           YES
SYSBACKUP          DROP TABLESPACE               NO           YES
SYSBACKUP          CREATE ANY CLUSTER               NO           YES

13 rows selected.

SQL> select * from dba_role_privs where grantee = 'SYSBACKUP';

GRANTEE           GRANTED_ROLE            ADMIN_OPTION DEFAULT_ROLE COMMON
------------------------- ------------------------- ------------ ------------ ------
SYSBACKUP          SELECT_CATALOG_ROLE        NO             YES          YES

The column COMMON (as states in the Oracle documentation: http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn23274.htm#REFRN23274)
"indicates how the grant was made. Possible values: YES if the role was granted commonly (CONTAINER=ALL was used); NO if the role was granted locally (CONTAINER=ALL was not used)"

When you are connected using MARCOV database user which has the SYSBACKUP system privilege you are able to use the DESCRIBE
command on every database objects, but you are not allowed to have a look at the data contained by the object.
Here is an example of what you receive (ORA-01031: insufficient privileges) when you try to see the data within the object.

SQL> show user;
USER is "SYSBACKUP"
SQL> select table_name from dba_tables where owner = 'MARCOV';

TABLE_NAME
------------------------
T2
T1

SQL> select * from MARCOV.T1;
select * from MARCOV.T1
                     *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> desc MARCOV.T1;
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                            NUMBER

If your database user with SYSBACKUP privilege needs to access some data, it should directly receive SELECT grant on specific tables.

Once you have a database user with SYSBACKUP privilege you can create a RMAN connection to manage your backups.

[marcov@localhost ~]$ rman

Recovery Manager: Release 12.1.0.1.0 - Production on Wed May 7 13:46:50 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target 'marcov/marcov@PDB001 as sysbackup'

connected to target database: CDB001 (DBID=4134986109)

RMAN> select sys_context('USERENV', 'CURRENT_SCHEMA') current_schema, sys_context('USERENV', 'SESSION_USER') session_user from dual;

using target database control file instead of recovery catalog


CURRENT_SCHEMA     SESSION_USER
------------------ ------------------
SYS                SYSBACKUP

From the RMAN session you can for example take a backup of the current controlfile or have a list of available backups.

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51      Full    209.94M    DISK        00:00:54     14-APR-14      
        BP Key: 51   Status: AVAILABLE  Compressed: YES  Tag: TAG20140414T151315
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_04_14/o1_mf_nnndf_TAG20140414T151315_9nqqw3np_.bkp
  List of Datafiles in backup set 51
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf
  8       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
  9       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf

RMAN> backup current controlfile;

Starting backup at 07-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 07-MAY-14
channel ORA_DISK_1: finished piece 1 at 07-MAY-14
piece handle=/app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_05_07/o1_mf_ncnnf_TAG20140507T135023_9pn7j3y4_.bkp tag=TAG20140507T135023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 07-MAY-14

Starting Control File and SPFILE Autobackup at 07-MAY-14
piece handle=/app/oracle/fast_recovery_area/CDB001/autobackup/2014_05_07/o1_mf_s_846942632_9pn7j9dy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-MAY-14

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51      Full    209.94M    DISK        00:00:54     14-APR-14      
        BP Key: 51   Status: AVAILABLE  Compressed: YES  Tag: TAG20140414T151315
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_04_14/o1_mf_nnndf_TAG20140414T151315_9nqqw3np_.bkp
  List of Datafiles in backup set 51
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  7       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/system01.dbf
  8       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/sysaux01.dbf
  9       Full 3389939    14-APR-14 /app/oracle/oradata/CDB001/PDB001/PDB001_users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56      Full    1.13M      DISK        00:00:05     07-MAY-14      
        BP Key: 56   Status: AVAILABLE  Compressed: YES  Tag: TAG20140507T135023
        Piece Name: /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_05_07/o1_mf_ncnnf_TAG20140507T135023_9pn7j3y4_.bkp
  Control File Included: Ckp SCN: 3418486      Ckp time: 07-MAY-14

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
57      Full    17.20M     DISK        00:00:02     07-MAY-14      
        BP Key: 57   Status: AVAILABLE  Compressed: NO  Tag: TAG20140507T135032
        Piece Name: /app/oracle/fast_recovery_area/CDB001/autobackup/2014_05_07/o1_mf_s_846942632_9pn7j9dy_.bkp
  SPFILE Included: Modification time: 07-MAY-14
  SPFILE db_unique_name: CDB001
  Control File Included: Ckp SCN: 3418494      Ckp time: 07-MAY-14

When you want to connect to a specific pluggable database using the AS SYSBACKUP syntax directly from the Linux command line you should quote your command with single and double quotes.

[marcov@localhost ~]$ rman target '"marcov/marcov@PDB001 as sysbackup"'

Recovery Manager: Release 12.1.0.1.0 - Production on Wed May 7 13:52:19 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB001 (DBID=4134986109)

RMAN>

As latest consideration the SYSBACKUP user accounts cannot be dropped.

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> drop user SYSBACKUP;
drop user SYSBACKUP
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped

You have no reason now to implement the right separation of duties that best fit for your requirements.

That's all.