23.3 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:

  1. The location specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file.
  2. The location specified by the WALLET_LOCATION parameter in the sqlnet.ora file. NOTE: You should have proper privileges to perform the following actions. For details to configure the software keystore, perform the following steps:
    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 should 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)))
    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

Perform the following steps 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.

    After you run this statement, the cwallet.sso file appears in the keystore location.

    Note:

    It is important to remember the master key password (<keystore_password>) used during creation of the keystore. There are no ways to retrieve the password if forgotten.
  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.

    Perform the following steps to open the software wallet:

    1. Login as sysdba or user with ADMINISTER KEY MANAGEMENT or SYSKM privilege.
    2. 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.

  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.)

    Perform the following 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 key- store 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';

  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:
  1. Setting the COMPATIBLE initialization parameter for tablespace encryption
  2. Setting the tablespace TDE master encryption key
  3. Creating the Encrypted Tablespace

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.

Perform the following 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.

    For example:

    SHOW PARAMETER COMPATIBLE

    Table 23-1 SHOW PARAMETER COMPATIBLE

    NAME   TYPE   VALUE
    compatible noncdbcompatible   string BOOLEAN  

    12.0.0.0

    FALSE
  3. If you want to change the COMPATIBLE parameter, perform the following steps:

Locate the initialization parameter file for the database instance.

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

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

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

For example:

COMPATIBLE = 12.2.0.0

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

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;

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.

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, see https://docs.oracle.com/cloud/latest/db121/ASOAG/ asotrans_config.htm#ASOAG9555 for details on manually creating encrypted tablespaces:

Configuring TDE in case of Upgrade

Create a new PDB (12c)/ instance (11g) on same or different Database Server for TDE. For more information, see Configuring Software Keystore and Encrypted Tablespace Creation.

Shutdown the OFSAAI Services.

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.

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.

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

Update .profile for ORACLE_SID environment variable with new ORACLE_SID.

Update JDBC URL by executing Port Changer utility. For details on how to execute Port Changer utility, see Changing IP/ Hostname, Ports, Deployed paths, Protocol of the OFSAA Instance section.

Navigate to the $FIC_WEB_HOME directory and execute the following command to trigger the creation of EAR/WAR file:

./ant.sh

The EAR/WAR file - <contextname>.ear/.war - is created in $FIC_WEB_HOME directory.

On completion of EAR/WAR file creation, the message "BUILD SUCCESSFUL" will be displayed. Edit the existing Connection Pool settings to point to new JDBC URL and verify connections.

Clear the webserver cache and redeploy the application onto your configured web application server.

Restart the OFSAA Services. For more information, refer to the Start/Stop Infrastructure Services section in the Oracle Financial Services Advanced Analytical Applications Infrastructure Application Pack Installation and Configuration Guide .