Using SmartKey with Oracle TDE

Updated: Nov 30, 2017

Overview

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.

Configuration

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 sqlnet.ora file:
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 libpkcs11.so.

  • 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 example, file api_key_file could have:
api_endpoint = "https://www.smartkey.io"
api_key = "MWY5YT...TO5n"
[log]
file = "<log filename>"
  • Run sqlplus and then login with user sys with role of sysdba
[oracle@localhost ~]$ sqlplus

SQL\*Plus: Release 12.2.0.1.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 12.2.0.1.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 12.2.0.1.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;