2019-09-10 | Adam Boliński

How to Read Encrypted Data

Before you start please note this is for education purpose, don’t do it if you don’t know what these tools are doing. Please be aware Don’t do it on your production database.

This blog post is regarding our POUG panel session Cloud vs On-premise. I will cover one subject on this panel related security in Autonomous Database Infrastructure. Based on the implementation of this technology in Autonomous Infrastructure Oracle Corp. stays that all data in client databases are encrypted and secured (Encryption Tablespace/Database Vault) and no one from Oracle can read your data. Please note that you don’t have access to Operating System daily maintenance is done by Oracle engineers.

I will not cover in this post how to create an encrypted tablespace and Database Vault it is too obvious and easy.

Before we start I would like to point out that this approach was created by my friend Kamil Stawiarski I just add few, not important tweaks. All information about this and tools created by Kamil you can find in his blog blog.ora-600.pl

Before we start we need to be sure that we have the newest version of Oracle, so let’s check :

Oracle Database 19c                                                  19.0.0.0.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch  29834717     : applied on Mon Sep 09 14:26:35 CEST 2019
Unique Patch ID:  23016168
Patch description:  "Database Release Update : 19.4.0.0.190716 (29834717)"
   Created on 10 Jul 2019, 02:09:26 hrs PST8PDT

Database Vault was also created so even sysdba do not have the option to select data from table employees.

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

So now let’s check if this approach is working :

SQL> alter system flush buffer_cache;

System altered.

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

DATA_OBJECT_ID
--------------
	 73351
SQL> select dbms_rowid.rowid_block_number(rowid) as bno, count(1)
     from   hr.employees
     group by dbms_rowid.rowid_block_number(rowid);

       BNO   COUNT(1)
---------- ----------
       131	   98
       132	    9

So we have two blocks in this table, let try to dump memory region (we use ipcs -m to identify proper one)

sgadump -b 8192 -d 73351 -s 983045 -o emps1
Dumped 2 blocks to emps1

Now let us check if we can read encrypted TBS using Kamil’s tool rico2

root@meshmgmt rico2]# python rico2.py listfile.rico2
RICO v2 by Kamil Stawiarski (@ora600pl | www.ora-600.pl)
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 rico2.py 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	/u01/app/oracle/oradata/ENC/datafile/secure_data01.dbf
rico2 > map
Wrong kind of block. Currently supported: 6.1
'FILE_NAME'
rico2 > set dba 1,131
	DBA		0x400083 (4194435 1,131)
rico2 > map
 File: /u01/app/oracle/oradata/ENC/datafile/secure_data01.dbf(1)
 Block: 131			Dba: 0x400083
------------------------------------------------------------
Wrong kind of block. Currently supported: 6.1
196
rico2 > d
 File: /u01/app/oracle/oradata/ENC/datafile/secure_data01.dbf(1)
 Block: 131 Offsets: 0 to 512		Dba: 0x400083
