Monday, February 14, 2011

Oracle ASO: TDE with column level encryption


$ORACLE_HOME/network/admin/sqlnet.ora
==================================================
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/home/oracle/wallet)))

SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(MD5)
SQLNET.ENCRYPTION_TYPES_SERVER=(DES40,RC4_40)
SQLNET.CRYPTO_SEED="Between Ten and Seventy Random Characters"

SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(MD5)
SQLNET.ENCRYPTION_TYPES_CLIENT=(DES40,RC4_40)

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 03:04:01 2011

Copyright (c) 1982, 2008, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> alter system set encryption key identified by "abcdefg12#";

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

$ ls -l /home/oracle/wallet/
total 4
-rw-rw-r-- 1 oracle dba 1573 Feb 14 03:04 ewallet.p12

$ sqlplus system/oracle1

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 03:08:04 2011

Copyright (c) 1982, 2008, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> alter table banking.customer modify (customer_name encrypt);

Table altered.

SQL> alter table banking.customer modify (customer_city encrypt);

Table altered.

SQL> desc banking.customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NUMBER
CUSTOMER_NAME VARCHAR2(30) ENCRYPT
CUSTOMER_TYPE VARCHAR2(3)
CUSTOMER_CITY VARCHAR2(20) ENCRYPT
CUSTOMER_COUNTRY VARCHAR2(2)

SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS

SQL> alter table BANKING.CUSTOMER move;

Table altered.

SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS

SQL> alter system flush buffer_cache;

System altered.

SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS

SQL> select tablespace_name from dba_tables where table_name='CUSTOMER';

TABLESPACE_NAME
------------------------------
BANKING01

SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS

SQL> create table banking.customer_2 tablespace banking01
2 as select * from banking.customer;

Table created.

SQL>
SQL> alter table banking.customer_2 modify (customer_name encrypt);

Table altered.

SQL> alter table banking.customer_2 modify (customer_city encrypt);

Table altered.

SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS
TEMASEK HOLDINGS

SQL> alter table banking.customer_2 move tablespace banking02;

Table altered.

SQL> desc banking.customer_2;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NUMBER
CUSTOMER_NAME VARCHAR2(30) ENCRYPT
CUSTOMER_TYPE VARCHAR2(3)
CUSTOMER_CITY VARCHAR2(20) ENCRYPT
CUSTOMER_COUNTRY VARCHAR2(2)

SQL> ! strings /u01/oracle/oradata/db01/banking01.dbf|grep TEMASEK
TEMASEK HOLDINGS
TEMASEK HOLDINGS

SQL> ! strings /u01/oracle/oradata/db01/banking02.dbf|grep TEMASEK

SQL>

SQL> select * from dba_encrypted_columns;

OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL
------------------------------ ------------------------------ ------------------------------ ----------------------------- ---
BANKING CUSTOMER_2 CUSTOMER_NAME AES 192 bits key YES
BANKING CUSTOMER_2 CUSTOMER_CITY AES 192 bits key YES
BANKING CUSTOMER CUSTOMER_NAME AES 192 bits key YES
BANKING CUSTOMER CUSTOMER_CITY AES 192 bits key YES