Transparent Data Encryption (TDE) in Oracle Databases allows you to protect sensitive data in tables by encrypting them when they are stored on media. The data is transparently decrypted for authorized users or applications when they access the data. See Introduction to Transparent Data Encryption for more information.
This article describes how to integrate SmartKey to be used with Oracle TDE.
Before starting, download the SmartKey PKCS#11 library from here.
The following steps have been verified with the Oracle Database version 12c release 2 running on CentOS 7.2.
- Add the following line to the
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD=HSM))
Copy SmartKey PKCS#11 library to folder
/opt/oracle/extapi/64/hsm/fortanix/0.8.0. Change the name of the library file to
Set permission and ownership for the folder that contains the SmartKey PKCS#11 library
[oracle@localhost ~]$ sudo chown -R oracle:oinstall /opt/oracle [oracle@localhost ~]$ sudo chmod -R 775 /opt/oracle
- Add an application to SmartKey for Oracle TDE. See Getting Started. Copy the API key
for the application and write it to a file along with the API endpoint. This file can also be used to specify the location
of a file that the PKCS#11 library can write logs to. Note the absolute path of this file, you will need it later. For
api_endpoint = "https://www.smartkey.io" api_key = "MWY5YT...TO5n" [log] file = "<log filename>"
sqlplusand then login with user
syswith role of
[oracle@localhost ~]$ sqlplus SQL\*Plus: Release 220.127.116.11.0 Production on Fri Dec 1 00:47:19 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> connect Enter user-name: sys as sysdba Enter password: Connected.
- Switch to root container. Keystore needs to be opened in the root container first. Run the following command in sqlplus:
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
- Run the following command to open the hardware key store
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "file:///home/oracle/api_key_file" CONTAINER = ALL;
- After you have opened the hardware keystore, run the following command to set the TDE master encryption key.
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "file:///home/oracle/api_key_file" CONTAINER = ALL;
- Connect to sql as a non-sysadmin user to enable encryption on a table column or a tablespace.
[oracle@localhost ~]$ sqlplus SQL\*Plus: Release 18.104.22.168.0 Production on Fri Dec 1 01:08:32 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter user-name: user Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 22.214.171.124.0 - 64bit Production
- Create a table with an encrypted column.
SQL> CREATE TABLE employee (first_name VARCHAR2(128),last_name VARCHAR2(128),empID NUMBER,salary NUMBER(6) ENCRYPT);
- Insert some data into the table
SQL> INSERT INTO employee VALUES ('JOHN', 'SMITH',001, 10000);
At this point, look at the audit log for the master key in the SmartKey web UI, and see that the master key was used.
- To list the encrypted columns in your database, run the following command:
SQL> select * from dba_encrypted_columns;