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/
SYSAUX /u01/app/oracle/oradata/orcl/
SYSTEM /u01/app/oracle/oradata/orcl/
UNDOTBS1 /u01/app/oracle/oradata/orcl/
USERS /u01/app/oracle/oradata/orcl/
SQL> CREATE TABLESPACE TEST
DATAFILE ‘/u01/app/oracle/oradata/orcl/
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;
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/
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:
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.