Overview
Considerations
When to Encrypt Data
Alternatives to Database Encryption
Encryption Standards
Key Handling
Encrypting Data in Oracle
The keyManager Package
The Crypto Package
The encryptEncoded Function
Steps to Encrypt Data in the Database
1. Install the Packages
2. Create an Encryption Key
3. Test the Encryption Key
4. Modify Your Code
Conclusion
Acknowledgments
Definitions
References
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.
Before starting to write your application code, there are a few things to consider.
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:
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:
There are a few simple rules and guidelines that you should follow when encrypting data for database storage.
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.
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 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.
Function | Use |
---|---|
createAnEncodedKey(seedValue) | This function creates an encryption key, using the supplied seed value (VARCHAR2), as part of the seed. The encryption key is base 64 encoded to make it easy to read and store. |
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.
Function | Use |
---|---|
encrypt(plainText, rawKey) | This function takes plaintext as a VARCHAR2, along with a key as a RAW, and returns the encrypted text. |
decrypt(encryptedText, rawKey) | This function takes encrypted text as a VARCHAR2, along with a key as a RAW, and returns the plaintext. |
encryptEncoded(plainText, encodedKey) | This function is a wrapper for the "encrypt" procedure noted above, automatically transforming the encoded key to the necessary RAW format. |
decryptEncoded(encryptedText, encodedKey) | This function is a wrapper for the "decrypt" procedure noted above, automatically transforming the encoded key to the necessary RAW format. |
The standalone encryptEncoded function provides a convenient wrapper for crypto.encryptEncoded.
Function | Use |
---|---|
encryptEncoded(plainText, encodedKey) | This function is a wrapper for the "encryptEncoded" procedure noted above. It automatically transforms the encoded key to the necessary RAW format. |
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)
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.
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.
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
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(); }
With this guide, you should now understand when to encrypt, what to encrypt, and how to encrypt data for storage in the database.
Martin Nystrom is a Security Analyst for the Cisco Security Incident Response Team, specializing in event monitoring tools and incident response.
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.
This document is part of the Cisco Security portal. Cisco provides the official information contained on the Cisco Security portal in English only.
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 in the document or materials linked from the document is at your own risk. Cisco reserves the right to change or update this document without notice at any time.