Richard Foote's Oracle Blog

Indexing and Transparent Data Encryption Part II (Hide Away) May 20, 2015

In Part I, I quickly ran through how to setup an encrypted tablespace using Transparent Data Encryption and to take care creating indexes outside of these tablespaces.

Another method of encrypting data in the Oracle database is to just encrypt selected columns. Although the advantage here is that we can just encrypt sensitive columns of interest (and that the data remains encrypted within the buffer cache), this method has a number of major restrictions, especially in relation to indexing.

To first set the scene, I’ll start by creating and populating an unencrypted table:

SQL> create table bowie (id number, code number, salary number, text varchar2(30)) tablespace unsafe;

Table created.

SQL> insert into bowie select rownum, 42, rownum, 'BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

I’ll now create an index on the (effectively unique) ID column:

SQL> create index bowie_id_i on bowie(id);

Index created.

We can effectively use this index on some basic range based predicates:

SQL> select * from bowie where id between 42 and 43;

ID       CODE     SALARY TEXT
---------- ---------- ---------- ------------------------------
42         42         42 BOWIE
43         42         43 BOWIE
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     2 |   112 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE      |     2 |   112 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_ID_I |     2 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

SQL> select * from bowie where id > 10000000000;

no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes |  Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |    56 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE      |     1 |    56 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_ID_I |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

So all is well with our index here and is used as expected.

If we perform a block dump and have a look at the first table block containing rows (following are excerpts from the block dump):

Block header dump:  0x020001dc
Object id on Block? Y
seg/obj: 0x16cda  csc: 0x00.2b3abc  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x20001d8 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.006.000005dd  0x014004e3.00bb.17  –U-  330  fsc 0x0000.002b3aca
0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
bdba: 0x020001dc
data_block_dump,data header at 0x1c72e64
===============
tsiz: 0x1f98
hsiz: 0x2a6
pbl: 0x01c72e64
76543210
flag=——–
ntab=1
nrow=330
frre=-1
fsbo=0x2a6
fseo=0x5d6
avsp=0x330
tosp=0x330
0xe:pti[0] nrow=330 offs=0

…

We notice the block contains 330 rows. Note this number as we’ll check back on it later.

If we look further on in the dump to the section listing some of the rows within the block:

block_row_dump:
tab 0, row 0, @0x181a
tl: 20 fb: –H-FL– lb: 0x1  cc: 4
col  0: [ 3]  c2 07 47
col  1: [ 2]  c1 2b
col  2: [ 3]  c2 07 47
col  3: [ 5]  42 4f 57 49 45
tab 0, row 1, @0x182e
tl: 20 fb: –H-FL– lb: 0x1  cc: 4
col  0: [ 3]  c2 07 48
col  1: [ 2]  c1 2b
col  2: [ 3]  c2 07 48
col  3: [ 5]  42 4f 57 49 45
tab 0, row 2, @0x1842
tl: 20 fb: –H-FL– lb: 0x1  cc: 4
col  0: [ 3]  c2 07 49
col  1: [ 2]  c1 2b
col  2: [ 3]  c2 07 49
col  3: [ 5]  42 4f 57 49 45
tab 0, row 3, @0x1856

…..

We can see the 4 columns of each row and note that the length of the ID and CODE columns are 3 and 2 bytes respectively. We can also see that the hex values of the CODE column (col 1) are all the same: c1 2b (as they all have a value of 42).

OK, time to encrypt some columns. I’ll re-create the table but this time encrypt both the ID and CODE columns using all the default settings:

SQL> drop table bowie;

Table dropped.

SQL> create table bowie (id number encrypt, code number encrypt, salary number, text varchar2(30)) tablespace unsafe;

Table created.

SQL> insert into bowie select rownum, 42, rownum, 'BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> desc bowie
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------

ID                                                 NUMBER ENCRYPT
CODE                                               NUMBER ENCRYPT
SALARY                                             NUMBER
TEXT                                               VARCHAR2(30)

If we look at a dump of this table we notice a number of key differences:

