Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 26, 2009

Oracle 11g's Transparent Data Encryption Security Feature

By Jim Czuprynski

Synopsis. 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.

The prior 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.

Fortunately, 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,

That’s 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

As I 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.)

Listing 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 directive.)

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 to AES256). The REKEY 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 most efficiently.

Note, 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.

Listing 3.2 shows several examples of these commands.

The Whole Nine Yards: Encrypting Tablespaces

While 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.

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 <index name> REBUILD ONLINE command.

Just 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.

However, 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.

To 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 files.

DICOM: An Industry Standard for Storing Patient Medical Imaging and Metadata

The American 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.)

Oracle 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.

Oracle 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 within ORDDicom 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 source file.

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 and ORDImage 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 datatype offers:

  • First, I’ll 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 150x200 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 column.

Listing 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 and THUMBPRINT columns as well as the corresponding output.

Verifying the images stored within a column with an ORDDicom or ORDImage 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.


Oracle 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

While 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 Developer’s Guide

B28412-01 Oracle Database 11g Multimedia Servlets and JSP API Reference

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 Reference

B28419-02 Oracle Database 11g PL/SQL Packages and Types Reference

B28424-03 Oracle Database 11g Advanced Application Developer’s Guide

B28530-03 Oracle Database 11g Advanced Security Administrator's Guide

Also, the following MetaLink documentation helps clarify these features:

386341.1 11g New Feature: Transparent Data Encryption at Tablespace Level

Finally, 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.

» See All Articles by Columnist Jim Czuprynski

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM