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;

SQL> SELECT RPAD(tablespace_name,18)||’ ‘||RTRIM(file_name) Tablespace FROM dba_data_files ORDER BY tablespace_name;

TABLESPACE
——————————————————————————–
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
USERS /u01/app/oracle/oradata/orcl/users01.dbf

SQL> CREATE TABLESPACE TEST
               DATAFILE ‘/u01/app/oracle/oradata/orcl/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;

SQL> CONN test/test@orcl

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;HighlightColumn:DATA‘);

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” /u01/app/oracle/oradata/orcl/test01.dbf
             Binary file /u01/app/oracle/oradata/orcl/test01.dbf matches

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 ———————

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

SQL> test/test@orcl

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;IncludeMasterColumn:false;ExtraCols:data;HighlightColumn:DATA’);

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 sscore(1),shighlight(1) from tde_test where scontains(id,’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” /u01/app/oracle/oradata/orcl/test01.dbf

SQL> !grep “secret” /u01/app/oracle/oradata/orcl/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.