Quirks with the SYSBACKUP privilege in 12c

Oracle 12c has a great new privilege, tailor made for the paranoid oracle DBA guys like me. However, there are still some quirks: SYSBACKUP privilege doesn’t work over Oracle*Net. If the authentication method is SID based local authentication, all is well:
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 5027385344 bytes
Fixed Size    2298736 bytes
Variable Size    1040190608 bytes
Database Buffers    3976200192 bytes
Redo Buffers    8695808 bytes
Database mounted.
SQL>

Now, let’s do a cold backup of tablespace USERS:

[oracle@medo admin]$ rman target system
Recovery Manager: Release 12.1.0.1.0 – Production on Sat Feb 22 17:20:06 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
target database Password:
connected to target database: ORA12C (DBID=214280212, not open)
RMAN> backup tablespace users;
Starting backup at 22-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data/oradata/ora12c/ORA12C/datafile/o1_mf_users_9f093j2d_.dbf
channel ORA_DISK_1: starting piece 1 at 22-FEB-14
channel ORA_DISK_1: finished piece 1 at 22-FEB-14
piece handle=/data/oradata/fra/ORA12C/backupset/2014_02_22/o1_mf_nnndf_TAG20140222T172029_9jl8of7v_.bkp tag=TAG20140222T172029 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-FEB-14
Starting Control File and SPFILE Autobackup at 22-FEB-14
piece handle=/data/oradata/fra/ORA12C/autobackup/2014_02_22/o1_mf_s_840215852_9jl8ohd8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-FEB-14
RMAN>
User SYSTEM does not have SYSDBA privilege. However, the V$PWFILE_USERS doesn’t show that while the instance is in the “MOUNTED” state:
SQL> select * from v$pwfile_users;
USERNAME    SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
—————————— —– —– —– —– —– —– ———-
SYS    TRUE  TRUE  FALSE FALSE FALSE FALSE    1
When the database is open, the user “SYSTEM” is clearly visible:
SQL> alter database open;
Database altered.
SQL> select * from v$pwfile_users;
USERNAME    SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
—————————— —– —– —– —– —– —– ———-
SYS    TRUE  TRUE  FALSE FALSE FALSE FALSE    0
SYSTEM    FALSE FALSE FALSE TRUE  FALSE FALSE    3
This shows that SYSTEM is not a SYSDBA user. However, the connection as SYSDBA will succeed:

SQL*Plus: Release 12.1.0.1.0 Production on Sat Feb 22 17:33:53 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> connect system as sysdba
Enter password:
Connected.

While the database is not open, SYSBACKUP connection is equivalent to a SYSDBA connection, because it’s impossible to read the user data while the database is not open. When the database is opened, the first attempt is surprising:

SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

This is because we are connecting to the container database which doesn’t contain the table SCOTT.EMP. Let’s try with the pluggable database which does contain that table:

SQL> connect system@local as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges

User SYSTEM cannot connect as SYSDBA, because that privilege wasn’t granted to that user. There is new connection syntax which allows connecting as SYSBACKUP, which was granted to the user SYSTEM:

SQL> connect system@local as sysbackup
Enter password:
Connected.
Let’s now open the pluggable database:
SQL> alter pluggable database open;
Pluggable database altered.
SQL> select * from scott.emp;
select * from scott.emp
               
ERROR at line 1:
ORA-01031: insufficient privileges

That’s the main advantage of the SYSBACKUP role: it prevents the backup operator from seeing the user data. Backup also works, which was the intention. The problem arises with connecting through the listener. It works with the user “SYS” or any other SYSDBA  user:

RMAN> connect target sys@local
target database Password:
connected to target database: ORA12C (DBID=214280212)

It doesn’t work with the SYSBACKUP users:

[oracle@medo admin]$ rman
Recovery Manager: Release 12.1.0.1.0 – Production on Sat Feb 22 18:09:20 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target system@local
target database Password:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges
RMAN>

This is a big problem because various network backup utilities use Oracle*Net connection. Unfortunately, rman attempts sysdba connection with “as SYSDBA”  and that doesn’t work. The problem is in the rman utility itself because sqlplus allows network connection as SYSBACKUP:

SQL*Plus: Release 12.1.0.1.0 Production on Sat Feb 22 18:14:15 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL> connect system@local as sysbackup
Enter password:
Connected.

About these ads

About mgogala

I am a long time Oracle DBA, who has worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s