Test Transparent Data Encryption on Scotas’s OLS

This example shows how Scotas OLS helps you to protect your data. Following this guide any sensitive data will never be exposed on Solr/Lucene and your application is complaint against Health Insurance Portability and Accountability Act (HIPAA) and Payment Card Industry (PCI) data security standards

• First enable TDE on Oracle database (Wallet config should be enabled first) connected as SYS

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY test;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY test;

• Check data files location to create a new small one

SQL> SELECT RPAD(tablespace_name,18)||’ ‘||RTRIM(file_name) Tablespace FROM dba_data_files ORDER BY tablespace_name;
TABLESPACE
——————————————————————————–
SYSAUX             /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
SYSTEM             /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
UNDOTBS1           /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
USERS              /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
SQL> CREATE TABLESPACE TEST
               DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/test01.dbf' SIZE 128K
               AUTOEXTEND ON NEXT 64K;
SQL> CREATE USER test identified by test
               temporary tablespace temp
               default tablespace test
               quota unlimited on test;
SQL> GRANT connect,resource,luceneuser TO test;

• Connect and test a non-secure storage by checking some text stored unencrypted on the datafile.

SQL> CONN test/test@orclpdb1
SQL> CREATE TABLE tde_test (
               id NUMBER(10),
               data VARCHAR2(50)
               )
              TABLESPACE test;
SQL> INSERT INTO tde_test (id, data) VALUES (1, 'This is a secret!');
SQL> commit;
SQL> CREATE index tde_idx on tde_test(id)
               indextype is Lucene.SolrIndex
               parameters('LogLevel:INFO;IncludeMasterColumn:false;ExtraCols:data "title";HighlightColumn:title');
SQL> SELECT count(*) from tde_test where scontains(id,'secret')>0;
     COUNT(*)
     ————
        1

• Table and index was created, we can connect as SYS and flush any memory structure to disk and verify the string using grep utility.

SQL> conn / as sysdba
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
              System altered.
SQL> !grep "This is a secret" /opt/oracle/oradata/ORCLCDB/ORCLPDB1/test01.dbf
             Binary file /opt/oracle/oradata/ORCLCDB/ORCLPDB1/test01.dbf matches

• Drop the user and datafile and start again the test

SQL> ALTER SYSTEM SET WALLET CLOSE IDENTIFIED BY test;
SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY test;
SQL> drop user test cascade;
SQL> DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

recreate here the tablespace test and the user

SQL> ALTER SYSTEM SET WALLET CLOSE IDENTIFIED BY test;
SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY test;
SQL> drop user test cascade;
SQL> DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

• Connect again using the new user but now using TDE on the column and the index.

SQL> test/test@orclpdb1
SQL> CREATE TABLE tde_test (
               id NUMBER(10),
               data VARCHAR2(50) ENCRYPT
               )
              TABLESPACE test;
SQL> INSERT INTO tde_test (id, data) VALUES (1, 'This is a secret!');
SQL> COMMIT;
SQL> SELECT * FROM tde_test;
ID  DATA
——— ————————————————–
1 This is a secret!
SQL> create index tde_idx on tde_test(id)
               indextype is Lucene.SolrIndex
               parameters('PopulateIndex:false;LogLevel:INFO;IncludeMasterColumn:false;ExtraCols:data "title";HighlightColumn:title');
SQL> select count(*) from tde_test where scontains(id,'secret')>0;
COUNT(*)
———
0

• PopulateIndex:false was used to not fill the Solr/Lucene structured until the encryption is enabled on the LOB column which stored Lucene files.

SQL> alter table tde_idx$t modify lob(data) (encrypt using '3des168');
SQL> alter index tde_idx rebuild parameters('LogLevel:INFO');
SQL> select count(*) from tde_test where scontains(id,'secret')>0;
COUNT(*)
———
1
SQL> select /*+ DOMAIN_INDEX_SORT */ sscore(1),shighlight(1) from tde_test where scontains(id,'title:secret','score desc',1)>0;
SSCORE(1)  SHIGHLIGHT(1)
———        —————————-—————————————————-
.306852818 This is a <em>secret</em>!

• Now connect again and verify that sensitive text are not visible on the table information and the index structures

SQL> conn / as sysdba
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

• Now grep utility can’t find any matches on the complete text or part of them (keywords)

SQL> !grep "This is a secret" /opt/oracle/oradata/ORCLCDB/ORCLPDB1/test01.dbf
SQL> !grep "secret" /opt/oracle/oradata/ORCLCDB/ORCLPDB1/test01.dbf

• Conclusion: All sensitive data will never be exposed on Solr/Lucene and your application is complaint against Health Insurance Portability and Accountability Act (HIPAA) and Payment Card Industry (PCI) data security standards.