The sysbackup privilege – how, when, and why should you use it?

It is recommended that you use the sysbackup administrative privilege instead of the sysdba administrative privilege to perform operations related to backup and recovery tasks.

Getting ready

For this recipe, you'll need:

• An existing database user (for example, tom) and a password file in 12c format, if you want to complete it using a password-authenticated user
• An existing OS user (for example, john), who belongs to the backupdba OS group, in order to connect to the database using OS authentication

How to do it...

Instructions are given in the Database authentication and OS authentication sections.

Database authentication

The instructions for database authentication are as follows:

1. Connect to the database as sysdba (or another user that can grant the sysbackup privilege):

   sqlplus / as sysdba
   

2. Grant the sysbackup privilege to user tom:

   grant sysbackup to tom;
   

3. Verify that there is an entry in the password file that grants user tom the sysbackup administrative privilege. Select data from the v$pwfile_users view:

   select * from v$pwfile_users;
   

   The following table is the result of the preceding command:

   Username	sysdb	sysop	sysas	sysba	sysdg	syskm	con_id
   sys	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	0
   sysdg	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	0
   sysbackup	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	0
   syskm	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	0
   tom	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	0

4. Test the connection using RMAN:

   rman target '"tom/oracle_123 as sysbackup"'
   

OS authentication

The instructions for OS authentication are as follows:

1. Verify that the OS user (for example, john) is a member of the backupdba OS group:

   $ id john
   

2. Connect to the database using the sysbackup privilege (SQL*Plus or RMAN):

   $> sqlplus / as sysbackup
   $> rman target '"/ as sysbackup"'
   

How it works...

You can use either Oracle Recovery Manager (RMAN) or SQL*Plus to perform the operations. When you connect to the database as sysbackup, you are connected as a predefined user sysbackup. If you want to check this, run the following statement:

SQL> select user from dual;

Otherwise, the following statement:

SQL> show user

Using the sysbackup privilege, you can connect to the database even when it is not open. This privilege enables better separation of duties and the implementation of the least privilege principle.

Note

From a security perspective, it is recommended that you implement the least privilege principle. The least privilege principle is an important security concept that requires that users are given only those privileges they need to perform their job.

To view the list of privileges a user can exercise when connected to the database using sysbackup privilege, you can create a user (for example, tom) and grant the user only sysbackup privileges. The next step is to connect to the database as user tom, using the sysbackup privilege and the execute statement:

select * from session_privs;

These privileges are shown in the following table:

Privileges (output from the previous statement)
sysbackup	select any transaction	select any dictionary	resumable
create any directory	alter database	audit any	create any cluster
create any table	unlimited tablespace	drop tablespace	alter tablespace
alter session	alter system

This is how you can check enabled roles:

SQL> select * from session_roles;

ROLE
-------------------
 SELECT_CATALOG_ROLE
 HS_ADMIN_SELECT_ROLE

Note

HS_ADMIN_SELECT_ROLE is granted to SELECT_CATALOG_ROLE.

If you want to view the roles and privileges granted to sysbackup, you can query DBA_ROLE_PRIVS and DBA_SYS_PRIVS:

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

Also, this new administrative privilege enables you to select, insert, delete, execute, and perform operations:

SELECT	PERFORM operations
X$ tables	STARTUP, SHUTDOWN
V$ and GV$ views	CREATE PFILE, CREATE SPFILE
APPQOSSYS.WLM_CLASSIFIER_PLAN	CREATE CONTROLFILE
SYSTEM.LOGSTDBY$PARAMETERS	FLASHBACK DATABASE
INSERT/DELETE	DROP DATABASE
SYS.APPLY$_SOURCE_SCHEMA	CREATE/DROP RESTORE POINT (including GUARANTEED restore points)
SYSTEM.LOGSTDBY$PARAMETERS
EXECUTE
SYS.DBMS_BACKUP_RESTORE	SYS.DBMS_DATAPUMP
SYS.DBMS_RCVMAN	SYS.DBMS_IR
SYS.DBMS_PIPE	SYS.SYS_ERROR
SYS.DBMS_TTS	SYS.DBMS_TDB
SYS.DBMS_PLUGTS	SYS.DBMS_PLUGTSP

Tip

It is important for you to remember that: When using the sysbackup privilege, you can't view application data.

There's more...

You can't drop user sysbackup. In a multitenant environment, you can restrict a user to be able to perform backups only for the PDB it can connect to. You can accomplish that by creating a local user in the PDB and granting the sysbackup privilege to the user. When you are connected to the database as the sysbackup, you are connected as sysbackup user to SYS schema:

SQL> connect / as sysbackup
Connected.

SQL> show user
USER is "SYSBACKUP"

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')---------------------------------------SYS

See also

• Creating password-authenticated users
• Creating and using OS-authenticated users