/*
|| Oracle 11g SecureFiles Listing 3
||
|| Demonstrates Oracle Database 11g's expanded security features, including:
|| - Enabling encryption for SecureFile LOBs
|| - Creating an encrypted tablespace
|| - Applying security to complex LOB targets (e.g. DICOM)
|
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various examples of Oracle 11gR1's
|| new security and SecureFiles features, and they should be carefully proofread
|| before being executed against any existing Oracle database to avoid potential
|| damage!
*/
/*
|| Listing 3.1:
|| Enabling Transparent Data Encryption (TDE)
*/
-----
-- Add parameter settings in database server's SQLNET.ORA network configuration
-- file to enable encryption within an Oracle 11g database ...
-----
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/admin/orcl/wallet)
)
-----
-- ... then, activate encryption inside the target Oracle 11g database by opening
-- the wallet and setting the encryption key password.
-----
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "r3aL1y!T16ht";
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "r3aL1y!T16ht";
/*
|| Listing 3.2:
|| Applying TDE encryption to existing SecureFile LOBS
*/
-- Apply default encryption to a single SecureFile LOB
SQL> ALTER TABLE trbtkt.secure_tickets
MODIFY (document CLOB ENCRYPT);
-- Apply non-default AES 256-bit encryption to a single SecureFile LOB
SQL> ALTER TABLE trbtkt.secure_tickets
MODIFY (scrnimg CLOB ENCRYPT USING 'AES256');
-- "Rekey" encryption for a single SecureFile LOB
SQL> ALTER TABLE trbtkt.secure_tickets
MODIFY (scrnimg CLOB REKEY USING 'AES192');
-- Apply encryption to a single partition segment for a SecureFile LOB
SQL> ALTER TABLE trbtkt.secure_tickets
MODIFY PARTITION sts_open (LOB(document) (ENCRYPT));
-- Remove encryption for a single SecureFile LOB
SQL> ALTER TABLE trbtkt.secure_tickets
MODIFY (scrnimg CLOB DECRYPT);
/*
|| Listing 3.3:
|| Creating an encrypted tablespace for storage of sensitive information
*/
-----
-- Create a new tablespace for storage of DICOM metadata and medical images.
-- Note that all data stored within this tablespace will be encrypted by
-- default using the AES 256-bit encryption algorithm to insure that no
-- sensitive data is viewable without an open "wallet"
-----
DROP TABLESPACE patimages INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE patimages
DATAFILE '/u01/app/oracle/oradata/orcl/patimages01.dbf'
SIZE 64M REUSE
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT);
/*
|| Listing 3.4:
|| Creating encrypted SecureFile LOBs for storage of sensitive information
*/
-----
-- Create a new user (MIPS)
-----
DROP USER mips CASCADE;
CREATE USER mips
IDENTIFIED BY mips
DEFAULT TABLESPACE patimages
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON patimages;
GRANT CONNECT, RESOURCE TO mips;
GRANT EXECUTE ANY PROCEDURE to mips;
GRANT CREATE ANY DIRECTORY TO mips;
----
-- Create new directory objects for staging of external LOBs
-----
DROP DIRECTORY mips_imgs;
CREATE OR REPLACE DIRECTORY mips_imgs
AS '/home/oracle/dicom';
GRANT READ ON DIRECTORY mips_imgs TO mips;
-----
-- Create new table (MIPS.PATIENT_IMAGES) that uses SecureFile LOBS to store
-- DICOM (Digital Imaging for Communication of Medical Information) files
-- securely within encrypted LOB columns
-----
DROP TABLE mips.patient_images PURGE;
CREATE TABLE mips.patient_images (
patient_id NUMBER
,name VARCHAR2(30)
,ssn VARCHAR2(11)
,dob DATE
,dicom_image ORDSYS.ORDDICOM
,anonymous ORDSYS.ORDDICOM
,thumbprint ORDSYS.ORDIMAGE
)
TABLESPACE patimages
LOB(dicom_image.source.localData)
STORE AS SECUREFILE (
TABLESPACE patimages
DISABLE STORAGE IN ROW
DEDUPLICATE
COMPRESS HIGH
CACHE READS
)
LOB(anonymous.source.localData)
STORE AS SECUREFILE (
TABLESPACE patimages
DISABLE STORAGE IN ROW
DEDUPLICATE
COMPRESS HIGH
CACHE READS
)
LOB(thumbprint.source.localData)
STORE AS SECUREFILE (
TABLESPACE patimages
DISABLE STORAGE IN ROW
DEDUPLICATE
COMPRESS HIGH
CACHE READS
)
;
-- Comments
COMMENT ON TABLE mips.patient_images
IS 'Contains Patient metadata and DICOM images';
COMMENT ON COLUMN mips.patient_images.patient_id
IS 'Unique identifier for a Patient';
COMMENT ON COLUMN mips.patient_images.name
IS 'Patient Name';
COMMENT ON COLUMN mips.patient_images.ssn
IS 'Patient Social Security Number';
COMMENT ON COLUMN mips.patient_images.dob
IS 'Patient Date of Birth';
COMMENT ON COLUMN mips.patient_images.dicom_image
IS 'DICOM LOB';
COMMENT ON COLUMN mips.patient_images.anonymous
IS 'Anonymous DICOM LOB';
COMMENT ON COLUMN mips.patient_images.thumbprint
IS 'Thumbprint of DICOM image';
-- Create indexes and constraints
CREATE UNIQUE INDEX mips.patient_images_pk_idx
ON mips.patient_images(patient_id)
TABLESPACE patimages;
ALTER TABLE mips.patient_images
ADD CONSTRAINT patient_images_pk
PRIMARY KEY (patient_id);
/*
|| Listing 3.5:
|| Using SQL*Loader to load DICOM raw files directly into an Oracle 11g database
*/
$> sqlldr system/oracle CONTROL=LoadDICOMFromFile.sqlparms
-----
-- SQL*Loader Parameter File: LoadDICOMFromFile.sqlparms
-- Purpose: This parameter file will load DICOM data directly from DICOM files
-- into column DICOM_IMAGE of table MIPS.PATIENT_IMAGES. It also controls
-- the initialization of columns ANONYMOUS (which holds "anonymized" DICOM
-- data) and THUMBPRINT (which holds a thumbprint image of the DICOM image
-- itself).
-----
LOAD DATA
INFILE *
INTO TABLE mips.patient_images
TRUNCATE
FIELDS TERMINATED BY WHITESPACE
OPTIONALLY ENCLOSED BY '"'
(
patient_id INTEGER EXTERNAL
,name CHAR
,ssn CHAR
,dob DATE "yyyy-mm-dd"
,fn FILLER CHAR
-----
-- Load column object MIPS.PATIENT_IMAGES.DICOM_IMAGE:
-- 1.) LOB attribute source.localData is loaded with the DICOM data.
-- 2.) Attribute srcType is set to "local".
-- 3.) Attribute updateTime is initialized to the current date.
-- 4.) LOB attribute extension is initialized with an empty LOB.
-----
,dicom_image COLUMN OBJECT (
source COLUMN OBJECT (
localData LOBFILE(fn) TERMINATED BY EOF
,srcType CONSTANT 'local'
,updateTime EXPRESSION "SYSDATE"
)
,extension LOBFILE(fn) TERMINATED BY EOF
DEFAULTIF dicom_image.source.srcType='local'
)
-----
-- Initialize (but do not load) column object MIPS.PATIENT_IMAGES.ANONYMOUS:
-- 1.) The LOB attributes source.localData and extension are initialized.
-- 2.) The srcType attribute is initialized to "local".
-- 3.) The localData LOB will hold the content for the DICOM data to be
-- made anonymous.
-- 4.) The srcType attribute is initialized to "local".
-- 5.) The extension LOB is an internal field used by ORDDICOM.
-----
,anonymous COLUMN OBJECT (
source COLUMN OBJECT (
localData LOBFILE(fn) TERMINATED BY EOF
DEFAULTIF anonymous.source.srcType='local'
,srcType CONSTANT 'LOCAL'
)
,extension LOBFILE(fn) TERMINATED BY EOF
DEFAULTIF dicom_image.source.srcType='local'
)
-----
-- Initialize (but do not load) column object MIPS.PATIENT_IMAGES.THUMBPRINT:
-- 1.) LOB attribute extension is initalized with an empty LOB.
-- 2.) LOB attribute source.localData is initialized with an empty LOB.
-- 3.) The local attribute is initialized to "1".
-----
,thumbprint COLUMN OBJECT (
source COLUMN OBJECT (
localData LOBFILE(fn) TERMINATED BY EOF
DEFAULTIF thumbprint.source.local=X'1'
,local CONSTANT 1
)
)
)
BEGINDATA
101 "Ames, Aldritch" 322-51-1111 1907-12-01 CT-MONO2-16-ankle.dcm
202 "Barry, Dave" 345-21-2222 1968-12-24 OT-MONO2-8-colon.dcm
303 "Colson, Charles" 342-43-3333 1931-11-03 MR-MONO2-8-16x-heart.dcm
404 "Dean, John" 322-09-4444 1942-02-18 NM-MONO2-16-13x-heart.dcm
505 "Ehrlichman, John" 345-09-5555 1914-08-01 US-PAL-8-10x-echo.dcm
606 "Haldeman, Robert" 322-18-6666 1918-11-11 US-RGB-8-esopecho.dcm
>>> SQL*Loader results:
SQL*Loader: Release 11.1.0.6.0 - Production on Sat Mar 14 13:00:40 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Control File: LoadDICOMFromFile.sqlparms
Data File: LoadDICOMFromFile.sqlparms
Bad File: LoadDICOMFromFile.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table MIPS.PATIENT_IMAGES, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PATIENT_ID FIRST * WHT O(") CHARACTER
NAME NEXT * WHT O(") CHARACTER
SSN NEXT * WHT O(") CHARACTER
DOB NEXT * WHT O(") DATE yyyy-mm-dd
FN NEXT * WHT O(") CHARACTER
(FILLER FIELD)
DICOM_IMAGE DERIVED * COLUMN OBJECT
*** Fields in DICOM_IMAGE
SOURCE DERIVED * COLUMN OBJECT
*** Fields in DICOM_IMAGE.SOURCE
LOCALDATA DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field FN
SRCTYPE CONSTANT
Value is 'local'
UPDATETIME EXPRESSION
SQL string for column : "SYSDATE"
*** End of fields in DICOM_IMAGE.SOURCE
EXTENSION DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field FN
DEFAULT if DICOM_IMAGE.SOURCE.SRCTYPE = 0X6c6f63616c(character 'local')
*** End of fields in DICOM_IMAGE
ANONYMOUS DERIVED * COLUMN OBJECT
*** Fields in ANONYMOUS
SOURCE DERIVED * COLUMN OBJECT
*** Fields in ANONYMOUS.SOURCE
LOCALDATA DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field FN
DEFAULT if ANONYMOUS.SOURCE.SRCTYPE = 0X6c6f63616c(character 'local')
SRCTYPE CONSTANT
Value is 'LOCAL'
*** End of fields in ANONYMOUS.SOURCE
EXTENSION DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field FN
DEFAULT if DICOM_IMAGE.SOURCE.SRCTYPE = 0X6c6f63616c(character 'local')
*** End of fields in ANONYMOUS
THUMBPRINT DERIVED * COLUMN OBJECT
*** Fields in THUMBPRINT
SOURCE DERIVED * COLUMN OBJECT
*** Fields in THUMBPRINT.SOURCE
LOCALDATA DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field FN
DEFAULT if THUMBPRINT.SOURCE.LOCAL = 0X01(character '')
LOCAL CONSTANT
Value is '1'
*** End of fields in THUMBPRINT.SOURCE
*** End of fields in THUMBPRINT
Table MIPS.PATIENT_IMAGES:
6 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 83456 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 6
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Sat Mar 14 13:00:40 2009
Run ended on Sat Mar 14 13:00:49 2009
Elapsed time was: 00:00:09.65
CPU time was: 00:00:00.61
/*
|| Listing 3.6:
|| Creating and storing "anonymized" patient information and thumbprint images
|| from previously-loaded ORDDicom objects:
|| 1.) Load the DICOM data model into memory
|| 2.) Translate the original DICOM file's metadata into corresponding elements
|| 3.) Build a thumbnail image in JPEG format based on the original DICOM file's images
|| 4.) Generate an "anonymous version" of the DICOM file
|| 5.) Save this new information into their corresponding SecureFile LOBs
*/
SET SERVEROUTPUT ON
DECLARE
dcm_row ORDSYS.ORDDICOM;
BEGIN
-- Load the DICOM data model
ord_dicom.setDatamodel;
-- Loop over all rows in the medical image table
FOR dcm_row IN (
SELECT
patient_id
,dicom_image
,anonymous
,thumbprint
FROM mips.patient_images
FOR UPDATE
)
LOOP
BEGIN
-----
-- Initialize the DICOM SecureFile LOB column attributes via the setProperties procedure
-----
dcm_row.dicom_image.setProperties();
-----
-- Build a thumbnail image in JPEG format based on the original DICOM image
-----
dcm_row.dicom_image.processCopy('fileFormat=JPEG fixedScale=150,200', dcm_row.thumbprint);
-----
-- Generate an "anonymous version" of the DICOM object via the makeAnonymous() function
-----
dcm_row.dicom_image.makeAnonymous(genUID(dcm_row.patient_id), dcm_row.anonymous);
-----
-- Save this newly-processed information back into their corresponding SecureFile LOBs
-----
UPDATE mips.patient_images
SET dicom_image = dcm_row.dicom_image
,anonymous = dcm_row.anonymous
,thumbprint = dcm_row.thumbprint
WHERE patient_id = dcm_row.patient_id;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error processing image for Patient ID #' || dcm_row.patient_id);
END;
END LOOP;
COMMIT;
END;
/
/*
|| Listing 3.7:
|| Viewing the metadata that's been loaded into ORDDICOM and ORDIMAGE objects
*/
-----
-- Display selected contents of:
-- 1.) Normal datatypes
-- 2.) ORDDICOM datatype
-- 3.) ORDIMAGE datatype
-- in table MIPS.PATIENT_IMAGES
-----
SET LINESIZE 80
SET PAGESIZE 80
TTITLE 'Sample Patient Metadata|(from MIPS.PATIENT_IMAGES)'
COLUMN patient_id FORMAT 99999 HEADING 'Pat|ID'
COLUMN name FORMAT A20 HEADING 'Patient Name' WRAP
COLUMN ssn FORMAT A11 HEADING 'Patient SSN'
COLUMN di_sop_uid FORMAT A30 HEADING 'DICOM Image SOP UID' WRAP
COLUMN tp_len FORMAT 99999 HEADING 'Thumb|Print|Image|Size'
SELECT
PI.patient_id
,PI.name
,PI.ssn
,PI.dicom_image.sop_instance_uid AS di_sop_uid
,PI.thumbprint.getcontentlength() AS tp_len
FROM mips.patient_images PI
ORDER BY PI.patient_id
;
TTITLE OFF
Sun Mar 15 page 1
Sample Patient Metadata
(from MIPS.PATIENT_IMAGES)
Thumb
Print
Pat Image
ID Patient Name Patient SSN DICOM Image SOP UID Size
------ -------------------- ----------- ------------------------------ ------
101 Ames, Aldritch 322-51-1111 1.2.840.113619.2.1.2411.103115 5034
2382.365.1.736169244
202 Barry, Dave 345-21-2222 1.3.46.670589.17.1.7.0.16 5677
303 Colson, Charles 342-43-3333 999.999.2.19960619.163000.1.10 3648
3
404 Dean, John 322-09-4444 2.16.840.1.113662.5.8796818449 1596
476.121423489.1.1.3101.5309511
143
505 Ehrlichman, John 345-09-5555 999.999.133.1996.1.1800.1.6.25 5252
606 Haldeman, Robert 322-18-6666 999.999.2.19941105.112000.2.10 4683
7