14 January, 2021

Configuring Transparent Data Encryption -- 2 : For Columns

The previous demo of TDE in 19c was for a full Tablespace (converting an existing, non-TDE, Tablespace to an Encrypted Tablespace).

Pre-creating a Table with an Encrypted column would be straightforward : 

CREATE TABLE employees (
 emp_id number primary key,
 first_name varchar2(128),
 last_name varchar2(128),
 national_id_no  varchar2(18) encrypt,
 salary number(6) )
tablespace hr_data
/


This encrypts the column with the AES encryption algorithm with a 192-bit key length ("AES192").

But what if you want to encrypt an existing, non-encrypted column ? You can use the MODIFY clause. 

ALTER TABLE employees (
 MODIFY (national_id_no encrypt)
/


A quick demo : 

SQL> create tablespace hr_data datafile '/opt/oracle/oradata/HEMANT/HR_DATA.dbf' size 5M;

Tablespace created.

SQL> CREATE TABLE employees (
  2  emp_id number primary key,
  3  first_name varchar2(128),
  4  last_name varchar2(128),
  5  national_id_no  varchar2(18),
  6  salary number(6) )
  7  tablespace hr_data;

Table created.

SQL> ^C

SQL> insert into employees
  2  select rownum, 'Hemant', 'Hemant' || to_char(rownum), dbms_random.string('X',12), 1000
  3  from dual
  4  connect by level "less than" 21    --- "less than" symbol replaced by string to preserve HTML formatting
  5  /

20 rows created.

SQL> commit;

Commit complete.

SQL> alter system  checkpoint;

System altered.

SQL> !sync ; sync

SQL>
SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more
}|{z
HEMANT
3J?5
HR_DATA
H4J?
AAAAAAAA
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA

SQL> alter table employees
  2  MODIFY (national_id_no encrypt)
  3  /

Table altered.

SQL> alter system checkpoint;

System altered.

SQL> !sync ; sync

SQL>

SQL> select emp_id, national_id_no
  2  from employees
  3  order by 1
  4  /

    EMP_ID NATIONAL_ID_NO
---------- ------------------
         1 LH6RUZRISE11
         2 DFIN8FZ7B6J0
         3 PLJ1R2QYRG2C
         4 UT3HB9ALF3B5
         5 LQMDUTFB2PTM
         6 1IGKV4E78M5J
         7 P9TQAV5BC5EM
         8 V69U6VZWCK26
         9 EOTOQHOB0F45
        10 OKMEV89XOQE1
        11 0D4L77P3YNF0
        12 CTMCLJSKQW82
        13 49T0AG7E2Y9X
        14 ODEY2J51D8RH
        15 R1HFMN34MYLH
        16 OXI0LOX161BO
        17 2XL44ZJVABGW
        18 4BIPWVECBWYO
        19 732KA25TZ3KR
        20 NN0X92ES90PH

20 rows selected.

SQL>
SQL> select emp_id, dump(national_id_no) col_dump
  2  from employees
  3  order by emp_id
  4  /

    EMP_ID COL_DUMP
---------- ------------------------------------------------------
         1 Typ=1 Len=12: 76,72,54,82,85,90,82,73,83,69,49,49
         2 Typ=1 Len=12: 68,70,73,78,56,70,90,55,66,54,74,48
         3 Typ=1 Len=12: 80,76,74,49,82,50,81,89,82,71,50,67
         4 Typ=1 Len=12: 85,84,51,72,66,57,65,76,70,51,66,53
         5 Typ=1 Len=12: 76,81,77,68,85,84,70,66,50,80,84,77
         6 Typ=1 Len=12: 49,73,71,75,86,52,69,55,56,77,53,74
         7 Typ=1 Len=12: 80,57,84,81,65,86,53,66,67,53,69,77
         8 Typ=1 Len=12: 86,54,57,85,54,86,90,87,67,75,50,54
         9 Typ=1 Len=12: 69,79,84,79,81,72,79,66,48,70,52,53
        10 Typ=1 Len=12: 79,75,77,69,86,56,57,88,79,81,69,49
        11 Typ=1 Len=12: 48,68,52,76,55,55,80,51,89,78,70,48
        12 Typ=1 Len=12: 67,84,77,67,76,74,83,75,81,87,56,50
        13 Typ=1 Len=12: 52,57,84,48,65,71,55,69,50,89,57,88
        14 Typ=1 Len=12: 79,68,69,89,50,74,53,49,68,56,82,72
        15 Typ=1 Len=12: 82,49,72,70,77,78,51,52,77,89,76,72
        16 Typ=1 Len=12: 79,88,73,48,76,79,88,49,54,49,66,79
        17 Typ=1 Len=12: 50,88,76,52,52,90,74,86,65,66,71,87
        18 Typ=1 Len=12: 52,66,73,80,87,86,69,67,66,87,89,79
        19 Typ=1 Len=12: 55,51,50,75,65,50,53,84,90,51,75,82
        20 Typ=1 Len=12: 78,78,48,88,57,50,69,83,57,48,80,72

20 rows selected.

SQL>

SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more
}|{z
HEMANT
3J?5
HR_DATA
AAAAAAAA
Hemant
Hemant204
Hemant
Hemant194
Hemant
Hemant184
Hemant
Hemant174[Q#
Hemant
Hemant164
Hemant
Hemant154
$^?[
Hemant
Hemant1448
Hemant
Hemant134
Hemant
Hemant124
Hemant
Hemant114
Hemant
Hemant104J
Hemant
Hemant94
Hemant
Hemant84M
zCAGp
Q(ru
Hemant
Hemant74
$o7tN
Hemant
Hemant6418
(       i+W
Hemant
Hemant54
f(cCL
Hemant
Hemant44
Hemant
Hemant34
Hemant
Hemant24
e{_
Hemant
Hemant14
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA


SQL> select version, version_full from v$instance;

VERSION           VERSION_FULL
----------------- -----------------
19.0.0.0.0        19.3.0.0.0

SQL>


When I insert a new row, the plain-text for this is not present.  But the old (20) rows plain-text is still present.

SQL> insert into employees
  2  values (21,'HemantNew','HemantNew21','ABCDEFGHIJ88',2000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> !sync;sync

SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf
}|{z
HEMANT
3J?5
SJ?
HR_DATA
UTJ?
AAAAAAAA
        HemantNew
HemantNew214S
Hemant
Hemant204
Hemant
Hemant194
Hemant
Hemant184
Hemant
Hemant174[Q#
Hemant
Hemant164
Hemant
Hemant154
$^?[
Hemant
Hemant1448
Hemant
Hemant134
Hemant
Hemant124
Hemant
Hemant114
Hemant
Hemant104J
Hemant
Hemant94
Hemant
Hemant84M
zCAGp
Q(ru
Hemant
Hemant74
$o7tN
Hemant
Hemant6418
(       i+W
Hemant
Hemant54
f(cCL
Hemant
Hemant44
Hemant
Hemant34
Hemant
Hemant24
e{_
Hemant
Hemant14
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA

SQL>

So, it seems that after I ran the MODIFY to encrypt a column, Oracle created new copies of the 20 rows with encrypted values.  However, the old plain-text (non-encrypted) values are still present in the datafile.

Apparently, those "still present" plain-text representations of the "NATIONAL_ID_NO" column in the datafile are explained in the documentation as :

"Column values encrypted using TDE are stored in the data files in encrypted form. However, these data files may still contain some plaintext fragments, called ghost copies, left over by past data operations on the table. This is similar to finding data on the disk after a file was deleted by the operating system."

You should remove old plaintext fragments that can appear over time.

Old plaintext fragments may be present for some time until the database overwrites the blocks containing such values. If privileged operating system users bypass the access controls of the database, then they might be able to directly access these values in the data file holding the tablespace.

To minimize this risk:

  1. Create a new tablespace in a new data file.

    You can use the CREATE TABLESPACE statement to create this tablespace.

  2. Move the table containing encrypted columns to the new tablespace. You can use the ALTER TABLE.....MOVE statement.

    Repeat this step for all of the objects in the original tablespace.

  3. Drop the original tablespace.

    You can use the DROP TABLESPACE tablespace INCLUDING CONTENTS KEEP DATAFILES statement. Oracle recommends that you securely delete data files using platform-specific utilities.

  4. Use platform-specific and file system-specific utilities to securely delete the old data file. Examples of such utilities include shred (on Linux) and sdelete (on Windows).

No comments:

Post a Comment