1.0 Introduction
This article describes how to integrate Fortanix 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
- Ensure the prerequisites are met.
- Configure Fortanix DSM for TDE as described here.
- Oracle Database requires PKCS#11 protocol implemented library of the HSM. Copy the downloaded Fortanix library file to the following directory structure.
/opt/oracle/extapi/{32,64}/hsm/<vendor_name>/<pkcs_lib_version>
For example, if your lib version is 3.16.1311, then run the following commands to create the directory and copy the downloaded library file.sudo mkdir -p /opt/oracle/extapi/64/hsm/fortanix/3.16.1311
libpkcs11.so
.cp fortanix_pkcs11_3.16.1311.so /opt/oracle/extapi/64/hsm/fortanix/3.16.1311/ libpkcs11.so
sudo chown -R oracle:oinstall /opt/oracle sudo chmod -R 775 /opt/oracle
- Create the configuration file pkcs11.conf for connecting to Fortanix DSM.
The default location is/etc/fortanix
.
If the requirement is to use a custom location, then set the environment variableFORTANIX_PKCS11_CONFIG_PATH
for the path of the custom location. For example:export FORTANIX_PKCS11_CONFIG_PATH=/u01/app/oracle/fortanix/pkcs11.conf
pkcs11.conf
in the desired folder with the following parameters:api_endpoint = "https://<fortanix_dsm_url>" api_key = "MWY5YT...TO5n" prevent_duplicate_opaque_objects = true retry_timeout_millis = 60000 [log] file = "<log filename>"
api_endpoint
is URL endpoint of the Fortanix DSM installation.api_key
is the API Key you copied to notepad after creating 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.
- 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.
- 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. - Add the location of the CA file in PKCS#11 conf file as follows:
api_endpoint = "https://<fortanix_dsm_url>" api_key = "MWY5YT...TO5n" prevent_duplicate_opaque_objects = true retry_timeout_millis = 60000 ca_certs_file = "/path/to/sdkms-ca.pem" [log] file = "<log filename>"
- Download the CA certificate as a PEM file on the Oracle Database machine. The file name can be
4.0 Integration Steps
4.1 Oracle Database Version 19c
4.1.1 Set Hardware Keystore Type
- In Oracle 19c, it is not required to edit the
sqlnet.ora
file. The setup of the keystore type can be done using Oracle initialization parameters. - 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="/opt/oracle/admin/ORCLCDB/wallet" scope=spfile;
$ORACLE_BASE/admin/$ORACLE_SID/wallet
is the wallet location. - Shut down the system.
SQL> shutdown
- Startup the system
SQL> startup
- Set the wallet type to HSM.
SQL> ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=HSM" scope=both;
4.1.2 Set up Keystore
Log in with User sys
with role sysdba
.
sqlplus / as sysdba
To open the hardware key store, run the following command:
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "file:///etc/fortanix/pkcs11.conf" CONTAINER = ALL;
In the specification above,
IDENTIFIED BY
points to the location of the PKCS#11 Configuration file prefixed withfile://
.CONTAINER
is for use in a multitenant environment. EnterALL
to set the keystore in all the pluggable databases (PDBs) in this container database (CDB), orCURRENT
for the current PDB.
4.1.3 Set 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
- Run the following command:
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "file:///etc/fortanix/pkcs11.conf" CONTAINER = ALL;
The command above sets the Oracle database for TDE. Open the Fortanix DSM Web UI and go to the audit log to confirm if the master key was generated and used.
- Click the Settings
tab , and then in the left panel click the LOG MANAGEMENT tab to see the audit logs.
Figure 1: Audit Logs
The Fortanix DSM is now successfully integrated with the Oracle TDE.
From here you can proceed to:- Start encrypting data. Follow Section 5.0.
- Improving the setup with Auto login. This allows to auto open the keystore on database restarts. See Section 8.1 Auto Login for 19c.
4.2 For Oracle Database Version 12c and 18c
4.2 1 Set Hardware Keystore Type in the sqlnet.ora
File
To configure a keystore for a hardware security module (hardware keystore), you must first define the keystore type in the sqlnet.ora
file. The Oracle Database checks the sqlnet.ora
file for the type of keystore and the directory location of the keystore. If not set, it assumes a software keystore by default. The sqlnet.ora
file is in the $ORACLE_HOME/network/admin
directory or in the location set by the TNS_ADMIN
environment variable. If the file is not present in the directory, create a new one.
Add the following line to the sqlnet.ora
file to define the hardware keystore type.
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD=HSM))
4.2.2 Set Up Keystore
- Log in with User
sys
with rolesysdba
.sqlplus / as sysdba
- Switch to root container (if it is a container database). The keystore needs to be opened in the root container first. Run the following command in SQL*Plus:
ALTER SESSION SET CONTAINER = CDB$ROOT;
- To open the hardware key store, run the following command:
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "file:///etc/fortanix/pkcs11.conf" CONTAINER = ALL;
IDENTIFIED BY
points to the location of the PKCS#11 Configuration file prefixed withfile://
.CONTAINER
is for use in a multitenant environment. EnterALL
to set the keystore in all the pluggable databases (PDBs) in this container database (CDB), orCURRENT
for the current PDB.
4.2.3 Set TDE Master Encryption 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.
- Run the following command:
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "file:///etc/fortanix/pkcs11.conf" CONTAINER = ALL;
The command above sets the Oracle database for TDE. Open the Fortanix DSM Web UI and go to the audit log to confirm if the master key was generated and used.
- Click the Settings
tab , and then in the left panel click the LOG MANAGEMENT tab to see the audit logs.
Figure 2: Audit Logs
The Fortanix DSM is now successfully integrated with the Oracle TDE.
From here you can proceed to:- Start encrypting data. Follow Section 5.0.
- Improving the setup with Auto login. This allows to auto open the keystore on database restarts. See Section 6.2 Auto Login for 12c and 18c.
5.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:
- Column Encryption: Selected columns of a table to be encrypted.
- Tablespace Encryption: Encrypt the entire tablespace using TDE. This is recommended by Oracle due to its better performance.
5.1 Column Encryption
- Connect to SQL*plus as a non-sysadmin user to enable encryption on a table.
- Next, create a table with an encrypted column. Use the
CREATE TABLE
SQL statement with theENCRYPT
clause.CREATE TABLE employee (first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER, salary NUMBER(6) ENCRYPT);
- Now insert some data into the table.
INSERT INTO employee VALUES ('JOHN', 'SMITH',001, 10000);
- 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.
5.2 Tablespace Encryption
- Connect to SQL*plus as a non-sysadmin user to create a new encrypted tablespace.
- Make sure that
COMPATIBLE
Initialization parameter setting is11.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
- Create a new encrypted tablespace using
CREATE TABLESPACE SQL
statement with theENCRYPT
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; - 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; - The
ENCRYPTED
column of theDBA_TABLESPACES
andUSER_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
6.0 Configuring Auto-Login
The method described in "Section 4.0" requires saving the Fortanix DSM API key in a config file. If you do not want to expose the API key in a file, you can configure the HSM key store to use auto-login using App ID and Password. The advantages would be:
- The API Key is not exposed to the database machine.
- In the case of database restart, the wallet is opened automatically.
In this method we 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 PKCS11 library will use this PIN and combine it with 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
6.1 Auto-Login in Oracle 19c
- 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 theV$ENCRYPTION_WALLET
view.Select * from V$ENCRYPTION_WALLET
- Close the HSM keystore if it is open.
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "file:///etc/fortanix/pkcs11.conf" CONTAINER = ALL;
- Create a directory for the software wallet.
mkdir /opt/oracle/admin/ORCLCDB/wallet/Fortanix
$WALLET_ROOT/tde
. - Change the keystore type to a software wallet.
ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE"
- Create a software keystore. This keystore will store the password to the HSM wallet for the purpose of auto-login.
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/oracle/admin/ORCLCDB/wallet/tde' IDENTIFIED BY "<DSM_APP_PASSWORD>";
- Open the software keystore.
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "<DSM_APP_PASSWORD>";
- 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 5.1: Using Fortanix Data Security Manager with Oracle TDE - Introduction.ADMINISTER KEY MANAGEMENT ADD SECRET '<DSM_APP_PASSWORD>' FOR CLIENT 'HSM_PASSWORD' IDENTIFIED BY "<DSM_APP_PASSWORD>" WITH BACKUP;
- Edit the existing PKCS#11 file:
- Remove API Key if exists in the file.
- Add App Id. This value can be fetched from Section 5.1: Using Fortanix Data Security Manager with Oracle TDE - Introduction.
The updated contents ofpkcs11.conf
is:
api_endpoint = "https://<fortanix_dsm_url>"
app_id = "<App UUID>"
prevent_duplicate_opaque_objects = true
retry_timeout_millis = 60000
[log]
file = "<log filename>"
- Close the software keystore.
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "<DSM_APP_PASSWORD>";
- Now create the auto-login keystore.
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/oracle/admin/ORCLCDB/wallet/tde IDENTIFIED BY "<DSM_APP_PASSWORD>";
- Set the wallet location to HSM backed by auto-login.
ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=HSM|FILE"
- Shut down and restart the database for changes to pick up.
- Run the following command to verify that the wallet is auto-open.
SELECT * FROM V$ENCRYPTION_WALLET;
6.2 Auto-Login in Oracle 12c and 18c
- 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 theV$ENCRYPTION_WALLET
view.Select * from V$ENCRYPTION_WALLET
- Close the HSM keystore if it is open.
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "file:///etc/fortanix/pkcs11.conf" CONTAINER = ALL;
- Create a directory for the software wallet.
mkdir /opt/oracle/admin/ORCLCDB/wallet/Fortanix
$ORACLE_BASE/admin/$ORACLE_SID/wallet/Fortanix
is the wallet location. - Now create a software keystore. This keystore will store the password to the HSM wallet for the purpose of auto-login. Make sure you use “
<DSM_APP_PASSWORD>
” as the wallet password in this step.ADMINISTER KEY MANAGEMENT CREATE KEYSTORE "/opt/oracle/admin/ORCLCDB/wallet/Fortanix" IDENTIFIED BY "<DSM_APP_PASSWORD>";
- Reconfigure the
sqlnet.ora
file and add the keystore location of the software keystore created in Step 3 to theDIRECTORY
setting of theENCRYPTION_WALLET_LOCATION
setting.
For example:ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/opt/oracle/admin/ORCLCDB/wallet/Fortanix)))
- Reconnect to the database, or log out and then log back in again, so that the change that you made in the previous step takes effect.
- Open the software keystore.
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;
- 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 5.1: Using Fortanix Data Security Manager with Oracle TDE - Introduction.ADMINISTER KEY MANAGEMENT ADD SECRET '<DSM_APP_PASSWORD>' FOR CLIENT 'HSM_PASSWORD' IDENTIFIED BY "<DSM_APP_PASSWORD>" WITH BACKUP;
- Edit the existing PKCS#11 file:
- Remove API Key if exists in the file.
- Add App Id. This value can be fetched from Section 5.1: Using Fortanix Data Security Manager with Oracle TDE - Introduction.
The updated contents ofpkcs11.conf
is:
api_endpoint = "https://<fortanix_dsm_url>"
app_id = "<App UUID>"
prevent_duplicate_opaque_objects = true
retry_timeout_millis = 60000
[log]
file = "<log filename>"
- Close the software keystore.
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "<DSM_APP_PASSWORD>";
- Now create the auto-login keystore.
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/oracle/admin/ORCLCDB/wallet/tde IDENTIFIED BY "<DSM_APP_PASSWORD>";
- Update the
sqlnet.ora
file to use the hardware security module location. Comment the previously configured wallet location (adjustDIRECTORY
to the path where your wallet is stored).ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD=HSM)(METHOD_DATA=(DIRECTORY=/opt/oracle/admin/ORCLCDB/wallet/Fortanix)))
- Shut down and restart the database for changes to pick up.
- Run the following command to verify that the wallet is auto-open.
SELECT * FROM V$ENCRYPTION_WALLET;
6.4 Rotate Master Key
Use the following commands to rotate a master key with/without auto-login wallet using a container and non-container database.
8.4.1 Without Using Auto-Login Wallet
Using container database:
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "file:///etc/fortanix/pkcs11.conf" CONTAINER = ALL;
Using non-container database:
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "file:///etc/fortanix/pkcs11.conf";
8.4.2 Using Auto-Login Wallet
Using container database:
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "DSM_APP_PASSWORD" CONTAINER = ALL;
Using non-container database:
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY "DSM_APP_PASSWORD";
7.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. But 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>"
api_key = "MWY5YT...TO5n"
prevent_duplicate_opaque_objects = true
retry_timeout_millis = 3000
oracle_tde_cache_config = {} # enabling prefetching of heartbeats
[log]
file = "<log filename>"
For more information on enabling prefetching of heartbeats, refer to Clients:PKCS#11 Library .
Comments
Please sign in to leave a comment.