/*
|| Oracle 10g Security Listing 4
||
|| Demonstrates new Oracle 10gR2 Security features, including:
|| - Writing out external tables using the ORACLE_DATAPUMP access method
|| - Encrypting an Oracle 10g DataPump export file
|| - Encrypting Oracle 10g RMAN backup files
|| - Using RMAN encrypted backup files during media recovery
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g
|| security enhancements, and it should be carefully proofread before
|| executing it against any existing Oracle database to insure that no
|| potential damage can occur.
*/

/* 
|| Listing 4.1: Writing out an external table using the ORACLE_DATAPUMP
||              access method
*/

-----
-- Open the security wallet
-----
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "Check_M8";

-----
-- Create a DIRECTORY object for storing the physical files for all
-- external tables
-----
DROP DIRECTORY external_dir;
CREATE DIRECTORY external_dir 
  AS '/home/oracle/_dba/extfiles';

GRANT READ, WRITE ON DIRECTORY external_dir TO PUBLIC;

-----
-- Create an external table using CTAS and the ORACLE_DATAPUMP
-- access method. Be sure to connect as HR within SQL*Plus!
-----
SQL> CONNECT hr/hr;

DROP TABLE hr.xt_employee_info PURGE;
CREATE TABLE hr.xt_employee_info (
     employee_id
    ,last_name
    ,first_name
    ,salary
    ,marital_status
    ,payroll_id
    ,termination_rsn
    ,termination_dt
    )
    ORGANIZATION EXTERNAL (
        TYPE ORACLE_DATAPUMP
        DEFAULT DIRECTORY external_dir
        LOCATION ('employee_info.txt')
    )
    AS
    SELECT 
        E.employee_id
       ,E.last_name
       ,E.first_name
       ,E.salary
       ,ES.marital_status
       ,ES.socsecnbr
       ,ES.termination_rsn
       ,ES.termination_dt
      FROM 
        hr.employees E
       ,hr.employee_secured ES     
     WHERE E.employee_id = ES.employee_id
     ORDER BY E.employee_id
;    

-----
-- Now, recreate the same external table, but apply encryption
-- to selected columns of the table. Encryption will be applied 
-- using the currently open Oracle Wallet.
-- NOTE: If SQL*Plus returns:
--   ORA-00604: error occurred at recursive SQL level 1
--   ORA-28336: cannot encrypt SYS owned objects
-- it's most likely because the SQL statements were run from the
-- SYS user account! 
-----
DROP TABLE hr.xt_employee_info PURGE;
CREATE TABLE hr.xt_employee_info (
     employee_id
    ,last_name
    ,first_name
    ,salary ENCRYPT
    ,marital_status
    ,payroll_id ENCRYPT
    ,termination_rsn ENCRYPT
    ,termination_dt ENCRYPT
    )
    ORGANIZATION EXTERNAL (
        TYPE ORACLE_DATAPUMP
        DEFAULT DIRECTORY external_dir
        LOCATION ('secured_employee_info.txt')
    )
    AS
    SELECT 
        E.employee_id
       ,E.last_name
       ,E.first_name
       ,E.salary
       ,ES.marital_status
       ,ES.socsecnbr
       ,ES.termination_rsn
       ,ES.termination_dt
      FROM 
        hr.employees E
       ,hr.employee_secured ES     
     WHERE E.employee_id = ES.employee_id
     ORDER BY E.employee_id
;    

-----
-- Can the external table's encrypted data be viewed at all?
-----

>>> View the external table data for the unencrypted table
>>> View the external table data for the encrypted table

/* 
|| Listing 4.2: Encrypting an Oracle 10g DataPump export file
*/

-----
-- DataPump Export command:
-----
expdp system/oracle PARFILE=EncryptedExport.dpectl

-----
-- Contents of EncryptedExport.dpectl parameter file:
-----
JOB_NAME=tde_export
DIRECTORY=external_dir
DUMPFILE=tde_export.dmp
TABLES=hr.employees,hr.employee_secured,hr.xt_employee_info
ENCRYPTION_PASSWORD=Check_M8

-----
-- Results of attempted DataPump Export operation:
-- 1.) Table HR.EMPLOYEES will be exported, but none of its data will
--     be encrypted in the dump set because none of its columns are
--     encrypted in the database. 
-- 2.) Table HR.EMPLOYEE_SECURED will be exported successfully and
--     its encrypted columns will remain encrypted in the dump set.
-- 3.) External table HR.XT_EMPLOYEE_INFO won't be exported, because
--     Oracle DataPump prohibits the export of an external table.
-----