---------------------------------------------------------------
06a20000 83004001 460a2000 00000216 | ......@.F. .....
06850000 c4e35c37 2abcdb74 97522d92 | ......\7*..t.R-.
b53e353a 5b0714d9 c72f47d9 188f136e | .>5:[..../G....n
ffc27c0d 32df4373 4037326e edb6268e | ..|.2.Cs@72n..&.
1a07a6b1 81785df9 86cd71f0 c37c2d93 | .....x]...q..|-.
f8b6ee11 ab99ec19 0d999755 931a4b1b | ...........U..K.
303372f1 3f579a38 c60c85c3 85bb9be4 | 03r.?W.8........
2179f836 5ac1e8e4 e9c212ae d2a56d6f | !y.6Z.........mo
cf9ab13b 3b3d1b13 13547a39 eba4ed1b | ...;;=...Tz9....
cb027d9a 89735b53 e6e52fe4 cbabeff9 | ..}..s[S../.....
6df6bb00 5ef61cce 74a9d744 cc83d4e9 | m...^...t..D....
28fb620b 07c182bf 3b8c2c81 e829eba5 | (.b.....;.,..)..
08597341 9f7b48f9 a4629bd8 a1367693 | .YsA.{H..b...6v.
0edba332 d0a625fd 8564e48a ee5cafb4 | ...2..%..d...\..
c7ecf40a 03d26b0c 1cecd694 fc3df55c | ......k......=.\
1ed623d1 eca1bf15 2c2429dc 62156b0c | ..#.....,$).b.k.
89f474a4 4f8a0244 f712207b e53b4060 | ..t.O..D.. {.;@`
ec689634 3e157fbb f0c9e193 a7e87908 | .h.4>........y.
a4fe4ffa 88a10d94 28de884e ffc28997 | ..O.....(..N....
80e1647f 90ecd658 528200f2 870435aa | ..d...XR.....5.
7656e87d d8dbce79 9e71ceca 1e0310b6 | vV.}...y.q......
a5922816 2ea8fcaa 1d641b4e 572de23b | ..(......d.NW-.;
6362a6df 7711ffb5 4f4c7820 3ddfe368 | cb..w...OLx =..h
417282f2 86e26315 468aeb58 bf44fa96 | Ar....c.F..X.D..
d1062ea0 4548db67 0f186ba6 905d70c9 | ....EH.g..k..]p.
c4f6e6e4 40fca512 73e3c2df c6eabb5b | ....@...s......[
2b773704 1f7a79a6 97d725c5 27b4fc9c | +w7..zy...%.'...
68131b83 5bdc35b4 85af935f 744a2c8c | h...[.5...._tJ,.
d24b6bdf 239c10e9 91e6dc24 ea09b484 | .Kk.#......$....
b4c3a27d 9494ffcc 845e4ed2 1c8c22d6 | ...}.....^N...".
0cff5832 d7b69426 ee19e150 7331a9a0 | ..X2...&...Ps1..

<16 bytes per line>


So it is encrypted you can’t read it on disk, but let’s see our two blocks dump

[root@meshmgmt rico2]# python rico2.py listfile.rico2
RICO v2 by Kamil Stawiarski (@ora600pl | www.ora-600.pl)
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 rico2.py 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	/home/oracle/emps1
rico2 > set dba 1,0
	DBA		0x400000 (4194304 1,0)
rico2 > map
 File: /home/oracle/emps1(1)
 Block: 0			Dba: 0x400000
------------------------------------------------------------
 DATA Table/Cluster

 struct kcbh, 20 bytes				@0

 struct ktbbh,  96 bytes 			@20

 struct kdbh, 14 bytes 				@124

 struct kdbt[1], 4 bytes			@138

 sb2 kdbr[98]					@142

 ub1 freespace[850]				@338

 ub1 rowdata[7000]				@1188

 ub4 tailchk					@8188


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 > x /rncccctcnnnn
rowdata[6938]				@8126	0x2c
-------------
flag@8126:	0x2c
lock@8127:	0x2
cols@8128:	11


col    0[2]     @8129:  c202                                     100
col    1[6]     @8132:  53746576656e                             Steven
col    2[4]     @8139:  4b696e67                                 King
col    3[5]     @8144:  534b494e47                               SKING
col    4[12]    @8150:  3531352e3132332e34353637                 515.123.4567
col    5[7]     @8163:  78670611010101                           2003-06-17:00:00:00
col    6[7]     @8171:  41445f50524553                           AD_PRES
col    7[3]     @8179:  c30551                                   48000
col    8[0]     @8183:  *NULL*
col    9[0]     @8184:  *NULL*
col   10[2]     @8185:  c15b                                     90

Even strings (raw form) can show you unencrypted contents of the table (from sgadump file), take a look :

....
....
SA_REP
Ellen
Abel
EABEL
011.44.1644.429267
SA_REP
Sundita
Kumar
SKUMAR
011.44.1343.329268
SA_REP
I	Elizabeth
Bates
EBATES
011.44.1343.529268
SA_REP
William
Smith
WSMITH
011.44.1343.629268
SA_REP
Tayler
TFOX
011.44.1343.729268
SA_REP
Harrison
Bloom
HBLOOM
011.44.1343.829268
.....
...

So in summary as Kamil wrote before in his post :

So as you can see – it is possible even with ASMM but it is much, much harder and we don’t want to make things easy for potential „hackers” ;).
And nothing is impossible in IT. It can be just unaffordable… or it can take longer than a life of one generation 😉
Good security is made by people you know and people you should trust. Nothing will protect itself – no matter how fancy software you’ll buy. Even the most advanced autonomous software – human immunology system – requires maintenance and correct usage and – from time to time – diagnostic by an expert you trust.