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