Using Fortanix Data Security Manager with Oracle TDE

1.0 Introduction

Transparent Data Encryption (TDE) enables you to encrypt sensitive data stored in tables and tablespaces.

After the data is encrypted, it is transparently decrypted for authorized users or applications when they access it. TDE helps protect data stored on media (also called data at rest) if the storage media or data file is stolen.

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. Oracle Database provides Transparent Data Encryption (TDE) to protect these data files. TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore.

This article describes how to integrateFortanix-Data-Security-Manager (DSM) with Oracle Transparent Data Encryption (TDE). It also contains the information that a user needs to:

• Generate a TDE master encryption key in Fortanix DSM

• Encrypt a tablespace or columns in a table

• Configure auto-login hardware security module (HSM)

2.0 Terminology References

Fortanix Data Security Manager

Fortanix DSM is the cloud solution secured with Intel® SGX. With Fortanix DSM, you can securely generate, store, and use cryptographic keys and certificates, as well as secrets, such as passwords, API keys, tokens, or any blob of data.

TDE – Transparent Data Encryption

Transparent Data Encryption (TDE) enables you to encrypt sensitive data that you store in tables and tablespaces. Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore. For more information, see Introduction to Transparent Data Encryption.

TDE Table Key

TDE Table key is an encryption key that is associated with a table whose columns are marked for encryption. The TDE master encryption key encrypts this table encryption key.

Tablespace Encryption Key

A Tablespace Encryption key is an encryption key for the encryption of a tablespace. The TDE master encryption key encrypts the tablespace encryption key, which in turn encrypts and decrypts data in the tablespace.

HSM – Hardware Security Module

A Security Module is an external keystore or a separate server or device that provides secure storage for encryption keys. External keystores are external to an Oracle database. Oracle Database can interface with external keystores but cannot manipulate them outside of the Oracle interface. The Oracle database can request the external keystore to create a key, but it cannot define how this key is stored in an external database.

3.0 Configuring Oracle Database for Integration

1. Ensure the prerequisites are met.

2. Configure Fortanix DSM for TDE as described here.

3. Fortanix provides PKCS#11 library (.so file) as a connector for the Oracle database integration which can be downloaded from here.

4. Copy the downloaded Fortanix library file to the following directory structure.
   /opt/oracle/extapi/64/hsm/fortanix/<pkcs_lib_version>
   For example, if your library version is 4.34.2503, then run the following commands to create the directory and copy the downloaded library file.

   sudo mkdir -p /opt/oracle/extapi/64/hsm/fortanix/4.34.2503

   Copy the Fortanix library file to the location above, and name it as libpkcs11.so.

   cp fortanix_pkcs11_4.34.2503.so   
   /opt/oracle/extapi/64/hsm/fortanix/4.34.2503/libpkcs11.so

   Set the permission and ownership of the folder.

   sudo chown -R oracle:oinstall /opt/oracle
   sudo chmod -R 775 /opt/oracle

5. Create the directory for pkcs11.conf file. The default location should be /etc/fortanix.

   sudo mkdir -p /etc/fortanix
   sudo chown -R oracle:oinstall /etc/fortanix
   sudo chmod -R 775 /opt/oracle

   If the requirement is to use a custom location, then set the environment variable FORTANIX_PKCS11_CONFIG_PATH for the path of the custom location.

   For example:

   export FORTANIX_PKCS11_CONFIG_PATH=/u01/app/oracle/fortanix/pkcs11.conf

6. Create the configuration file pkcs11.conf under /etc/fortanix folder with the following parameters:

   api_endpoint = "https:// <fortanix_dsm_url>"
   app_id = "95XXX8-XXXX0-4442-XXXX5-XXXX9c"
   prevent_duplicate_opaque_objects = true
   retry_timeout_millis = 60000
   [log]
   file = "<log filename>"

   Where,

   • api_endpoint is the URL endpoint of the Fortanix DSM installation.

   • app_id is the app UUID you will get in the DSM user interface (UI), post-creation of the app.

   • prevent_duplicate_opaque_objects = true, to prevent creating duplicate opaque objects.

   • retry_timeout_millis can be set in milliseconds, which allows for retries in case of failures from the service side. By default, this is set to 3 seconds.

   • file is an optional log file location that can be set. By default, logging is done in /var/log/syslog. Ensure the Oracle instance owner has access to the log file location.

