A crucial reason for upgrading the storage capabilities of Large Objects (LOBs)
is the ever-expanding requirements for data security within an Oracle 11g
database. This article – the last in this three-part series – investigates how
to extend Transparent Data Encryption (TDE) security features to LOBs and
tablespaces to secure data, and explains how these features enable improved
security for complex, unstructured data like Digital Imaging for Communication
of Medical Information (DICOM) objects.
article in this series focused on how Oracle 11g’s new SecureFile features like deduplication and
compression help constrain the size of LOBs when it’s most necessary and
desirable to save space within LOB segments and their corresponding
tablespaces. While these features certainly offer impressive storage and
performance improvements, they still contribute nothing to solve what’s perhaps
the most glaring omission of BasicFile LOBs: the ability to secure sensitive or confidential information
that’s stored within a large object.
Oracle 11g’s new SecureFile and
security features dovetail nicely with recently-elected United States President
Barack Obama’s published agenda. The White House’s directives on technology
directly state that the new administration plans to
Lower Health Care Costs by Investing in
Electronic Information Technology Systems: Use health information
technology to lower the cost of health care. Invest $10 billion a year over the
next five years to move the U.S. health care system to broad adoption of
standards-based electronic health information systems, including electronic
health records. (Copyright 2009, www.whitehouse.gov/technology)
a fortunate development for our nation’s Oracle DBAs and application
developers, because one of the most dramatic ways health care costs can be
reduced is by requiring insurance companies and healthcare providers to utilize
existing technology to “digitize” health information, including medical
records, images, and patient metadata.
Encrypting LOBs: Putting the “Secure” In SecureFiles
mentioned in the first
article in this series, Oracle Database 11g
is strategically positioned to facilitate storage of sensitive information within
SecureFile LOBs because they can now leverage the proven features of Transparent Data Encryption (TDE) that
were first released in Oracle Database 10gR2. TDE provides encryption
automatically via industry-accepted encryption algorithms (3DES168, AES128,
AES192, and AES256) at the column level.
Enabling Transparent Data Encryption. Before
I can utilize Transparent Data Encryption features, however, I need to set up
encryption on my database. Fortunately, this is even easier in Oracle Database 11g because now I only need to add the
appropriate configuration directives to my database’s network configuration
profile. (In prior Oracle database releases, the simplest way to set up this
“wallet” file was via the Oracle Wallet
Manager utility. For more information on how TDE is enabled in prior
releases, please see my article
on how to implement TDE in Oracle 10gR2.)
3.1 shows the lines I added to my database server’s SQLNET.ORA
network configuration file to enable creation of the default TDE PKI key file, ewallet.p12,
in the directory I specified, and how I then “opened the wallet” to enable encryption
inside my database with the ALTER SYSTEM SET ENCRYPTION KEY command.
Controlling SecureFile Encryption. Once the
TDE setup is complete, it’s relatively simple to enable encryption for a
SecureFile LOB. Just like enabling encryption for other datatypes in Oracle
tables, the ENCRYPT
directive tells Oracle to apply TDE encryption to an existing SecureFile LOB.
(Encryption can also be removed from a SecureFile LOB by specifying the DECRYPT
Changing the SecureFile Encryption Algorithm or Encryption
Key. Just as with other Oracle datatypes, the ALTER TABLE <table_name>
REKEY command can be used to change the current encryption algorithm (e.g. from its default of AES192
directive can also be used to re-encrypt
an existing SecureFile LOB if the TDE PKI key has been changed. Oracle will
apply encryption at the block level to ensure that re-encryption is performed
however, that the corresponding SecureFile LOB’s segment can be only be ALTERed
to enable or disable encryption
of LOBs contained within the same partition
(i.e. LOB segments can’t be REKEYed). This is because Oracle Database 11g applies the same encryption algorithm to
all SecureFile LOBs within the same LOB partition.
3.2 shows several examples of these commands.
The Whole Nine Yards: Encrypting Tablespaces
Oracle Database 10gR2 did expand
its encryption capabilities to any table or index in the database, it could
still be rather tedious to identify and isolate just those objects and columns
that required encryption. In many cases, it might have been easier to simply
apply encryption to a set of objects logically related within similar business functions – for example, all
tables and indexes that encapsulated confidential employee information in the
database. To make encryption simpler to implement in these cases, Oracle
Database 11g now makes it
possible to encrypt an entire tablespace.
encryption is still enabled at block level, but it cannot be activated for an
existing tablespace, so the Oracle DBA must first create a new tablespace and
enable encryption during its creation. Once the encrypted tablespace exists,
the DBA can execute the appropriate ALTER TABLE <table name>
MOVE commands to move tables to the encrypted tablespace. In similar
fashion, an existing index can be recreated in an encrypted tablespace using
the ALTER INDEX
REBUILD ONLINE command.
as with encrypted columns, the encryption wallet for the database must be
opened first before an encrypted tablespace can be created. The new ENCRYPTION
directive of the CREATE TABLESPACE command signals that the new
tablespace will now automatically apply the specified encryption algorithm to
all objects stored within. AES 128-bit encryption is the default encryption
algorithm, but any of the four standard encryption algorithms (3DES168, AES128,
AES192 and AES256) may be applied. Best of all, an encrypted tablespace can be
transported to a different Oracle 11g
database as long as both the source and target database servers have the same endianness
and share the same encryption wallet.
note that temporary tablespace and UNDO tablespaces are not permitted to use
this encryption method; likewise, the source data for external tables and
external LOBs (i.e. BFILEs) cannot be encrypted, either. Finally, since
encryption keys are applied at the individual table level, there is no way to
rekey globally the encrypted objects stored within an encrypted tablespace, but
the same method to initially encrypt a tablespace could be employed to
accomplish a rekeying operation.
illustrate the features of encrypted tablespaces, I’ve created a new tablespace
named PATIMAGES that employs AES 256-bit encryption using
the code shown in Listing 3.3. I’ll
utilize this new tablespace as a container for a new table that’s going to
contain sensitive medical information as part of my final discussion on SecureFiles:
the ability to store medical images and
related information securely within an Oracle 11g database using data sourced from DICOM
DICOM: An Industry Standard for Storing Patient Medical Imaging and Metadata
College of Radiology (ACR) and the National Electrical Manufacturers
Association (NEMA) collaborated in 1993 to formalize the Digital Imaging and Communications In Medicine
(DICOM) standard for medical imaging devices and applications, and it’s become
one of the most common standards for storage and transmission of radiological
images. While Oracle Database 10gR2
fully supported the DICOM 3.0 standard, it wasn’t until Oracle Release 11g that methods existed to guarantee that
DICOM data could be encrypted and stored in a secure fashion within an Oracle
database. (I don’t know about you, but I’d like to know that my medical records
and pictures of my innards are kept securely under lock and key, or at least as
encrypted bits, thank you very much.)
Database 11g focuses on providing
efficient storage of DICOM images by extending the capabilities of the original
Oracle 10gR2 ORDImage
object type to include a new ORDDicom object type. Since ORDDicom
objects can be stored within SecureFile LOBs, this provides the ability to
compress, deduplicate, and encrypt both the images
stored within DICOM files as well as the related metadata that doctors, universities and hospitals use to
evaluate patient information, especially for diagnosis and research.
10gR2 provided the ability to
dump patient information into Extensible
Markup Language (XML) documents, but the new ORDDicom
object extends these capabilities by making it possible to query directly
against patient metadata stored with an Oracle 11g database. This means that it’s easier than ever to search
for specific patient information using advanced indexing features like the new XMLIndex
datatype. In addition, Oracle 11g
makes it possible to create, store, present,
and search thumbprints of the data images stored
objects for easier selection of the appropriate patient information. The ORDDicom
data model also helps guarantee patient
confidentiality because it provides methods to “anonymize” private
information stored within patient metadata that’s been loaded from a DICOM
Creating DICOM Objects. To demonstrate how
these powerful new features take advantage of Oracle 11g’s enhanced SecureFile encryption
capabilities, I’ve created a new schema and created a new table, MIPS.PATIENT_IMAGES,
that I’ll use as a target for storing DICOM metadata and images within columns
that use ORDDicom
datatypes. Column DICOM_IMAGE will store DICOM information loaded
directly from corresponding source files, while column ANONYMOUS
will store “anonymized” patient data and column THUMBPRINT will store a
thumbprint image derived from the first image in each DICOM file. Listing 3.4
shows the DDL I’ve used to create this new table.
Populating ORDDicom Objects from DICOM Sources.
Now that I’ve constructed an appropriate repository for my DICOM information,
I’ll employ SQL*Loader to extract and format the patient metadata and images
directly from DICOM sources. For my source data, I’ll use several sample DICOM files that I downloaded from Sebastien
Barre’s excellent DICOM web site.
(Names and other patient data are entirely fictitious and are used only to
demonstrate these concepts.) Listing 3.5
shows the SQL*Loader parameter file used to initialize, format and load the
DICOM data from these files directly into the ORDDicom and ORDImage
datatype columns, as well as the results of invoking SQL*Loader to perform this
initial data load.
Generating Thumbprint Images and “Anonymized” Metadata.
To complete the population of the ANONYMOUS and THUMBPRINT
columns, I’ll need to apply some pretty slick transformation methods that the ORDDicom
load the ORDDicom object model by calling its setModel method. This object model needs to be loaded before
any other methods can be employed.
I’ll then use the processCopy method to generate a
150×200 JPEG image and store it in the THUMBPRINT column.
Finally, I’ll employ the makeAnonymous method to create an
anonymous version of the DICOM_IMAGE column’s data and store it in the ANONYMOUS
3.6 shows how to implement these methods against all the
rows that were initially loaded into the MIPS.PATIENT_IMAGES table.
Using ORDDicom and ORDImage Column Contents.
Finally, I’m ready to peruse the DICOM information that’s been loaded
successfully into these DICOM columns. Fortunately, the ORDDicom
datatype makes this extremely simple because it provides several methods to
query patient metadata attributes directly. Listing 3.7
shows a sample query I used to display several of these attributes directly
from the DICOM_IMAGE
columns as well as the corresponding output.
the images stored within a column with an ORDDicom
datatype is a little trickier because it requires an interface to a “viewer”
application to see the images. Since these data are stored using
industry-standard image formats (e.g. JPEG, TIF, PNG), however, there are
numerous free viewers available to display their contents.
Database 11g’s new SecureFile
features dramatically expand the capabilities for storing large objects within
an Oracle database with greater security and efficiency than ever before. The
ability to compress and deduplicate SecureFILE LOBs offers
incredible space savings during their storage, and the extension of Transparent Data Encryption features to
SecureFile LOBS insures that sensitive or confidential information can be
stored securely within an Oracle 11g
database. These features enable Oracle Database 11g as a prime candidate for implementing new U.S.
federal government initiatives for storing hospital, patient, and medical
metadata within a central database.
References and Additional Reading
I’m hopeful that I’ve given you a thorough grounding in the technical aspects
of the features I’ve discussed in this article, I’m also sure that there may be
better documentation available since it’s been published. I therefore strongly
suggest that you take a close look at the corresponding Oracle documentation on
these features to obtain crystal-clear understanding before attempting to
implement them in a production environment. Please note that I’ve drawn upon
the following Oracle Database 11g
documentation for the deeper technical details of this article:
B28279-02 Oracle Database 11g New Features Guide
B28320-01 Oracle Database 11g Reference Guide
B28393-03 Oracle Database 11g SecureFiles and Large Objects
B28412-01 Oracle Database 11g Multimedia Servlets and JSP API
B28413-01 Oracle Database 11g Multimedia Java API Reference
B28415-02 Oracle Database 11g Multimedia User’s Guide
B28416-02 Oracle Database 11g Multimedia DICOM Developer’s Guide
B28417-01 Oracle Database 11g Multimedia DICOM Java API
B28419-02 Oracle Database 11g PL/SQL Packages and Types
B28424-03 Oracle Database 11g Advanced Application Developer’s
B28530-03 Oracle Database 11g Advanced Security Administrator’s
the following MetaLink documentation helps clarify these features:
386341.1 11g New Feature: Transparent Data Encryption at
Sebastien Barre’s excellent web site
on DICOM offers some great information, including an open-source DICOM file
viewer called DICOM2 as well as a cornucopia of links to myriad other DICOM and
medical imaging web sites.