The reason for using Oracle Transparent Data Encryption Oracle (TDE) column encryption is to protect confidential data, such as credit card and social security numbers, stored in table columns. TDE column encryption encrypts and decrypts data transparently when data passes through the SQL layer with no modification required to any existing application.
The first step to implementing Oracle TDE is to create a wallet file location that will store all of the encryption key information. Below is a screenshot after manually creating the “orcl_wallet” folder in the file directory on the machine where the Oracle database is installed:
The next step is to specify the location of the wallet in the “sqlnet.ora” file, located in the “NETWORK/admin” folder.
- ENCRYPTION_WALLET_LOCATION =(SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = ’file location’)))
Then, open up a command window and connect to “sqlplus” using the “sys” as an “sysdba” account. As an alternative, a user assigned the “SYSKM” privilege also login.
After logging into “sqlplus,” execute the following commands to create, open, and set the master key:
- ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘file location’ IDENTIFIED BY ‘password’;
- This command creates the keystore file in the location that was inserted into the sqlnet.ora file
- ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY ‘password’;
- This command opens the keystore to enable encrypting columns or tablespaces
- ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY ‘password’ WITH BACKUP;
- This command sets up the master key that controls all of the keys that are used to encrypt specific columns
Query “V_$ENCRYPTION_WALLET” to ensure that the “STATUS” column is set to “OPEN” and that the “WALLET_TYPE” column is set to “PASSWORD.” This ensures that the wallet has been set up correctly and is open.
One advanced feature of TDE is being able to set the wallet for auto-login. This opens the wallet and key store automatically whenever the database is started. If auto-login is not enabled, commands to open the key store must be executed each time the database is started.
To enable auto-login, enter the following statements:
- ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘file location’ IDENTIFIED BY ‘password’;
- After entering the “Create Auto Login” command, use the “Startup Force” command to restart the database instance. The reason to restart the database instance is so that it can switch the wallet type from “password” to “auto-login.”
To ensure that the auto-login is set to “Wallet,” log in to the database via any SQL querying tool and query the “V_$ENCRYPTION_WALLET” table. Ensure that your “WALLET_TYPE” column is set to “AUTOLOGIN” and that the “STATUS” column is set to “OPEN.”