7. If Fortanix DSM is installed in an on-premises environment, the CA certificate used for the HTTPS endpoint needs to be provided in PKCS#11 configuration. If not set, the TLS communication to Fortanix DSM will not be successful.

   1. Download the CA certificate as a PEM file on the Oracle Database machine. The file name can be sdkms-ca.crt. Make sure the full certificate chain is part of the file.

   2. Add the location of the CA file in the PKCS#11 conf file as follows:

   api_endpoint = "https:// <fortanix_dsm_url>"
   app_id = "95XXX8-XXXX0-4442-XXXX5-XXXX9c"
   prevent_duplicate_opaque_objects = true
   retry_timeout_millis = 60000
   ca_certs_file = "</path/to/sdkms-ca.pem>"
   [log]
   file = "<log filename>"

NOTE

• In case of Oracle RAC Database environment, repeat the Steps 4 to 6 above on all the nodes.

• If you experience any connection issues between Oracle and Fortanix DSM, refer to PKCS#11 Library: Connection Issues for troubleshooting.

4.0 Integration Steps

4.1 Oracle Database Version 19c and Above

4.1.1 Configure HSM Key Store

1. Run the following command to create the directory structure for wallet files:

   mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde

2. Set the Oracle wallet location. Even though HSM Wallet does not reside on a local disk, this step is required to set TDE.

   ALTER SYSTEM SET WALLET_ROOT="$ORACLE_BASE/admin/$ORACLE_SID/wallet" scope=spfile; 

   In the case of the RAC database use the following:

   ALTER SYSTEM SET WALLET_ROOT="$ORACLE_BASE/admin/$ORACLE_SID/wallet" scope=spfile sid=’*’; 

   Where $ORACLE_BASE/admin/$ORACLE_SID/wallet is the wallet location and it must be created before running the command. ORACLE_BASE and ORACLE_SID environment variables will be translated based on the database environment variables.

   NOTE

   WALLET_ROOT is the database static parameter which requires instance restart.
   Use the following comand to restart the instance:

   shutdown immediate
   startup 

   In case of the RAC database, create the directory structure on all RAC nodes and restart DB instances in rolling fashion.

3. Set the TDE_CONFIGURATION wallet type to HSM.

   ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=HSM" scope=both;

   In the case of the RAC database use the following:

   ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=HSM" scope=both sid=’*’;

4.1.2 Open HSM Keystore

1. Log in with the User sys with role sysdba.

sqlplus / as sysdba

To open the hardware Keystore, run the following command:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<DSM_APP_PASSWORD>" CONTAINER = ALL;

In the specification above,

• DSM_APP_PASSWORD is the app password that you created in DSM UI.

  To get the app password in DSM UI,

  1. Navigate to Apps.

  2. Select the created app.

  3. Click VIEW API KEY DETAILS.

  4. Click USERNAME/PASSWORD.

  5. Copy the Password.

• CONTAINER is for use in a multitenant environment. Enter ALL to set the Keystore in all the pluggable databases (PDBs) in this container database (CDB), or CURRENT for the current PDB.

4.1.3 Configure TDE Master Key

Next, you must create a TDE master encryption key that is stored inside the Fortanix hardware keystore. Oracle Database uses the TDE master encryption key to encrypt or decrypt TDE table keys or tablespace encryption keys inside the hardware security module.

NOTE

Ensure that the above step is executed on only one node in the RAC cluster environment.

1. Run the following command: The command sets the Oracle database for TDE.

   ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<DSM_APP_PASSWORD>" CONTAINER = ALL;

   NOTE

   If you encounter “ORA-46665: master keys not activated for all PDBs during REKEY” error, then for each pluggable database, master key must be created separately using the following command:

   alter session set container=<PDB_NAME>;
   ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<DSM_APP_PASSWORD>";

