Custom Database Security Layer

White Paper


It is becoming increasingly necessary to encrypt the data in the databases to prevent unauthorized access to sensitive data from database administrators, OS administrators, or in case the disk or tape backups are stolen. Many times, it is not possible to modify to the existing application and the encryption needs to be done solely within the database, transparent to the applications and users. This paper will discuss the details of how this can be achieved.


The encryption will work by creating additional layer of objects in the database to encrypt/decrypt on the fly and hide the encrypted data in the tables only accessible by these newly created objects.


Two functions to encrypt and decrypt the data need to be created. The input parameter for the encrypting function should be a character string, varchar2 for example, and the output should be a raw. The decrypting function will have raw as an input parameter and a character string as an output. The functions can utilize any encrypting/decrypting mechanisms available to the database. For Oracle it can be:

For security purpose, the source code of the functions should be hidden within the database. There are several database utilities that can scramble the source code. Oracle, for example, uses WRAP utility which would encrypt the source code of any oracle object. The decryption and encryption of sensitive data will occur only for authorized sessions. All other sessions will receive an error or the sensitive data will be returned as NULL or “N/A” depending on the business requirements.


There are multiple ways for defining authorized sessions. It can be done via determination of the origination of the session or based on the value the session sets at the initiation. Both of the methods are discussed in detail below.


One methodology is for the application to populate global temporary table at the application startup with the encryption key. Global temporary table is a virtual table and does not reside on disk. It resides only in memory during the duration of the session and is available only to that session. That would mean that only the sessions started by the application would be able to decrypt/encrypt the data and the encryption key would have be stored in the application configuration properties file.  All other sessions started manually would not be considered authorized.  This method, however, would require application configuration changes, which may not be possible.


The other methodology is based on limiting the authorized sessions to authorized list of clients and users.  It is based on the IP address and/or other Sessions context based information available to the database. Only sessions from certain IP addresses, hosts, and/or with certain OS usernames would be able to encrypt/decrypt the data. The full list of available Session information is below:

The source code of the encrypt/decrypt function will have a list of the authorized clients. However, since the source code of the function itself is encrypted this does not post a security risk.


All the tables with the columns needing the encryption/decryption should be renamed into the raw tables and views referencing these tables should be created to replace them. Therefore, the application would still be referring to the objects with the same name, except now they would be views and not tables. The views will have function calls to decrypt the data. Consider the example below, where the field SS_NUM needs to be encrypted.


Initial Table:

Name                                      Null?    Type
 -------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(100)
 SS_NUM                                             VARCAHR2(100)

After implementing the transparent encryption:

Name                                      Null?    Type
 -------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(100)
 SS_NUM                                             RAW(128)



The population of the tables would be based on triggers created on the above views. Three triggers need to be created for every view, for INSERT, UPDATE, and DELETE. The triggers will have the function call to encrypt the data and will populate the raw tables. The function to encrypt the data should produce an error if it is called by an unauthorized session and the trigger should return this error to the user.


Now you know more about the tools and technologies you can utilize in encrypting sensitive data. Hopefully the information presented here will help you achieve the security goals of your organization and will make your tasks easier.

If you have feedback about this document, or would like to discuss the details of implementing the database transparent security layer, please send e-mail to:

Copyright © 2007, All rights reserved by RudnikConsulting Inc