R.1 Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) enables you to encrypt sensitive data, such as Personally Identifiable Information (PII), that you store in tables and tablespaces.

After the data is encrypted, this data is transparently decrypted for authorized users or applications when they access this data. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a Keystore.

For more details on TDE, see the Database Advanced Security Guide.

TDE tablespace encryption enables you to encrypt all of the data stored in a tablespace. To control the encryption, you use a Keystore and TDE master encryption key. Oracle Database supports both software keystores and hardware, or HSM-based, keystores. A software keystore is a container for the TDE master encryption key, and it resides in the software file system.

Configuring TDE During Behavior Detection Installation Using Full Installer

This section provides information on how to enable TDE (Transparent Data Encryption) in the database.

Configuring a Software Keystore and Encrypted Tablespace Creation

A software keystore is a container for the TDE master encryption key, and it resides in the software file system. You must define a location for the key in the sqlnet.ora file so that the database locates the keystore (one per database) by checking the keystore location in the sqlnet.ora file. After defining the location, create the keystore and open it. Set the TDE master key after opening it and then encrypt the data.

To find whether a wallet is already existing, check the following entries:
  • The location specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
  • The location specified by the WALLET_LOCATION parameter in the sqlnet.ora file.
  • Encrypted tablespaces can share the default database wallet. However, Oracle recommends that you use a separate wallet for transparent data encryption functionality by specifying the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.

    Note:

    You should have proper privileges to perform the following actions.

To configure the software keystore, follow these steps:

Step 1: Set the Software keystore location in the sqlnet.ora file

The first step is to designate a location for software keystore in the sqlnet.ora file. The Oracle Database will check the sqlnet.ora file for the directory location of the keystore to determine whether it is a software keystore or a hardware module security (HSM) keystore.

Note:

Ensure that the directory location which you want to set for software keystore exists beforehand. Preferably, this directory must be empty.

In a multitenant environment, the keystore location is set for the entire multitenant container database (CDB), not for individual pluggable databases (PDBs).

By default, the sqlnet.ora file is located in the ORACLE_HOME/ network/admin directory or in the location set by the TNS_ADMIN environment variable. Ensure that you have properly set the TNS_ADMIN environment variable to point to the correct sqlnet.ora file.

To create a software keystore on a regular file system, use the following format when you edit the sqlnet.ora file:
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA=(DIRECTORY=<<path to keystore>>)))

Examples:

For regular file system in which the database name is orclb: 
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=/etc/ORACLE/WALLETS/orcl)))
When multiple databases share the sqlnet.ora file: 
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=/etc/ORACLE/WALLETS/orcl)))
When Oracle Automatic Storage Management (ASM) is configured: 
ENCRYPTION_WALLET_LOCATION=(SOURCE= (METHOD=FILE) (METHOD_DATA=(DIRECTORY=+disk1/mydb/wallet)))
For ASM Diskgroup: 
ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=+ASM_file_path_of_the_diskgroup)))

Step 2: Create the Software Keystore

There are three different types of Software Keystores:
  • Password-based Software Keystores
  • Auto-login Software Keystores
  • Local Auto-login Software Keystores
To create a software keystore:
  1. Login as sysdba or user with ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  2. Use the following command to create password-based software keystore:

    CONN sys/password@serviceid AS SYSDBA

    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location' IDENTIFIED BY software_keystore_password;

    keystore_location is the path of the keystore directory you want to create software_keystore_password is the password of the keystore that you want to create. For example, to create the keystore in the /etc/ORACLE/WALLETS/orcl directory: ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl'

    IDENTIFIED BY password;

    After you run this statement, the ewallet.p12 file, which is the keystore, appears in the keystore location.

    Alternatively, you can create an Auto-Login or Local-Login Keystore to avoid opening the Keystore manually every time. Use the following command:

    ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY keystore_password;

    LOCAL enables you to create a local auto-login software keystore. Otherwise, omit this clause if you want the keystore to be accessible by other computers.

Step 3: Open the Software Keystore

Depending on the type of keystore you create, you must manually open the keystore before you can use it.