Export: Release 10.2.0.1.0 - Production on Wednesday, 17 January, 2007 17:12:11
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."TDE_EXPORT":  system/******** PARFILE=EncryptedExport.dpectl 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39214: Data Pump does not support external tables with encrypted columns. TABLE:"HR"."XT_EMPLOYEE_INFO" will not be exported
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMPLOYEES"                            15.76 KB     107 rows
. . exported "HR"."EMPLOYEE_SECURED"                     7.320 KB       5 rows
Master table "SYSTEM"."TDE_EXPORT" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.TDE_EXPORT is:
  /home/oracle/_dba/extfiles/tde_export.dmp
Job "SYSTEM"."TDE_EXPORT" completed with 1 error(s) at 17:12:38

/* 
|| Listing 4.3: Importing an encrypted Oracle 10g DataPump export file
*/

-----
-- Creation of NEWHR user account (to enable remapping during DataPump Import)
-----
CREATE USER newhr IDENTIFIED BY newhr;
GRANT CONNECT TO newhr;
GRANT UNLIMITED TABLESPACE TO newhr;

-----
-- DataPump Import command:
-----
impdp system/oracle PARFILE=EncryptedImport.dpictl

-----
-- Contents of EncryptedImport.dpictl parameter file:
-----
JOB_NAME=tde_import
DIRECTORY=external_dir
DUMPFILE=tde_export.dmp
TABLES=hr.employees,hr.employee_secured
EXCLUDE=TRIGGER
EXCLUDE=CONSTRAINT
ENCRYPTION_PASSWORD="C1D8H3H4A6E1D8"
REMAP_SCHEMA=hr:newhr

-----
-- Open the security wallet
-----
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "Check_M8";


-- Results of attempted DataPump Import operation:
-- 1.) Table HR.EMPLOYEES will be imported and remapped successfully.
-- 2.) Table HR.EMPLOYEE_SECURED will be imported successfully and
--     its encrypted columns will remain encrypted in the new schema.
-----

Import: Release 10.2.0.1.0 - Production on Wednesday, 17 January, 2007 17:24:48
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."TDE_IMPORT" successfully loaded/unloaded
Starting "SYSTEM"."TDE_IMPORT":  system/******** PARFILE=EncryptedImport.dpictl 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "NEWHR"."EMPLOYEES"                         15.76 KB     107 rows
. . imported "NEWHR"."EMPLOYEE_SECURED"                  7.320 KB       5 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."TDE_IMPORT" successfully completed at 17:24:58

/* 
|| Listing 4.4: What RMAN encryption algorithms are available?
*/

SET PAGESIZE 50
SET LINESIZE 80
TTITLE 'Available RMAN Encryption Algorithms|(From V$RMAN_ENCRYPTION_ALGORITHMS)'
COL algorithm_name          FORMAT A08      HEADING 'Name'
COL algorithm_description   FORMAT A32      HEADING 'Description'
COL is_default              FORMAT A10      HEADING 'Default?'
COL restore_only            FORMAT A10      HEADING 'Restore|Only?'
SELECT
     algorithm_name
    ,algorithm_description
    ,is_default
    ,restore_only
  FROM v$rman_encryption_algorithms
 ORDER BY algorithm_name
;
TTITLE OFF

                      Available RMAN Encryption Algorithms
                      (From V$RMAN_ENCRYPTION_ALGORITHMS)
 
                                                     Restore
Name     Description                      Default?   Only?
-------- -------------------------------- ---------- ----------
AES128   AES 128-bit key                  YES        NO
AES192   AES 192-bit key                  NO         NO
AES256   AES 256-bit key                  NO         NO

/* 
|| Listing 4.5: Creating an RMAN encrypted backup set using TDE features
*/

-----
-- Back up the entire database in encrypted form. Note that the 
-- CONFIGURE command is used to activate encrypted backups and 
-- to override the default encryption algorithm (AES128)
-----
RMAN> RUN {
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES192';
}

RMAN> RUN {
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
}

Starting backup at 17-JAN-07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=149 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=5 recid=1 stamp=612117584
input archive log thread=1 sequence=6 recid=2 stamp=612117589
input archive log thread=1 sequence=7 recid=3 stamp=612117592
input archive log thread=1 sequence=8 recid=4 stamp=612117593
input archive log thread=1 sequence=9 recid=5 stamp=612120575
channel ORA_DISK_1: starting piece 1 at 17-JAN-07
channel ORA_DISK_1: finished piece 1 at 17-JAN-07
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_annnn_TAG20070117T172936_2txdc2qj_.bkp tag=TAG20070117T172936 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_01_17/o1_mf_1_5_2tx9fhy9_.arc recid=1 stamp=612117584
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_01_17/o1_mf_1_6_2tx9fo81_.arc recid=2 stamp=612117589
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_01_17/o1_mf_1_7_2tx9frvy_.arc recid=3 stamp=612117592
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_01_17/o1_mf_1_8_2tx9fst1_.arc recid=4 stamp=612117593
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_01_17/o1_mf_1_9_2txdbxs4_.arc recid=5 stamp=612120575
Finished backup at 17-JAN-07
 
