Monday, February 14, 2011

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