Oracle ASO: TDE with tablespace level encryption

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 14 03:37:24 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>  set echo on
SQL> @/home/oracle/aso_scripts/create_enc_tablespace.sql
SQL> set echo on
SQL> spool create_enc_tablespace.out
SQL> 
SQL> drop tablespace example_11g_enc_tablespace including contents and datafiles;

Tablespace dropped.

SQL> 
SQL> create tablespace example_11g_enc_tablespace
  2  datafile '/u01/oracle/oradata/db01/example_11g_enc.dbf'
  3  size 50m
  4  encryption using 'AES192'
  5  default storage(encrypt)
  6  /

Tablespace created.

SQL> set echo off
SQL> @@/home/oracle/aso_scripts/create_banking_schema.sql

SQL> 
SQL> connect dvacctmgr/oracle12#
Connected.
SQL> 
SQL> drop user banking cascade;

User dropped.

SQL> drop user banking_dba_sr;

User dropped.

SQL> drop user banking_dba_jr;

User dropped.

SQL> 
SQL> connect / as sysdba
Connected.
SQL> 
SQL> drop tablespace banking02 including contents and datafiles;

Tablespace dropped.

SQL> create tablespace banking02
  2  datafile '/u01/oracle/oradata/db01/banking02.dbf' size 1m;

Tablespace created.

SQL> 
SQL> drop tablespace banking01 including contents and datafiles;

Tablespace dropped.

SQL> create tablespace banking01
  2  datafile '/u01/oracle/oradata/db01/banking01.dbf'
  3  size 1m;

Tablespace created.

SQL> 
SQL> connect dvacctmgr/oracle12#
Connected.
SQL> 
SQL> create user banking identified by oracle1
  2  default tablespace banking01;

User created.

SQL> create user banking_dba_sr identified by oracle1;

User created.

SQL> create user banking_dba_jr identified by oracle1;

User created.

SQL> 
SQL> connect / as sysdba
Connected.
SQL> grant dba to banking,banking_dba_sr,banking_dba_jr;

Grant succeeded.

SQL> 
SQL> connect banking/oracle1
Connected.
SQL> alter session set nls_date_format = 'dd/mon/yyyy';

Session altered.

SQL> create table banking.customer (
  2          customer_id number,
  3          customer_name varchar2(30),
  4          customer_type varchar2(3),
  5          customer_city varchar2(20),
  6          customer_country varchar2(2)
  7  );

Table created.

SQL> create table account (
  2          account_id number,
  3          account_type_code varchar(4),
  4          customer_id number ,
  5          block_credit char(1),
  6          block_debit char(1),
  7          date_opened date,
  8          date_closed date
  9  );

Table created.

SQL> create table account_balance (
  2          account_id number,
  3          account_bal_date date,
  4          bal_avail_adj number,
  5          bal_avail_closing number
  6  );

Table created.

SQL> 
SQL> insert into customer values(101,'HERTZ CORPORATION','LO','BERLIN','DE');

1 row created.

SQL> insert into customer values(102,'SUNGARD DATA SYSTEMS','GL','NEW YORK','US');

1 row created.

SQL> insert into customer values(103,'TEMASEK HOLDINGS','GL','SINGAPORE','SG');

1 row created.

SQL> insert into customer values(104,'NORDIC TELEPHONE','GL','STOCKHOLM','SE');

1 row created.

SQL> insert into customer values(105,'ORACLE CORPORATION','GL','REDWOOD SHORES','US');

1 row created.

SQL> insert into customer values(106,'QWEST COMMUNICATIONS','GL','DENVER','US');

1 row created.

SQL> insert into customer values(107,'OLD MUTUAL PRC','GL','LONDON','UK');

1 row created.

SQL> insert into customer values(108,'FRESENIUS MED CARE','GL','LONDON','UK');

1 row created.

SQL> insert into customer values(109,'EMI GERMANY CORPORATION','LO','FRANKFURT','DE');

1 row created.

SQL> insert into customer values(110,'DAIMLER','GL','STUTTGART','DE');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> insert into account values(1001,'CORP',101,'N','N','11/JAN/1987',NULL);

1 row created.

SQL> insert into account values(1002,'CORP',102,'N','N','23/FEB/1992',NULL);

1 row created.

SQL> insert into account values(1003,'CORP',103,'Y','Y','01/JUL/1983','27/JUL/1984');

1 row created.

SQL> insert into account values(1004,'CORP',104,'N','N','30/JUL/1981',NULL);

1 row created.

SQL> insert into account values(1005,'CORP',105,'N','N','16/OCT/1999',NULL);

1 row created.

SQL> insert into account values(1006,'CORP',106,'N','N','04/DEC/1995',NULL);

1 row created.

SQL> insert into account values(1017,'ADJ',106,'N','N','20/JAN/2009',NULL);

1 row created.

SQL> insert into account values(1018,'CORP',107,'N','Y','21/AUG/2000','03/SEP/2002');

1 row created.

SQL> insert into account values(1221,'CORP',108,'N','N','28/SEP/2008',NULL);

1 row created.

SQL> insert into account values(1932,'CORP',109,'Y','N','03/NOV/2003',NULL);