You do not need to manually open auto-login or local auto-login software keystores. These keystore are automatically opened when it is required, that is, when an encryption operation must access the key. If necessary, you can explicitly close any of these types of keystores. You can check the status of whether a keystore is open, closed, open but with no master key, or open but with an unknown master key by querying the STATUS column of the V$ENCRYPTION_ WALLET view.

Note:

After you open a keystore, it remains open until you manually close it. Each time you restart a database instance, you must manually open the password keystore to re-enable encryption and decryption operations.

Follow these steps to open the software wallet:

Login as sysdba or user with ADMINISTER KEY MANAGEMENT or SYSKM privilege.

Use the following command to open password-based software keystore:
CONN sys/password@serviceid AS SYSDBA ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password [CONTAINER = ALL | CURRENT];

software_keystore_password is the same password that you used to create the keystore in Step 2: Create the Software Keystore.

CONTAINER is for use in a multitenant environment. Enter ALL to set the keystore in all of the PDBs in this CDB, or CURRENT for the current PDB.

Note:

In a CDB, open the Keystore in the ROOT (CDB$ROOT) container and in all the associated PDBs, where TDE is enabled.

You do not need to manually open auto-login or local auto-login software Keystores.

Step 4: Set the Software TDE Master Encryption Key

Once the keystore is open, you can set a TDE master encryption key for it. The TDE master encryption key is stored in the keystore. This key protects the TDE table keys and tablespace encryption keys. By default, the TDE master encryption key is a key that Transparent Data Encryption (TDE) generates.

In a multitenant environment, you can create and manage the TDE master encryption key from either the root or the PDB.

Ensure that the database OPEN_MODE is set as READ WRITE. To find the status for a non-multitenant environment, query the OPEN_MODE column of the V$DATABASE dynamic view. If you are using a multitenant environment, then query the V$PDBS view. (If you cannot access these views, then connect as SYSDBA and try the query again. In order to connect as SYSKM for this type of query, you must create a password file for it. See Oracle Database Administrator's Guide for more information.)

Follow these steps to set the encryption key:

  1. Login as sysdba or user with ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  2. Use the following command to set the encryption key:
    CONN sys/password@serviceid AS SYSDBA ADMINISTER KEY MANAGEMENT SET KEY [USING TAG 'tag'] IDENTIFIED BY password [WITH BACKUP [USING 'backup_identifier']] [CONTAINER = ALL | CURRENT];
    • tag is the associated attributes and information that you define. Enclose this setting in single quotation marks ('').
    • password is the mandatory keystore password that you created when you created the keystore in "Step 2: Create the Software Keystore".
    • WITH BACKUP creates a backup of the keystore. You must use this option for password- based keystores. Optionally, you can use the USING clause to add a brief description of the backup. Enclose this description in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time_stamp_ emp_key_backup.p12, with emp_key_backup being the backup identifier). Follow the file naming conventions that your operating system uses.
    • CONTAINER is for use in a multitenant environment. Enter ALL to set the key in all of the PDBs in this CDB, or CURRENT for the current PDB.
For example: 
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password WITH BACKUP USING 'emp_key_backup';

Step 5: Encrypting your Data

After completing the keystore configuration, encrypt the data. You can encrypt individual columns in a table or entire tablespaces. OFSAA recommends encrypting entire tablespaces and the description in this section covers encrypting entire tablespaces.

Note the following restrictions on using Transparent Data Encryption when you encrypt a tablespace:
  • Transparent Data Encryption (TDE) tablespace encryption encrypts or decrypts data during read and write operations, as compared to TDE column encryption, which encrypts and decrypts data at the SQL layer. This means that most restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions, do not apply to TDE tablespace encryption.
  • To perform import and export operations, use Oracle Data Pump. Encrypting data involves the following steps:
    • Setting the COMPATIBLE initialization parameter for tablespace encryption.
    • Setting the tablespace TDE master encryption key.
    • Creating the Encrypted Tablespace.

Step 1: Setting the COMPATIBLE initialization parameter for tablespace encryption

Prerequisite- You must set the COMPATIBLE initialization parameter for the database to 11.2.0.0 or later. Once you set this parameter to 11.2.0.0, the change is irreversible.

