Transparent Data Encryption
Encrypt sensitive data transparently without writing a single line of code.
Organization's worst nightmare: Someone has stolen backup tapes of your database. Sure, you built a secure system, encrypted the most sensitive assets, and built a firewall around the database servers.
On database, protecting data from such theft is not just good practice; it's a requirement for compliance with most laws, regulations, and guidelines. How can you protect your database from this vulnerability? One solution is to encrypt the sensitive data in the database and store the encryption keys in a separate location; without the keys, any stolen data is worthless. However, you must strike a balance between two contradictory concepts: the convenience by which applications can access encryption keys, and the security required to prevent the key theft. And to comply with company and federal regulations, you need a solution immediately, without any complex coding.
A new feature introduced in Oracle Database 10g Release 2 lets you do just that: You can declare a column as encrypted without writing a single line of code. When users insert the data, the database transparently encrypts it and stores it in the column. Similarly, when users select the column, the database automatically decrypts it. Since all this is done transparently without any change to the application code, the feature has an appropriate name: Transparent Data Encryption (TDE).
Transparent Data Encryption belongs to the Advanced Security Option that is available as an Option for the Oracle Database 10g Enterprise Edition only.
Transparent data encryption is a key-based access control system.
Even if the encrypted data is retrieved, it cannot be understood until authorized decryption occurs, which is automatic for users authorized to access the table.
The keys for all tables containing encrypted columns are encrypted with the database server master key and stored in a dictionary table in the database. No keys are stored in the clear.
What is needed to create/store the master key of the server ?
A wallet file that is outside the database and accessible only to the security administrator:
1. Storing the master key in this way prevents its unauthorized use.
2. It is also used to generate encryption keys and perform encryption and decryption.
A. Prepare the database to allow Transparent Data Encryption
1- Specify the location of the wallet file used to store the encryption master key by adding the following entry in $ORACLE_HOME/network/admin/sqlnet.ora:
ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=D:\Oracle\product\10.2.0\db_2)))
We can enter any existing directory, but make sure there is no obfuscated wallet in this directory (named 'cwallet.sso')
It is not mandatory to specify the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora.
If not specified, when "ALTER SYSTEM SET ENCRYPTION..." is executed, the server creates the wallet in the default location : $ORACLE_BASE/ADMIN//WALLET.
If this location doesn't exist, an ORA-28368 is received.
This default location can help a lot when someone needs to have different wallets for different instances in the same ORACLE_HOME.
Instead of playing around with different values of TNS_ADMIN environment variable, let each instance write its own wallet in its default location.
2- Start the listener:
Lsnrctl start
3- Connect to the database 'as sysdba':
Sqlplus “/as sysdba”
4. Create the encrypted wallet file :
SQL%gt; ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "swagatham";
If no encrypted wallet is present in the directory defined in sqlnet.ora :
1.) It creates an encrypted wallet (ewallet.p12)
2.) It opens the wallet
3.) It creates the database server master encryption key for TDE
If an encrypted wallet already exists :
1.) It opens the wallet
2.) It creates or re-creates the database server master encryption key for TDE.
This is done ONLY ONCE, unless you want to re-encrypt your data with a new encryption key for the whole database.
Note: It can also be an existing key pair from a PKI certificate designated for encryption. To use transparent data encryption with PKI key pairs, the issuing certificate authority must be able to issue X.509v3 certificates with the key usage field marked for encryption.
SQL> ALTER SYSTEM SET ENCRYPTION KEY "certificate_ID" IDENTIFIED BY "swagatham";
You can search for a certificate_ID by querying the V$WALLET fixed view when the wallet is open. Only certificates that can be used as master keys by transparent data encryption are shown.
For later sessions, you do not have to use the same command : you only need to open the wallet (it is automatically closed when you shut down the database).
To load the master key after the database is restarted :
SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "swagatham";
The master encryption key is necessary because each table has its own encryption key. The column keys are stored in the database and encrypted with the master key.
By default, the command above generates a master key using the Advanced Encryption
Standard with 192 bits : AES192.
3DES could be used, or a smaller or bigger number of bits for the AES encryption : 3DES168 (168 Bits) , AES256 (256 Bits), AES128 (128 Bits)
Note that only users with the 'ALTER SYSTEM' privilege ('/as sysdba' or users with the default DBA role, like 'system') can create a wallet and a master key, or open the wallet. Users without proper privileges are not able to use the key, since it is not available to them.
B. The database is ready to allow encryption: how to encrypt/decrypt data in table columns.
1- Create table KUMAR.CUST_CREDITPAY_INFO with encryption (default AES192) applied to the CREDIT_CARD_NUMBER column .
NO SALT is specified since there will be an index on the credit_card_number column, which is not possible when the encrypted values are 'salted'.
SALT is always added in encrypted columns by default to cleartext before encrypting it. This makes it harder for attackers to steal the data through a brute force attack.
Full article...
