Friday, August 15, 2008

Transparent Data Encryption

# create wallet directory
mkdir /opt/oracle/product/10.2.0/db_1/encryption_wallet

# sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/product/10.2.0/db_1/encryption_wallet/)))

sqlplus / AS SYSDBA

#create the table and insert data
CREATE TABLE datafix.tde_test (
id NUMBER(10),
data VARCHAR2(50)
);

INSERT INTO datafix.tde_test (id, data) VALUES (1, 'secret');
COMMIT;

select * from datafix.tde_test;

# create the encryption key and password
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "password";

# encrypt the table field
alter table datafix.tde_test modify (data encrypt);

select * from datafix.tde_test;

# close the wallet

ALTER SYSTEM SET WALLET CLOSE;

select * from datafix.tde_test;

# open the wallet

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "password";

select * from datafix.tde_test;

# check what is encrypted

SQL> SELECT * FROM dba_encrypted_columns;

OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME ENCRYPTION_ALG SAL
------------------------------ ----------------------------- ---
DATAFIX TDE_TEST
DATA AES 192 bits key YES