In 12C release 1 Oracle has introduced new administrative privilege SYSBACKUP. It’s less powerful than SYSDBA:
- it enables to execute backup and restore commands in rman
- it enables to start stop the database
- it’s more restrictive – it hasn’t got SELECT ANY TABLE privilege
During installation of oracle database binaries the privilege is associated with OS group backupdba.
Example of adding oracle dedicated groups
#groups for database management groupadd -g 54321 oinstall groupadd -g 54322 dba groupadd -g 54323 oper groupadd -g 54324 backupdba groupadd -g 54325 dgdba groupadd -g 54326 kmdba groupadd -g 54327 asmdba groupadd -g 54328 asmoper groupadd -g 54329 asmadmin
add user Oracle for database software
useradd -u 54321 -g oinstall -G dba,oper,backupdba,dgdba,kmdba oracle
when installation of binaries is going on automatically new group is recognized
Example
For this presentation I used following installation Install Oracle 12C Release 1 (12.1) on Oracle Linux 7 (OEL7)
as user root add group backupdba to linux user tomasz
[root@oel7 ~]# usermod -a -G backupdba tomasz
check user groups
[tomasz@oel7 ~]$ id uid=1000(tomasz) gid=1000(tomasz) groups=1000(tomasz), 10(wheel),54324(backupdba)
connect as user tomasz to database and make backup. NOTE please prepare .bash_profile for this user. It can be copy from user oracle.
[tomasz@oel7 ~]$ rman target '"/ as sysbackup"' Recovery Manager: Release 12.1.0.2.0 - Production on Sun Nov 23 15:45:30 2014 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA12C (DBID=242189610) RMAN> backup database; Starting backup at 23-NOV-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=262 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set ...
OS user tomasz within database is identified by externally identified database user SYSBACKUP with very limited privileges. Database user hasn’t got SELECT ANY TABLE so is not able to see sensitive businesses data.
[tomasz@oel7 ~]$ sqlplus / as sysbackup SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 23 15:47:31 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show user USER is "SYSBACKUP" SQL> select * from session_roles; ROLE ---------------------------------- SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE SQL> select * from session_privs; PRIVILEGE ---------------------------------------- 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 14 rows selected. SQL>
Have a fun 🙂
Tomasz
hi thank you
but what if i want to separate the roles after the installation ?