2. Open the Fortanix DSM UI and go to the audit log to confirm if the master key was generated and used.

3. Click the Settings tab, and then in the left panel click the LOG MANAGEMENT tab to see the audit logs.  

   

   The Fortanix DSM is now successfully integrated with the Oracle TDE.

5.0 Configuring Auto-Login

Auto Login configuration allows wallets to open automatically in the case of database restart.

Auto-Login will store the Fortanix DSM App’s secret/password in an auto-login keystore using a software keystore and the App ID (App UUID) will be stored in a config file. The Oracle TDE will pass the app secret stored in the auto-login keystore as PIN to the Fortanix DSM PKCS11 library. Fortanix DSM KMS PKCS11 library will use this PIN and combine it with the App ID in the config file to create the basic authentication token for authenticating to Fortanix DSM.

For additional details on Oracle TDE and auto-login keystore, please see: https://docs.oracle.com/database/121/ASOAG/managing-keystore-and-tde-master-encryption-key.htm#ASOAG10434

5.1 Auto-Login in Oracle 19c and Above

1. Check if the HSM key store is open. You can check the status of whether a keystore is open or closed by querying the STATUS column of the V$ENCRYPTION_WALLET view.

   Select * from V$ENCRYPTION_WALLET; 

2. Close the HSM keystore if it is open.

   ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "<DSM_APP_PASSWORD>" CONTAINER = ALL; 

3. Change the keystore type to a software wallet.

   ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE";

4. Create a software keystore. This keystore will store the HSM wallet password to enable auto-login functionality.

   ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde' IDENTIFIED BY "<DSM_APP_PASSWORD>";

5. Open the software keystore.

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<DSM_APP_PASSWORD>" CONTAINER=ALL;

6. Add Fortanix DSM App password as a secret in the software wallet using the client as HSM_PASSWORD. This is an Oracle-defined client name that is used to represent the HSM password as a secret in the software keystore. The app password can be fetched as per Section 4.1.2: Open HSM Keystore.

   ADMINISTER KEY MANAGEMENT ADD SECRET '<DSM_APP_PASSWORD>' FOR CLIENT 'HSM_PASSWORD' IDENTIFIED BY "<DSM_APP_PASSWORD>" WITH BACKUP;

7. Close the software keystore.

   ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "<DSM_APP_PASSWORD>";
   

8. Now create the auto-login keystore.

   ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '$ORACLE_BASE/admin/$ORACLE_SID/wallet/tde' IDENTIFIED BY "<DSM_APP_PASSWORD>";

9. Set the wallet location to HSM backed by auto-login.

   ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=HSM|FILE";

   For the RAC database, run the following command:

   ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=HSM|FILE"
   Sid=’*’;

10. Shut down and restart the database to verify autologin is functioning properly.

11. Run the following command to verify that the wallet is auto-open.

    SELECT * FROM V$ENCRYPTION_WALLET;

    This completes the auto-login steps for Oracle 19c and above.

    NOTE

    In the case of RAC database, transfer the ewallet.p12 and cwallet.sso files to all nodes of RAC at specified location $ORACLE_BASE/admin/$ORACLE_SID/wallet/tde.

6.0 Encrypt Your Data

Once the TDE is set up on the Oracle Database, we can proceed with data encryption. There are 2 ways of encrypting data:

1. Column Encryption: Selected columns of a table to be encrypted.

2. Tablespace Encryption: Encrypt the entire tablespace using TDE. This is recommended by Oracle due to its better performance.

6.1 Column Encryption

1. Connect to SQL*plus as a non-sysadmin user to enable encryption on a table.

2. Next, create a table with an encrypted column. Use the CREATE TABLE SQL statement with the ENCRYPT clause.

   CREATE TABLE employee (first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER, salary NUMBER(6) ENCRYPT);

3. Now insert some data into the table.

   INSERT INTO employee VALUES ('JOHN', 'SMITH',001, 10000);