1 row created.

SQL> insert into account values(2135,'CORP',102,'Y','Y','10/OCT/2006',NULL);

1 row created.

SQL> insert into account values(2218,'ADJ',102,'N','N','27/MAR/1988','10/DEC/2001');

1 row created.

SQL> insert into account values(6127,'ADJ',102,'Y','N','01/MAY/1996','22/APR/2005');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> insert into account_balance values(1001,'01-MAY-2006',121455.90,101440.01);

1 row created.

SQL> insert into account_balance values(1002,'01-JUL-2008',782000.00,780211.23);

1 row created.

SQL> insert into account_balance values(1003,'15-JUN-2003',978332.90,765232.00);

1 row created.

SQL> insert into account_balance values(1004,'19-APR-2009',850200.18,850200.18);

1 row created.

SQL> insert into account_balance values(1005,'28-OCT-2004',232900.10,120918.75);

1 row created.

SQL> insert into account_balance values(1006,'03-SEP-2007',496039.88,490190.59);

1 row created.

SQL> insert into account_balance values(1017,'22-JAN-1992',101900.00,100248.95);

1 row created.

SQL> insert into account_balance values(1018,'01-SEP-2008',768950.00,569122.30);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> set echo on
SQL> 
SQL> spool off


SQL> alter system flush buffer_cache;

System altered.

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

SQL> @/home/oracle/aso_scripts/show_banking_tables.sql 
SQL> set echo on
SQL> 
SQL> select substr(a.table_name,1,28) "TABLE",substr(b.tablespace_name,1,13) "TABLESPACE",
  2  substr(a.owner,1,10) "OWNER",
  3  b.encrypted "ENC?"
  4  from dba_tables a, dba_tablespaces b
  5  where a.tablespace_name=b.tablespace_name
  6  and owner in ('BANKING')
  7  order by 3,1,2
  8  /

TABLE                        TABLESPACE    OWNER      ENC
---------------------------- ------------- ---------- ---
ACCOUNT                      BANKING01     BANKING    NO
ACCOUNT_BALANCE              BANKING01     BANKING    NO
CUSTOMER                     BANKING01     BANKING    NO

SQL> set echo off
SQL> @/home/oracle/aso_scripts/move_banking_tables_to_11g_encrypted_tablespace.sql
SQL> 
SQL> connect / as sysdba
Connected.
SQL> 
SQL> alter table banking.account move tablespace example_11g_enc_tablespace;

Table altered.

SQL> alter table banking.account_balance move tablespace example_11g_enc_tablespace;

Table altered.

SQL> alter table banking.customer move tablespace example_11g_enc_tablespace;

Table altered.

SQL> 
SQL> set echo off
SQL> alter system flush buffer_cache;

System altered.

SQL> @/home/oracle/aso_scripts/show_banking_tables.sql 
SQL> 
SQL> select substr(a.table_name,1,28) "TABLE",substr(b.tablespace_name,1,13) "TABLESPACE",
  2  substr(a.owner,1,10) "OWNER",
  3  b.encrypted "ENC?"
  4  from dba_tables a, dba_tablespaces b
  5  where a.tablespace_name=b.tablespace_name
  6  and owner in ('BANKING')
  7  order by 3,1,2
  8  /

TABLE                        TABLESPACE    OWNER      ENC
---------------------------- ------------- ---------- ---
ACCOUNT                      EXAMPLE_11G_E BANKING    YES
ACCOUNT_BALANCE              EXAMPLE_11G_E BANKING    YES
CUSTOMER                     EXAMPLE_11G_E BANKING    YES

SQL> set echo off
SQL> ! strings /u01/oracle/oradata/db01/example_11g_enc.dbf|grep TEMASEK

SQL> set echo on
SQL> @/home/oracle/aso_scripts/show_encrypted_tablespaces.sql
SQL> desc v$encrypted_tablespaces;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TS#                                                NUMBER
 ENCRYPTIONALG                                      VARCHAR2(7)
 ENCRYPTEDTS                                        VARCHAR2(3)

SQL> 
SQL> select t.name "TSName", e.encryptionalg "Algorithm", d.file_name "File Name"
  2  FROM v$tablespace t, v$encrypted_tablespaces e, dba_data_files d
  3  WHERE t.ts# = e.ts# and t.name = d.tablespace_name;

TSName                         Algorit
------------------------------ -------
File Name
--------------------------------------------------------------------------------
EXAMPLE_11G_ENC_TABLESPACE     AES192
/u01/oracle/oradata/db01/example_11g_enc.dbf


SQL> 
SQL> select a.owner "Owner", a.table_name "Table Name", e.encryptionalg "Algorithm"
  2  FROM dba_tables a, v$encrypted_tablespaces e
  3  WHERE a.tablespace_name in (select t.name from v$tablespace t,
  4  v$encrypted_tablespaces e where t.ts# = e.ts#);

Owner                          Table Name                     Algorit
------------------------------ ------------------------------ -------
BANKING                        ACCOUNT                        AES192
BANKING                        CUSTOMER                       AES192
BANKING                        ACCOUNT_BALANCE                AES192