Follow these steps to set the COMPATIBLE initialization parameter:
  1. Log into the database instance. In a multitenant environment, log into the PDB.
  2. Check the current setting of the COMPATIBLE parameter as described in the following table.

    Table R-1 Show Parameter Compatible

    Name Type Value
    Compatible String 12.0.0.0
    noncdbcompatible Boolean False
  3. If you want to change the COMPATIBLE parameter, follow these steps:
    1. Locate the initialization parameter file for the database instance.

      UNIX systems: This file is in the ORACLE_HOME/dbs directory and is named initORACLE_SID.ora (for example, initmydb.ora).

    2. In SQL*Plus, connect as a user who has the SYSDBA administrative privilege, and then shut down the database.

      For example: CONNECT /AS SYSDBA SHUTDOWN

    3. Edit the initialization parameter file to use the correct COMPATIBLE setting.

      For example: COMPATIBLE = 12.2.0.0

    4. In SQL*Plus, ensure that you are connected as a user who has the SYSDBA administrative privilege, and then start the database.

      For example: CONNECT /AS SYSDBA STARTUP

    5. If tablespace encryption is in use, then open the keystore at the database mount. The keystore must be open before you can access data in an encrypted tablespace.
      STARTUP MOUNT;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password; ALTER DATABASE OPEN;

Step 2: Setting the tablespace TDE master encryption key

Make sure that you have configured the TDE master encryption key as shown in Step 4: Setting the software TDE master encryption key.

Step 3: Creating the Encrypted Tablespace

After you have set the COMPATIBLE initialization parameter, you are ready to create the encrypted tablespace.

Follow the instruction given in Running the Schema Creator Utility with Encryption section for configuring the schema creator file to create tablespaces.

If you are enabling TDE in case of upgrade or you did not enable it during installation and want to enable at a later point of time.

Running the Schema Creator Utility With Encryption

This section is applicable only if you want to enable TDE during installation.

Run the schema creator utility by including the encrypt=on option in the Tablespace tag in the

<<APP PACK>>_SCHEMA_IN.xml file. You have to perform this procedure manually as it is not a part of the <<APP PACK>>_SCHEMA_IN.xml.TEMPLATE originally.

Following is an example for OFS _AAAI_PACK_ SCHEMA_IN.xml
<APPPACKSCHEMA>
<APP_PACK_ID>OFS_AAAI_PACK</APP_PACK_ID>
<JDBC_URL>jdbc:oracle:thin:@<DB_Server_IP>:1521:<DB_NAME></JDBC_URL>
<JDBC_DRIVER>oracle.jdbc.driver.OracleDriver</JDBC_DRIVER>
<HOST><OFSAA_Server_IP/HOST Name></HOST>
<SETUPINFO NAME="<PREFIX_NAME>" PREFIX_SCHEMA_NAME="Y"/>
<PASSWORD APPLYSAMEFORALL="Y" DEFAULT="<PASSWORD>"/>
<TABLESPACES>
<TABLESPACE NAME="OFS_AAI_TBSP" VALUE="TS_USERS1" DATAFILE="<ABSOLUTE PATH to TABLESPACE>/<TABLESPACE_DATA_FILE_NAME>.dbf" SIZE="500M" AUTOEXTEND="OFF" ENCRYPT="ON" />
</TABLESPACES>
<SCHEMAS>
<SCHEMA TYPE="CONFIG" NAME="ofsaaconf" PASSWORD="" APP_ID="OFS_AAI" DEFAULTTABLESPACE="##OFS_AAI_TBSP##" TEMPTABLESPACE="TEMP"
QUOTA="unlimited"/>
<SCHEMA TYPE="ATOMIC" NAME="ofsaaatm" PASSWORD="" APP_ID="OFS_AAAI" DEFAULTTABLESPACE="##OFS_AAI_TBSP##" TEMPTABLESPACE="TEMP" QUOTA="unlimited" INFODOM="OFSAAAIINFO"/>
<SCHEMA TYPE="ATOMIC" NAME="ofsaaatm" PASSWORD="" APP_ID="OFS_IPE" DEFAULTTABLESPACE="##OFS_AAI_TBSP##" TEMPTABLESPACE="TEMP" QUOTA="unlimited" INFODOM="OFSAAAIINFO"/>
</SCHEMAS>
</APPPACKSCHEMA>