Block header dump:  0x02000464
Object id on Block? Y
seg/obj: 0x16cef  csc: 0x00.2e6e1f  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x2000460 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.001.00000671  0x01409e62.00ec.19  –U-   60  fsc 0x0000.002e6e7b
0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
bdba: 0x02000464
data_block_dump,data header at 0x1d82e64
===============
tsiz: 0x1f98
hsiz: 0x8a
pbl: 0x01d82e64
76543210
flag=——–
ntab=1
nrow=60
frre=-1
fsbo=0x8a
fseo=0x3dc
avsp=0x352
tosp=0x352
0xe:pti[0] nrow=60 offs=0

….

The first difference is that where previously we had 330 rows, the block now only contains 60 rows of data.

If we look further on to the rows themselves:

tab 0, row 0, @0x1b72
tl: 118 fb: –H-FL– lb: 0x1  cc: 4
col  0: [52]
bd 33 16 9a aa 6a 68 cf f7 a2 99 6c 3d b0 0c 1f 11 3d 42 cb 76 20 52 cc 18
8a 96 fd 0e 59 41 f2 a0 0f da 96 37 40 9b 28 dd 93 82 29 74 32 f1 53 c5 27
e8 e1
col  1: [52]
57 0c 63 c8 4f a9 42 f7 61 c3 63 b2 1b 0e f9 bc 74 5b 74 46 87 08 26 23 6b
c9 ae 52 ca 0e 31 9e ac 54 79 f7 2d f9 64 41 30 e5 6e 11 00 a3 55 d4 81 42
a4 19
col  2: [ 2]  c1 3e
col  3: [ 5]  42 4f 57 49 45
tab 0, row 1, @0x1be8
tl: 118 fb: –H-FL– lb: 0x1  cc: 4
col  0: [52]
c3 13 d5 f7 a8 1f 46 8b bd 2b a9 e0 25 8e b3 15 2d 52 77 f5 fa 8a 52 46 f6
1d 0d 0a 58 8f 68 51 ea 2f 8c 0e 56 9d 2b cf 6f 86 7c d4 a9 d9 2a 25 fd a1
6e 7d
col  1: [52]
49 19 8f 40 da 0e ad fb 7a 79 6d 50 61 c9 9e 33 90 3f 73 c9 64 0a f0 98 c6
2f 8c c1 3f 47 fd 78 55 be 5c e5 df 1f 94 ab c0 3b a2 e3 37 65 f4 8e 80 c0
f4 66
col  2: [ 2]  c1 3f
col  3: [ 5]  42 4f 57 49 45
tab 0, row 2, @0x1c5e
tl: 118 fb: –H-FL– lb: 0x1  cc: 4
col  0: [52]
1e 9d 90 85 70 e8 50 39 c9 64 9f 85 6f c6 e6 03 44 8c 59 73 9a 0e a6 cb 3c
ff 41 91 42 2a 36 f1 98 b7 54 61 24 1a 62 87 3f b9 21 5e d7 43 f6 39 14 7a
d0 34
col  1: [52]
e5 a0 4a 82 06 12 88 08 fb df db b7 00 19 60 cc d1 da 98 34 1d 24 44 64 79
04 48 29 7a 2c 5d 26 06 0d f3 5a 42 1c 34 59 65 14 85 53 e7 07 ac ee 11 73
82 5f
col  2: [ 2]  c1 40
col  3: [ 5]  42 4f 57 49 45

….

We see the reason why we have fewer rows per block is that the encrypted columns have significantly increased in size. Where previously they were just 3 and 2 bytes respectively, both the ID and CODE columns are now 52 bytes in length. The actual size would in part depend on the encryption algorithm used (some algorithms round to the next 8 bytes), in this example I used the default AES192.

With AES192, the length of the column is rounded to the next 16 bytes. However, if we simply encrypt a columns as is, it would mean a column value would be encrypted to the same value when using the same encryption key. This means a malicious person could potentially attempt to reverse engineer a value by inserting known column values and seeing if the generated encrypted values are the same as those in the table. To prevent this, Oracle by default adds “Salt”, which is basically a random string, to the column value being encrypted to make it now impossible to reverse engineer the inserted value. This adds another 16 bytes to the length of the column value. If we look at the second CODE column (col 1) in the block dump, we notice they all have different encrypted values even though they all have the same actual value of 42 within the table.