Starting backup at 17-JAN-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/tbs_encrypted01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JAN-07
channel ORA_DISK_1: finished piece 1 at 17-JAN-07
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_nnndf_TAG20070117T172947_2txdcdck_.bkp tag=TAG20070117T172947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-JAN-07
channel ORA_DISK_1: finished piece 1 at 17-JAN-07
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_ncsnf_TAG20070117T172947_2txdgj9x_.bkp tag=TAG20070117T172947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 17-JAN-07
 
Starting backup at 17-JAN-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=10 recid=6 stamp=612120693
channel ORA_DISK_1: starting piece 1 at 17-JAN-07
channel ORA_DISK_1: finished piece 1 at 17-JAN-07
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_annnn_TAG20070117T173133_2txdgpp4_.bkp tag=TAG20070117T173133 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2007_01_17/o1_mf_1_10_2txdgnyc_.arc recid=6 stamp=612120693
Finished backup at 17-JAN-07

-----
-- Creating an RMAN encrypted backup for specific tablespaces
-----
RMAN> RUN {
# First, clear the current RMAN encryption settings ...
CONFIGURE ENCRYPTION FOR DATABASE CLEAR;
# ... then activate encryption for specific tablespaces
CONFIGURE ENCRYPTION FOR TABLESPACE example ON;
CONFIGURE ENCRYPTION FOR TABLESPACE tbs_encrypted ON;
BACKUP TABLESPACE example, tbs_encrypted;
}

old RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
RMAN configuration parameters are successfully reset to default value
 
tablespace EXAMPLE will be encrypted in future backup sets
new RMAN configuration parameters are successfully stored
 
tablespace TBS_ENCRYPTED will be encrypted in future backup sets
new RMAN configuration parameters are successfully stored
 
Starting backup at 17-JAN-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/tbs_encrypted01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JAN-07
channel ORA_DISK_1: finished piece 1 at 17-JAN-07
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_nnndf_TAG20070117T173748_2txdtfgf_.bkp tag=TAG20070117T173748 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 17-JAN-07

/* 
|| Listing 4.6: Creating a password-only mode RMAN encrypted backup set
*/

-----
-- Creating a password-controlled RMAN encrypted backup set:
-- 1.) First, close the security wallet from within SQL*Plus.
-- 2.) Next, clear all CONFIGUREd RMAN settings for backups.
-- 3.) Finally, set a password to encrypt the datafile and
--     create the RMAN backupset.
-----

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;

RMAN> RUN { 
CONFIGURE ENCRYPTION FOR DATABASE CLEAR;
CONFIGURE ENCRYPTION FOR TABLESPACE example CLEAR;
CONFIGURE ENCRYPTION FOR TABLESPACE tbs_encrypted CLEAR;
SET ENCRYPTION ON IDENTIFIED BY Check_M8 ONLY;
BACKUP AS COMPRESSED BACKUPSET TABLESPACE example, tbs_encrypted;
}

Starting backup at 17-JAN-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/tbs_encrypted01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JAN-07
channel ORA_DISK_1: finished piece 1 at 17-JAN-07
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_nnndf_TAG20070117T191249_2txldkw4_.bkp tag=TAG20070117T191249 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 17-JAN-07

/* 
|| Listing 4.7: Creating a dual-mode RMAN encrypted backup set
*/

-----
-- Creating a dual-mode RMAN encrypted backup. Since TDE will be
-- used if an Oracle Wallet is open anyway, all that's required
-- is the specification of the password
-----

RMAN> RUN {
CONFIGURE ENCRYPTION FOR DATABASE CLEAR;
SET ENCRYPTION ON IDENTIFIED BY Check_M8;
BACKUP DATABASE PLUS ARCHIVELOG;
}

/* 
|| Listing 4.8: Using RMAN encrypted backup files during media recovery
*/

-----
-- First, delete the target datafile, and then bounce the database
-----
$> rm -f /u01/app/oracle/oradata/orcl/tbs_encrypted01.dbf
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

-----
-- Now attempt to restore and recover that datafile. This will fail
-- because the password must be supplied to decrypt the backup set
-----
RMAN> RUN {
RESTORE DATAFILE 6 VALIDATE;
}

Starting restore at 17-JAN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
 
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_nnndf_TAG20070117T191249_2txldkw4_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/17/2007 19:20:42
ORA-19870: error reading backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_nnndf_TAG20070117T191249_2txldkw4_.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

-----
-- Once the encryption password is set properly, the datafile can
-- then be restored.
-----
RUN {
SET DECRYPTION IDENTIFIED BY Check_M8;
RESTORE DATAFILE 6 VALIDATE;
}

Starting restore at 17-JAN-07
using channel ORA_DISK_1
 
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_nnndf_TAG20070117T191249_2txldkw4_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2007_01_17/o1_mf_nnndf_TAG20070117T191249_2txldkw4_.bkp tag=TAG20070117T191249
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 17-JAN-07