Introduction:- Today we are going to learn about encryption in Oracle. Oracle database has the unique feature to secure data from the data loss. As we get a hint from the word encryption that means the process of converting information or data into code, especially to prevent unauthorized access. Rising security threats, expanding compliance requirements, consolidation, and cloud computing are just a few of the reasons why data security has become critical. Protecting data requires a defense in depth, multi-layered approach that encompasses controls to evaluate security postures, prevent data loss, detect suspicious activities and apply data access controls at the source through data-driven security.

To create a standard Oracle wallet and then add a master key to it you have to follow few basic steps:

  1. Configure a standard wallet and then add a master key to it you have to follow few basic steps
  2. Creating the Software Keystore
  3. Opening a Software Keystore
  4. Setting the TDE Master Encryption Key in the Software Keystore
  5. Encrypt the Data

1) Configure the sqlnet.ora file 

Oracle should know where to find the Oracle Wallet so you have to define a directory accessible by the Oracle Software.
In the multi tenant solution, the Oracle Wallet location is valid for the CDB and every PDBs at the same time. Edit your sqlnet.ora file and use the following syntax to let the database know where the software key store is located on file system.
Be sure that the directory exists to avoid the error “ORA-46633: creation of a password-based keystore failed”:

2) Create the Oracle Wallet
It’s possible to create the Oracle Wallet using the owm gui utility (as you can read on this post) or from sqlplus with a new set of key management statements (ADMINISTER KEY MANAGEMENT).
The steps to create an Oracle Wallet must be executed from the sqlplus command line with a user who has been granted the new SYSKM administrative privilege:
when in a multitenant environment you have to log in to the root container.

To avoid the error “ORA-46633: creation of a password-based keystore failed”, the directory you are going to specify in the create keystore statement must be already present.

To create the keystore under the path specified in the sqlnet.ora file use the following statement:

Querying the V$ENCRYPTION_WALLET view you can see the location, status and type of the wallet.

3) Opening a Software Keystore
To setup, configure and use encrypted tablespace or column the Oracle Wallet needs to be open.
The v$encryption_wallet view says the status of the wallet is closed so you need to open it using the following statement:

The status is now OPEN_NO_MASTER_KEY. This means that the wallet is open, but still a master key needs to be created.

4) Setting the TDE Master Encryption Key in the Software Keystore
You need to set a master key for the Oracle wallet used in the TDE activities on tables or tablespace.

If you need to change the password of your Oracle Wallet because of your company’s security guidelines, you must use the WITH BACKUP option: in this way you are forced to take a backup of your “old” wallet.

A change of the password doesn’t prevent the normal use of every TDE operations: they continue to work as usual with the new password without interruptions. You need to provide the old password and the new password.

5) Encrypt the Data
Once you have created an Oracle Wallet and set a TDE master key in it, you can proceed to encrypt your data. Let’s start creating a new encrypted tablespace first and then a column’s table. My current data files:

The statement to create an encrypted tablespace:

Information about the encrypted tablespace available in the database.

How is it possible to test if the data is encrypted or not ? I’m going to create a table on the USERS (unencrypted) tablespace and another on the TS_ENCRYPTED tablespace. Because the Oracle Wallet is already open I can create on the encrypted tablespace the t1_encrypted table and insert some rows in it.

Flush the buffer cache to be sure all data is written to the datafiles.

I’m able to grep and see the text on the USERS tablespace, but not that one on the TS_ENCRYPTED tablespace.

Let’s see what happens when the Oracle Wallet is closed. The following command closes an open Oracle Wallet.

You have to open again the Oracle Wallet to successfully execute the query

Let’s see how a closed Oracle Wallet affects an encrypted column of a table. I’m going to create a new table with two columns: one is encrypted and the other is not encrypted.

The Oracle Wallet is closed.

When the Oracle Wallet is closed I can able to query the non-encrypted column.

But when I try to query the encrypted column it fails:

I need first to open the Oracle Wallet

Now I can query again the encrypted column of my table.


About The Author

Leave a Reply