/*
|| 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