Security. Each day it seems another breach is reported, another hack revealed, more personal user information is stolen, apparently despite the best efforts to thwart such attacks. It’s becoming increasingly obvious that guarding against break-ins is simply not enough; one must be prepared for the maliciously inclined to succeed at hacking their way into ‘secure’ systems. For the Oracle DBA this may not be as daunting a task as it first appears. Oracle offers Transparent Data Encryption (TDE) [available with the Oracle Advanced Security Option] to protect sensitive data at the column, table or tablespace level, rendering any attempts to abscond with encrypted data files essentially useless. Let’s take a deeper look into Oracle’s TDE and see how it can protect vulnerable data.
TDE ‘transparently’ encrypts (and decrypts) data protected by an Oracle keystore/wallet. Using the chosen encryption algorithm (selected from several available) data is encrypted upon insert and decrypted upon select in protected databases as long as the wallet/keystore is open. The list of available encryption algorithms is shown below:
Encryption Algorithms
* Advanced Encryption Standard (AES) Key length: 128, 192, 256 bits
* Triple Data Encryption Standard (TDES) Key length: 168 bits
* Regional encryption algorithms ARIA and SEED
* GOST
Hashing Algorithms (optional)
*Secure Hash Algorithm 1 (SHA-1) Digest length: 160 bits
Given the available encryption and hashing options, data protected with TDE will be undecipherable without the keystore password.
Configuration begins with the creation of a wallet/keystore associated with the database. Some preliminary work needs to be done before attempting to create such a wallet; the local sqlnet.ora file must be modified to include the following entry:
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/$ORACLE_SID/)))
This allows Oracle to find the wallet location, so it can be opened and used. Choose a directory (or create one specifically for this purpose) and create a full path for each ORACLE_SID that will have a keystore. For example, if the base directory is /oracle/mywallet and there are databases named SNORD and PLEEBO on that server the full paths to create will be /oracle/mywallet/SNORD and /oracle/mywalled/PLEEBO. Match the case of the directory name with the ORACLE_SID found in the ora_pmon_
Presuming the Advanced Security Option is licensed for your installation the next step is to create the wallet/keystore. The following script can be used to ensure no steps are missed:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/oracle/mywallet/&&&1' IDENTIFIED BY &:&:2;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY &:&:2;
administer key management create key using tag '&:&:1._master_key' identified by &:&:2 with backup using '&:&:1._master_bkup';
ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/oracle/mywallet/&:&:1/' IDENTIFIED BY &:&:2;
column key_id new_value key_identifier noprint
SELECT KEY_ID FROM V$ENCRYPTION_KEYS;
ADMINISTER KEY MANAGEMENT USE KEY '&key_identifier' IDENTIFIED BY &&2 WITH BACKUP USING '&&1._master_key';
Connect to the database as SYS AS SYSDBA to execute the commands; the &&: variables will contain the ORACLE_SID and the encryption password, in that order. As an example, if the script was named cr_wallet.sql it would be executed in this manner:
SQL> @cr_wallet snord plinkenfarb
Remember the encryption wallet password; it is a good idea to store it in a password safe of some sort for retrieval later.
Now that the wallet is created (if all of the commands were executed then it will be an auto-login wallet that will open when the database is started) and the master key is in use data can be encrypted. One way is to create encrypted tablespaces; in such cases all data in the tables is encrypted regardless of sensitivity. As stated earlier as long as the encryption wallet is open the data is transparently encrypted/decrypted without user intervention. It is also possible to encrypt specific columns of specific tables; in this case only the encrypted columns will be obfuscated if the data is somehow obtained by unauthorized persons so that not even a ‘strings’ command will return it. As with the full tablespace encryption any encrypted columns in tables will be automatically and transparently decrypted while the wallet is open.
Creating encrypted tablespaces is not much different from creating ‘regular’ tablespaces; the additional commands are highlighted in the example below which uses Oracle Managed Files to generate file names:
CREATE TABLESPACE "USERS_TDE" DATAFILE
SIZE 11796480
AUTOEXTEND ON NEXT 11796480 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);
The algorithm used can be selected from the list provided earlier in this article; by default, AES192 will be used if no specific algorithm is supplied in the CREATE TABLESPACE statement.
To encrypt specific columns in a table, specify the ENCRYPT directive for each column to be encrypted, optionally selecting the algorithm to use (by default it’s AES192):
CREATE TABLE employee (
first_name VARCHAR2(128),
last_name VARCHAR2(128),
empID NUMBER ENCRYPT NO SALT,
salary NUMBER(6) ENCRYPT USING 'AES256'
);
Multiple columns can be encrypted in a single table, and not every table in the schema needs to have columns encrypted. In the example above two columns are encrypted with different encryption levels. Notice the NO SALT directive for one column; this is necessary if the encrypted column is to be indexed. Even though SALT-ed columns make brute force attacks even harder to complete every NO SALT column is still quite secure against attacks.
TDE isn’t the ‘be-all’ and ‘end-all’ of data obfuscation, but it can cause a would-be hacker to look elsewhere for ‘easier’ targets. As the old adage states — “An ounce of prevention is worth a pound of cure.” Consider licensing the Advanced Security Option and configuring TDE to protect valuable, personal data from attack. It just might get the DBA team a bit more sleep at night.
