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