Oracle Wallet & Transparent Data Encryption

These days in any organization, data protection is one of the top priorities. In fact, it’s a requirement for compliance with most laws, regulations like PCI.

We can protect the database from outsiders by following means

1. Build secure public network interface
2. Protect database by adding firewall around database servers.
3. Restrict the access to database servers, by adding IP based restrictions
4. Encrypt the sensitive data

Normally as a part of Disaster Recovery solution, database backups are stored at offsite location. Which means, data tapes need moving from one physical location to another. This physical move is the most vulnerable part of the data security.

To protect the data in this scenario, we can protect the data by a key, which resides outside the database structure. Without this key, all encrypted sensitive data will be of no use.

This problem is addressed in Oracle 10g R2. We can just declare the column as encrypted. When the data is inserted, it will be stored as encrypted. When it is retrieved, it is decrypted on the fly. Users will not know, what is happening at the database layer. We can also encrypt the existing data, by just modifying the column as encrypted. This feature is called as “Transparent Data Encryption”.

Oracle Database 10g encrypts data using a master key, which is stored in a secure location called a wallet, which is a file on the database server. Table keys are placed in the data dictionary. Oracle Database 10g generate a single encrypted table key for the table and uses it to encrypt those columns.

Since the data is stored encrypted, all dependent components, like backup and archived logs etc. are also in encrypted format.

To use the TDE, we need to configure database wallet. We can specify the location of the wallet in $ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/admin/DEVDB/wallet)))

Once the location is defined, we need to create the wallet.

SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “hardpass”;

NOTE: Double quotes are MUST

This command will perform following actions

1. Creates the wallet in the location specified in sqlnet.ora i.e. /u01/app/oracle/admin/DEVDB/wallet
2. Set the password of the wallet as “hardpass”.
3. Open the wallet for TDE

$ /u01/app/oracle/admin/DEVDB/wallet> ls -al

-rw——- 1 oracle oinstall 1838 Apr 25 12:04 ewallet.p12

If you enable the wallet auto-login feature, you will see another file

-rw——- 1 oracle oinstall 1866 Apr 25 12:04 cwallet.sso

This is a copy of your wallet, but in an encrypted/proprietary format. The cwallet.sso file does not require a password to open, and you can open it using the Oracle PKI Provider in java. You can also open the ewallet.p12 file using the PKI provider, but that file does require a password to open. Using the cwallet.sso file means you won’t have to store the wallet password in cleartext anywhere, so it’s more portable.

The wallet must be opened explicitly, after the database instance starts.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY “hardpass”;

If wallet is not open, even the owner of the table will not able to select the data & receive

ERROR at line 1:
ORA-28365: wallet is not open

We can explicitly close the wallet using

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

For encrypting the data, we can simply use

SQL> ALTER TABLE EMP MODIFY (SAL ENCRYPT);
SQL> ALTER TABLE EMP MODIFY (SAL DECRYPT);

Details of the encrypted columns are stored in DBA_ENCRYPTED_COLUMNS

SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME
—————————— ——————————
COLUMN_NAME ENCRYPTION_ALG SAL
—————————— —————————– —
SCOTT EMP
SAL AES 192 bits key NO

We can also change the key by using ALTER TABLE with REKEY clause.

GUI admin console for wallet can be started using $ORACLE_HOME/bin/owm


Performance impact on SELECT & INSERT

At the time of insertion, it has to read the encryption key from the wallet, encrypt the data and then write. Opposite, in case of data retrieval. It has to decrypt the data by reading the encryption key from the wallet. These operations will have some performance overhead. But it is much better than loosing the data & customer confidence.

Additional Licensing Cost

TDE is considered part of Oracle Advanced Security, so may have additional Licensing Cost

Advertisements
This entry was posted in Oracle Security and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s