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 PATHI'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 3After 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 YESThe 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 NUMBERIf 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-14When 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 droppedYou have no reason now to implement the right separation of duties that best fit for your requirements.
That's all.
