Archives For November 2013

sysbackup, be careful

November 24, 2013 — Leave a comment

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.

oracle 12c introduced common (valid for all containers) and local (valid for just a pdb) users. one might think that creating a common user and assigning privileges to that users results in the same set of privileges in all the pdbs. this is not the case:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create user c##1 identified by "test" container=all;

User created.

SQL> grant create session to c##1;

Grant succeeded.

SQL> connect c##1/test
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> connect c##1/test@pdb1
ERROR:
ORA-01045: user C##1 lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

so, either you’ll have to grant the privilege in each container you want the user to be able to do a specfic task or you’ll need to grant it for all containers:

SQL> grant create session to c##1 container=all;

Grant succeeded.

SQL> connect c##1@pdb1 
Enter password: 
Connected.
SQL> 

what happens if a new pdb is created? do we need to re-grant the privilege for the new pdb?

SQL> conn / as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER admin2 IDENTIFIED BY "test"
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE pbd1
    DATAFILE '/oradata/orcl/pdb1/pdb2_01.dbf' SIZE 250M AUTOEXTEND ON
  PATH_PREFIX = '/oradata/orcl/pdb2'
  FILE_NAME_CONVERT = ('/oradata/orcl/pdbseed/', '/oradata/orcl/pdb2');
  2    3    4    5    6  

Pluggable database created.

SQL> SQL> alter pluggable database pdb2 open;

Pluggable database altered.
SQL> connect c##1/test@pdb2
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB2
SQL> 

no. once granted on the root level the privilege is available on all the current pdbs and all pdbs that might get created in the future. what will happen if you unplug a pdb and plug it to a container which does not have the commen user? needs to be tested …

almost on demand

November 13, 2013 — Leave a comment

saturday, early in the morning morning, everybody else is still sleeping. a perfect time to do one of the oracle on demand sessions I wanted to complete:

grrr….

dbms_qopatch

November 9, 2013 — Leave a comment

some time ago I blogged on how to list the installed patches for an oracle database. if you are on 12c there is another possibility:

SQL> select dbms_qopatch.get_opatch_lsinventory from dual;
 
    OracleHome-94047000-c49c-4d73-b13c-d252248d3c09
    oracle_home
    /opt/oracle/oraInventory
    false
    oneoff
    /opt/oracle/product/base/12.1.0.1
    oracle_home
  
  
....

this returns a xml of the opatch inventory right out of the database. another nice little feature introduced with 12c.

if you check the documentation you’ll notice that there are some other functions you might use to query the optach inventory.