Overview

This guide is targeted for web application developers and describes how to programmatically encrypt sensitive data for storage in SQL databases. Specifically, it describes how to accomplish this in Oracle, using Oracle packages for handling encryption and decryption.

Considerations

Before starting to write your application code, there are a few things to consider.

When to Encrypt Data

First, do you really need to encrypt the data? All databases provide the ability to restrict access to data. That is normally enough to protect confidentiality.

Second, consider your information security organization's information classification policy. It should detail which information needs to be encrypted. Deciding which data needs encryption is the responsibility of the data steward or owner, and should be done only when there is a true need for it.

Other reasons you may require encryption to protect data stored in the database are:

• To hide it from those with privileged access to the database (DBAs, for example)
• To comply with legal statutes (such as California's SB1386 law)
• To store it in a schema for which you cannot control the account credentials accessing the data (using shared accounts, for example)

Alternatives to Database Encryption

Data encryption comes at the price of complexity and performance, so do not encrypt data unless it is truly necessary. Here are some effective alternatives to encryption:

• Use object security. Use SQL grant and revoke statements to restrict which accounts can access the data. The accounts to which you grant access must be controlled to ensure that you are only allowing access to authorized users.
• Store a secure hash. Rather than storing the data directly, store a hash of the data. This allows your program to prove that the holder has the correct value, without actually storing it.
• Do not store the data. Do you really need to store the data to begin with? Perhaps the data does not need to be collected or stored.
• Store the data somewhere else. Perhaps the database is not the right place for this data. Consider using an external file system if you cannot be assured that the database is secure.

Encryption Standards

There are a few simple rules and guidelines that you should follow when encrypting data for database storage.

• Use standard algorithms. Do not invent your own scrambling techniques. Oracle, for example, implements standard encryption algorithms such as Triple Data Encryption Standard (3DES), Twofish, Blowfish with DBMS_OBFUSCATION_TOOLKIT (8i and 9i), and DBMS_CRYPTO (10g). Proprietary encryption algorithms are unproven and easily broken.
  Note: Oracle 8i supports only DES encryption, which is past its time. We recommend using the stronger encryption afforded by algorithms available in Oracle 9i or 10g if possible.
• Use object security. Even though you are taking care to encrypt data in the database, you should not neglect basic object security (SQL grant and revoke statements) to prevent access to even the encrypted data.
• Do not encrypt primary keys or indexed columns. If you encrypt a primary key, you will have to encrypt all referencing foreign keys. If you encrypt an indexed column, you may end up with slow queries when trying to use the encrypted value.

Key Handling

Protecting the encryption key is the foundation for effective confidentiality. In the case of database encryption, keep the encryption key and the database credentials far apart—an attacker with access to both of these has everything needed to access and decrypt the data. Here are four important techniques for protecting the encryption key.

• Do not store the key in the database. The DBA can see everything, and you are presumably protecting the data from the DBA as well. If the key must be stored in a central place for an application to use it, store the key on the application server.
• Do not lose the key. The key will be needed for as long as the data is needed. If the key is misplaced, the data becomes useless. Packaged solutions provide sophisticated key escrow, but this guide illustrates a simple, custom solution. As a practical measure, store the key in a version control system along with the application source code, or on a well-maintained file server for which you can be assured there are regular backups.
• Hide the key in the code. If you must store the database credentials and the encryption key on the same server (and you probably do, if you are deploying an application), you will need to hide the key. Because the decryption key and database credentials are stored on the same server, you will have to rely on obfuscation to hide these from privileged users. Because the database username and password are stored as plaintext in a file, we recommend storing the encryption key in your code and naming it something that is not obvious. Further, do not use your production key for development and testing. Keep that key hidden.
• Encode the key to readable text. This is not a must, but you will want to test encryption/decryption. It is easiest to do that with plain SQL*Plus statements (demonstrated below). If you encode the key so that it can be easily read, it will make your testing much easier.

Encrypting Data in Oracle

Oracle provides tools to handle every step of the encryption process; you can generate keys, encrypt, and decrypt in the database. Alternatively, this could all be done in your application because all languages have encryption libraries for the encryption process. Performing both key generation and encryption in the database offers two distinct advantages: it is easy to test and debug, and it is language neutral.

Oracle version 9i contains an encryption package, DBMS_OBFUSCATION_TOOLKIT, that should handle all your encryption needs. Oracle 10g, though not covered in this guide, improves upon 9i with the DBMS_CRYPTO package. It is easier to use and supports more cryptographic algorithms. 3DES encryption, used by the packages described below, should be sufficient for your needs. Note: Oracle 8i does not support 3DES; it is available only in 9i and 10g.

This guide introduces a set of Oracle packages that greatly simplify key generation, encryption, and decryption. This set of packages provides procedures and functions that are easily accessible from SQL statements. Although the package headers will remain the same for each database version, the package body must change slightly from version to version to support varying underlying packages and encryption standards.

The keyManager Package

The keyManager package provides interfaces to create and use various kinds of encryption keys. You will use just one function, allowing you to create an encoded key. You will need to supply at least 192 bits (24 characters) for the key, so that it can be broken up into three 8-byte keys by the Oracle crypto packages to do 3DES encryption.

 

The Crypto Package

The crypto package provides the core functions for encrypting and decrypting. These functions are wrappers for calls to DBMS_OBFUSCATION_TOOLKIT. You will convert your encoded key (generated with the KeyManager.createAnEncodedKey) to a RAW with UTL_RAW.CAST_TO_RAW, so you can use it with this package.

 

The encryptEncoded Function

The standalone encryptEncoded function provides a convenient wrapper for crypto.encryptEncoded.

 

Steps to Encrypt Data in the Database

These steps will guide you through creating a key, storing it in your application code, then writing application code to store and query the data. It uses an example table named "secretstuff", formatted as follows:

SQL> desc secretstuff
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 NAME                                               VARCHAR2(256)
 SSN                                                VARCHAR2(24)

1. Install the Packages

All the key generation, encryption, and decryption will execute in the database. This is a language-neutral approach that makes it easy to test your work.

Ask your DBA to install this key generation and encryption package in the database for you. If you do not have DBA support for it, you can download and unzip it to your home directory, then run the install script.

SQL> @theCrypt.sql
 
[snip]

Function created.
  
Package body created.
 
Input truncated to 11 characters
 
Package body created.
 
SQL>

The packages are now installed and ready to use for key generation and encryption.

2. Create an Encryption Key

You will use the keyManager package to create an encoded encryption key, so that you can store it for use in your application. The example below creates an encoded encryption key, passing a few "random" characters to supply the first portion of the key's "seed," which makes it harder to guess the key.

SQL> select keymanager.createAnEncodedKey('Under all speech that is 
good for anything there lies a silence that is better. Silence is 
deep as Eternity; speech is shallow as Time. ') from dual;
 
KEYMANAGER.CREATEANENCODEDKEY('UNDERALLSPEECHTHATISGOODFORANYTHIN,Y
-------------------------------------------------------------------
416175625A74502B7562696E48625A3976644E6A6D316D6267796F53524B5739

You've created the encryption key
416175625A74502B7562696E48625A3976644E6A6D316D6267796F53524B5739
which will be used in the steps below.

3. Test the Encryption Key

Test the encryption key by inserting a row with an encrypted value, then selecting it and decrypting that same value. This should demonstrate that the value you encrypted decrypts back to the original plaintext.

SQL> insert into secretstuff(name,ssn)
2        values('Clark Kent,
3        encryptEncoded('550-13-5513',
4        '416175625A74502B7562696E48625A3976644E6A6D316D...'));
 
 
1 row created.
 
        SQL> select decryptEncoded(ssn,
 2 '416175625A74502B7562696E48625A3976644E6A6D316...')
 3  from secretstuff
 4  where name = 'Clark Kent';
 
decryptEncoded(ssn,
-------------------
550-13-5513

4. Modify Your Code

Now that encryption and decryption has been proven, change the program code to use these functions. Here is an example of an insert and a select in Java. Though Oracle functions are being called, you will still use PreparedStatement objects as normal.

    private static void insert() throws SQLException {
        PreparedStatement statement =
           con.prepareStatement("INSERT INTO secretstuff (name,ssn) " +
                                "VALUES(?,encryptEncoded(?,?))");
        statement.setString(1,name);
        statement.setString(2,ssn);
        statement.setString(3,encryptKey);
        statement.execute();
        statement.close();
    }
 
    private static void select() throws SQLException {
        PreparedStatement statement =
           con.prepareStatement("SELECT decryptEncoded(ssn,?) FROM secretstuff " +
                                "WHERE name = ?");
        statement.setString(1,encryptKey);
        statement.setString(2,name);
        ResultSet rs = statement.executeQuery();
        while (rs.next()) {
           System.out.println("The SSN for " + name + " is " + rs.getString(1));
        }
        statement.close();
    }

Conclusion

With this guide, you should now understand when to encrypt, what to encrypt, and how to encrypt data for storage in the database.

Acknowledgments

Martin Nystrom is a Security Analyst for the Cisco Security Incident Response Team, specializing in event monitoring tools and incident response.

Definitions

Object security. Setting permissions such as insert, update, and delete on objects such as tables, views, and packages. This allows you to specify privileges for those with assigned roles or directly to individual users within the database. The database engine enforces all such privileges automatically.

Primary key. The unique column (or set of columns) that serves as the identifier for a table row. This is often an automatically generated integer to ensure uniqueness. However, it can be any combination of any type of fields, and the database will automatically enforce uniqueness.

Foreign key. A column that references a primary key column in another table. After a column is defined as a foreign key, the database engine ensures that only values that already exist in the referenced table are added to the column.

Indexed column. A column that is placed into a special table for rapid searching by the database.

Reference

Oracle FAQ Glossary

---

This document is part of the Cisco Security Center.

This document is provided on an "as is" basis and does not imply any kind of guarantee or warranty, including the warranties of merchantability or fitness for a particular use. Your use of the information on the document or materials linked from the document is at your own risk. Cisco reserves the right to change or update this document at any time.

Back to Top

Cisco Security Center