So that’s 32 bytes accounted for. The remaining 20 bytes is a result of TDE adding a Message Authentication Code (MAC) to each encrypted value for integrity checking purposes.

Clearly, having columns that increase so significantly due to encryption will also have an impact on any associated indexes as they will likewise not be able to contain as many entries per index block and hence be significantly larger.

However, the more pressing issue is that by adding salt to the encryption process, there is no easy deterministic way Oracle can associate an actual indexed value with the encrypted value when accessing and modifying the index. As a result, Oracle simply doesn’t allow an index to be created on any column that has been encrypted with salt.

SQL> create index bowie_id_i on bowie(id);
create index bowie_id_i on bowie(id)
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt

SQL> alter table bowie add primary key(id);
alter table bowie add primary key(id)
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt

If we want to encrypt a column and have the column indexed, we must encrypt the column without salt. Additionally, if you want to make the index more efficient without the overheads associated with MAC integrity checks, you may also want to encrypt the columns with the NOMAC option.

Let’s re-create the table without these encryption defaults:

SQL> drop table bowie;

Table dropped.

SQL> create table bowie (id number encrypt 'nomac' no salt, code number encrypt 'nomac' no salt, salary number, text varchar2(30)) tablespace unsafe;

Table created.

SQL> insert into bowie select rownum, 42, rownum, 'BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

If we look at a block dump of this table:

Block header dump:  0x020000e4
Object id on Block? Y
seg/obj: 0x16cf0  csc: 0x00.2e75a9  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x20000e0 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.00a.00000872  0x0140b74e.00f0.09  –U-  148  fsc 0x0000.002e75bb
0x02   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
bdba: 0x020000e4
data_block_dump,data header at 0x1dc2e64
===============
tsiz: 0x1f98
hsiz: 0x13a
pbl: 0x01dc2e64
76543210
flag=——–
ntab=1
nrow=148
frre=-1
fsbo=0x13a
fseo=0x46d
avsp=0x333
tosp=0x333
0xe:pti[0] nrow=148 offs=0

…

We notice the number of rows in the table has somewhat bounced back up to 148 rows.

If we look at some of the rows:

block_row_dump:
tab 0, row 0, @0x1b01
tl: 47 fb: –H-FL– lb: 0x1  cc: 4
col  0: [16]  de 59 69 48 5e 65 d8 21 da 49 bd ba 19 d5 1f 80
col  1: [16]  9e d8 3b 95 65 60 43 df 2c e2 b0 85 ae 5e 87 61
col  2: [ 3]  c2 02 34
col  3: [ 5]  42 4f 57 49 45
tab 0, row 1, @0x1b30
tl: 47 fb: –H-FL– lb: 0x1  cc: 4
col  0: [16]  9e 96 cc e1 57 07 74 61 64 19 99 4a ad 08 d9 93
col  1: [16]  9e d8 3b 95 65 60 43 df 2c e2 b0 85 ae 5e 87 61
col  2: [ 3]  c2 02 35
col  3: [ 5]  42 4f 57 49 45
tab 0, row 2, @0x1b5f
tl: 47 fb: –H-FL– lb: 0x1  cc: 4
col  0: [16]  f8 cf 58 ba f2 1b f7 a6 2d 59 1c c7 2c e4 28 86
col  1: [16]  9e d8 3b 95 65 60 43 df 2c e2 b0 85 ae 5e 87 61
col  2: [ 3]  c2 02 36
col  3: [ 5]  42 4f 57 49 45

We see the length of the encrypted columns has dropped back down to 16 bytes, still more than the unencrypted columns but less than the 52 bytes required for the encrypted columns with both salt and MAC enabled.

Note though that the CODE column (col 1) while encrypted all now have the same encrypted hex value (9e d8 3b 95 65 60 43 df 2c e2 b0 85 ae 5e 87 61) without the salt applied. So the encrypted data is that little bit less secure but we can now successfully create B-tree indexes on these encrypted columns:

SQL> create index bowie_id_i on bowie(id);

Index created.

This however doesn’t end the various restrictions associated with indexing column encrypted columns as we’ll see in the next post.