Testing the Encryption

Test the encryption by checking if a tablespace is encrypted or not. Execute the following query to check:
SELECT tablespace_name, encrypted FROM dba_tablespaces;

indicates whether the TABLESPACE is encrypted or not in the ENCRYPTED column.

Table R-2 Testing the Encryption

TABLESPACE_NAME ENCRYPTED
SYSTEM No
SYSAUX No
UNDOTBS1 No
TEMP No
USERS No
ENCRYPTED_TS Yes
6 rows selected. -

The above example indicates TABLESPACE ENCRYPTED_TS is created with Encryption ON.

Configuring TDE in Case of Upgrade

This section details about the configurations required in case you want to enable TDE in OFSAA applications after upgrade to OFSAA 8.1.2.0.0 version from a previous version. Additionally, these configurations are required in case you did not enable TDE during 8.1.2.0.0 installation and want to enable at a later point of time.

To configure the TDE in case of upgrade, follow these steps:

  1. Create a new PDB (12c)/ instance (11g) on same or different Database Server for TDE. For more information, see the Configuring a Software Keystore and Encrypted Tablespace Creation section above.
  2. Shutdown the OFSAAI Services.
  3. Export all Configuration, Atomic and Sandbox Schemas as per the applications installed in your OFSAA instance.
    For example:
    expdp SYSTEM/oracle@OFSA12C2DB DIRECTORY=data_pump_dir DUMPFILE=ofsaaconf_ofsaaatm_%U.dmp filesize=2G SCHEMAS=ofsaaconf,ofsaaatm LOGFILE=ofsaaconf_ofsaaatm_exp.log

    Note:

    The above command will create data dumps as files of 2GB size each (multiples). Any other commands/tools as appropriate may be used to archive the schemas.
  4. Import all schemas that are exported using the above command, into the new DB instance.
    For example: 
    impdp SYSTEM/oracle@OFSA12nDB DIRECTORY=data_pump_dir DUMPFILE=ofsaaconf_ofsaaatm_%U.dmp SCHEMAS=ofsaaconf,ofsaaatm LOGFILE=ofsaaconf_ofsaaatm_imp.log

    Note:

    Restoring the exported dumps creates Configuration and Atomic Schema(s) with the same user credentials as that of the source, along with the existing grants.

    If schemas are restored using a tool/ mechanism other than as mentioned in the Step 1 and 2, retain the user credentials of Configuration and Atomic Schemas same as in the Source environment, along with the Schema grants.

  5. Provide select grants on sys.V_$parameter to view Configuration and Atomic Schemas of Target Environment database.

    For example:

    Login as sys user:

    SQL> GRANT SELECT ON SYS.V_$PARAMETER TO ofsaaconf;

    Grant succeeded

    SQL> GRANT SELECT ON SYS.V_$PARAMETER TO ofsaaatm;

    Grant succeeded

  6. Update .profile for ORACLE_SID environment variable with new ORACLE_SID.
  7. Update JDBC URL by executing Port Changer utility. For details on how to execute Port Changer utility, see Changing IP/Hostname, Ports, Deployed Paths of the OFSAA Instance.
  8. Navigate to the $FIC_WEB_HOME directory and execute the following command to trigger the creation of EAR/WAR file: ./ant.sh.
  9. The EAR/WAR file - <contextname>.ear/.war - is created in $FIC_WEB_HOME directory. On completion of EAR/WAR file creation, a message indicating that build is successful is displayed.
  10. Edit the existing Connection Pool settings to point to new JDBC URL and verify connections.
  11. Clear the webserver cache and redeploy the application onto your configured web application server.
  12. Restart the OFSAA Services. For more information, refer to the Start/Stop Infrastructure Services section in Oracle Financial Services Advanced Analytical Applications Infrastructure Application Pack Installation and Configuration Guide.