4. To list the encrypted columns in your database, run the following command:

   select * from dba_encrypted_columns;
   
   OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SALT INTEGRITY_ALG
   ------- ---------- ----------- -------------- ---- -------------
   OE EMPLOYEE SALARY AES 192 bits key YES SHA-1

More details can be found here https://docs.oracle.com/database/121/TDPSG/GUID-61259237-5514-4531-AFB4-CF716F93F1E5.htm#TDPSG44324.

6.2 Tablespace Encryption

1. Connect to SQL*plus as a non-sysadmin user to create a new encrypted tablespace.

2. Make sure that COMPATIBLE initialization parameter setting is 11.2.0.0 or higher. This is typically an issue with older 11g databases.

   SHOW PARAMETER COMPATIBLE
   NAME                        TYPE                VALUE
   compatible                  string              11.2.0.0
   noncdbcompatible            BOOLEAN             FALSE
   

   
   # If above is less than 11.2.0.0, then run following
   alter system set COMPATIBLE='11.2.0.0' scope=spfile;
   shutdown
   startup

3. Create a new encrypted tablespace using CREATE TABLESPACE SQL statement with the ENCRYPT clause.

   CREATE TABLESPACE encrypted_ts
   DATAFILE '<PATH_TO_DATAFILE>/encrypted_ts01.dbf' SIZE 128K
   AUTOEXTEND ON NEXT 64K
   ENCRYPTION USING 'AES256'
   DEFAULT STORAGE(ENCRYPT);
   
   ALTER USER test QUOTA UNLIMITED ON encrypted_ts;
   

4. Now insert some data into the table or tablespace.

   CREATE TABLE ets_test (
     id    NUMBER(10),
     data  VARCHAR2(50)
   ) TABLESPACE encrypted_ts;
   INSERT INTO ets_test (id, data) VALUES (1, 'This is a secret!');
   COMMIT;

5. The ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES views indicate if the tablespace is encrypted or not.

   SELECT tablespace_name, encrypted FROM dba_tablespaces;
   TABLESPACE NAME             ENC
   ____________________________________________________________________ 
   SYSTEM                      NO
   SYSAUX                      NO
   ENCRYPTED_TS                YES
   
   3 rows selected

7.0 Rotate Master Key

Use the following commands to rotate a master key with/without an auto-login wallet using a container and non-container database.

7.1 Without Using Auto-Login Wallet

Using container database:

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<DSM_APP_PASSWORD>" CONTAINER = ALL;

Using a non-container database:

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "<DSM_APP_PASSWORD>";

7.2 Using Auto-Login Wallet

Using container database:

ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "<DSM_APP_PASSWORD>" CONTAINER = ALL;

Using a non-container database:

ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "<DSM_APP_PASSWORD>";

8.0 Enable Prefetching Responses of Upcoming Heartbeat Requests Locally (Optional)

To ensure connection to the Fortanix DSM, the Oracle Database makes repeated requests for encrypting dummy data. These requests are called heartbeats. By default, the heartbeat is created every 3 seconds, and if the database server does not receive a response within the time limit, the wallet closes. However, a delay of 3 seconds can happen even due to factors like minor outages or network latency.

So, in cases of connectivity loss, the results for the upcoming heartbeats are prefetched and used. Note that this is just used to delay the wallet closure. If the connectivity is not restored before the prefetched values are used up, the wallet will still close but only after a delay.

If you are expecting a network latency in the communication channel between Fortanix DSM and Oracle Database Server, you can (optionally) enable the prefetching of heartbeats functionality by specifying the following in the PKCS#11 configuration file:

api_endpoint = "https:// <fortanix_dsm_url>"
app_id = "95XXX8-XXXX0-4442-XXXX5-XXXX9c"
prevent_duplicate_opaque_objects = true
retry_timeout_millis = 60000

[oracle_tde_cache_config] #enabling prefetching of heartbeats
cache_size = 100
request_timeout = 2000
[log]
file = "<log filename>"

For more information on enabling prefetching of heartbeats, refer to Clients:PKCS#11 Library .