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
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;
COLUMN_NAME ENCRYPTION_ALG SAL
—————————— —————————– —
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