Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database.

The operating system data files where data is stored is not used. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. Encrypted data is transparently decrypted for a database user or application that has access to data.

OFSLL application processes sensitive data. Hence, it is recommended to use a TDE to protect confidential data, such as credit card and social security numbers, stored in table columns.

A script is provided along with the distribution media to encrypt the sensitive columns in the table. You can follow the steps below to enable TDE for column encryption:

To start using TDE, the security administrator must create a wallet and set a master key. The wallet can be the default database wallet shared with other Oracle Database components, or a separate wallet specifically used by TDE. Oracle strongly recommends that you use a separate wallet to store the master encryption key.

Specifying a Wallet Location for Transparent Data Encryption

If you wish to use a wallet specifically for TDE, then you must specify a wallet location in the sqlnet.ora file by using the ENCRYPTION_WALLET_LOCATION parameter. Oracle recommends that you use the ENCRYPTION_WALLET_LOCATION parameter to specify a wallet location for TDE.

ENCRYPTION_WALLET_LOCATION = (SOURCE =

(METHOD = FILE)

(METHOD_DATA =

(DIRECTORY =

/etc/ORACLE/WALLETS/oracle)))

If no wallet location is specified in the sqlnet.ora file, then the default database wallet location is used. The default database wallet location is ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet or ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet. Here, DB_UNIQUE_NAME is the unique name of the database specified in the initialization parameter file.

Setting the Master Encryption Key

The master encryption key is stored in an external security module, and is used to protect the table keys and tablespace encryption keys. By default, the master encryption key is a random key generated by Transparent Data Encryption (TDE). It can also be an existing key pair from a PKI certificate designated for encryption. To use TDE with PKI key pairs, the issuing certificate authority must be able to issue X.509v3 certificates with the key usage field marked for encryption.

To set the master encryption key, use the following command:

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY ‘password’

Resetting the Master Encryption Key

Reset/Regenerate the master encryption key only if it has been compromised or as per the security policies of the organization. You should back up the wallet before resetting the master encryption key.

Use the ALTER SYSTEM command to set or reset (rekey) the master encryption key.

Opening and Closing the Encrypted Wallet

The database must load the master encryption key into memory before it can encrypt or decrypt columns/tablespaces. Opening the wallet allows the database to access the master encryption key. Use the following ALTER SYSTEM command to explicitly open the wallet:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password"; where password is the password to open the wallet. You should enclose the password string in double quotation marks (" ").

Once the wallet has been opened, it remains open until you shut down the database instance, or close it explicitly by issuing the following command:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "password"

Encrypting Columns in Existing Tables

To add an encrypted column to an existing table, or to encrypt or decrypt an existing column, you use the ALTER TABLE SQL command with the ADD or MODIFY clause.

SQL> ALTER TABLE applicants MODIFY (apl_gender_cd ENCRYPT USING 'AES256');

Encrypting the indexed columns:

SQL> ALTER TABLE applicants MODIFY (apl_ssn ENCRYPT USING 'AES256' NO SALT);

Disabling Encryption on a Column

You may want to disable encryption for reasons of compatibility or performance. To disable column encryption, use the ALTER TABLE MODIFY command with the DECRYPT clause.

Example 7-11 Turning Off Column Encryption:

SQL> ALTER TABLE applicants MODIFY (apl_gender_cd DECRYPT);

A Set of scripts are provided on the installation media in the dba_utils directory to encrypt the recommended columns in OFSLL.

upgrade_tb_tde_enable_ofsll.sql to encrypt base table columns.

upgrade_tb_opur_tde_enable_ofsll.sql to encrypt archive table columns.

upgrade_tb_api_tde_enable_ofsll.sql to encrypt api table columns.