Encrypting Data at Rest

Data should be encrypted at rest and in motion. In this post, I'll discuss encrypting data files rather than securing database communications.

There are several different approaches to encrypt data:

There's encryption in the application that feeds to the database where extra code is written to automatically encrypt selecting, inserting or updating of data.

Secondly, there are file system capabilities where everything that gets written to a disk is encrypted. This protects sensitive information from physical theft but doesn't protect from rogue DBAs copying/selecting details in a table. This option protects data as long as it is on the filesystem that supports encryption: once the data is copied, the protection is lost.  For example, if a DBA copies backups to optical media than they become cleartext and unprotected.

And third, we have Transparent Data Encryption (TDE) that utilizes the functionality that the database vendor has provided to encrypt data without modifying the application or depending on the capabilities of the file system. Changes to the database will be required depending on the implementation - from changing database options to changing column attributes.

The three major database vendors (Oracle, Sybase and Microsoft) provide database-level encryption functionality in their products. Oracle started shipping TDE as part of the Oracle Advanced Security Option in Oracle Database 10g R2 Enterprise Edition. TDE in Oracle has significantly improved it in newer database releases. Sybase Adaptive Server Enterprise (ASE) encrypted columns feature debuted in version 12.5.4 and is quite advanced in the Sybase ASE 15.5. In addition, Microsoft has shipped TDE in the Microsoft SQL Server 2008 Enterprise edition.

The main idea is simple - user-specified columns or entire database files are encrypted with a strong encryption algorithm so that data in the files can't be read by an attacker without knowing the encryption key(s).  This is done without any change to the application code. When the application issues a SELECT query, the database decrypts data and returns clear text to the application. In the same way when the application saves data via INSERT/UPDATE, the database encrypts the values and saves to the disk encrypted data. By doing so, this prevents privileged OS users on the database host from reading sensitive data as well as keeping data and backup files safe. Encryption is required to comply with certain government and industry regulations such as PCI, HIPPA, etc.

Architecture

Most implementations use symmetric key encryption to encrypt the data. Usually there are two-tiers: first, symmetric keys are used to encrypt columns/data files and then there is a master key used to encrypt the column encryption keys themselves. This master key can be protected by a password (Oracle Database) or can be an asymmetric key (Microsoft SQL Server). In each implementation there are differences how the keys are stored, what is the scope of the encryption, what algorithms could be used and so on.

Oracle Databases store master keys in an external security module (ESM) which can be the Oracle Wallet (file), Hardware Security Module (HSM), or external PKCS#11 compatible key management system. HSM is a hardware device that receives encrypted column keys from the database and returns keys in clear text so the master key never leaves the device.

Microsoft SQL Server 2008 allows protecting the database encryption key by using an asymmetric key stored in an extensible key management (EKM) module which is very similar to the HSM concept: via EKM third-party EKM/HSM vendors to register their modules in SQL Server.

Sybase ASE stores a key encryption key which encrypts column encryption keys (master key) in the database and encrypts it by system encryption password, or a login password, or a user-provided password. Sybase implementation also requires special 'decrypt' permission to be granted to read clear text data.

Performance Impact

Usually the columns encrypted should be limited to ones containing sensitive or Personally Identifiable Information (PII) such as Social Security Numbers, credit card numbers, etc. in order to minimize the performance footprint.  There might be some storage overhead too depending on the strategy used.

According to the Oracle whitepaper "Oracle Advanced Security Transparent Data Encryption Best Practices," the performance impact is 4% to 8% in end-user response time, and an increase of 1% to 5% in CPU usage. Oracle Database 11.2.0.2 can leverage hardware-based cryptographic acceleration in Intel Xeon 5600 CPUs with AES-NI which makes performance impact very small. Before implementing database-level encryption the impact on CPU and memory should be researched.

Limitations

Index usage might be limited for certain comparison operations. In some implementations, anything except equality comparison of encrypted column's value will cause a full table scan. Not all data types are supported for the encrypted columns, for instance FILESTREAM DATA in Microsoft SQL Server 2008 is not supported. Consult your database documentation for more details on particular implementation.

Comparison Matrix

Examples

Here are some examples that enable database-level encryption in different products.

In Oracle Database we must first configure The Oracle Wallet file location if wallet is used to store master key and create the wallet. Next the Wallet must be open via:

ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY 's1mpl3p4ssw0Rd'

Then table definition can be updated to encrypt some columns:

ALTER TABLE Users MODIFY (Password ENCRYPT)

Now the database will store password columns value encrypted on the disk. Note that the Oracle Wallet must be opened each time the database starts. Alternatively it can be configured to auto-open via 'auto-open' wallet. For more details consult Oracle documentation.

The following SQL code is used to enable simplest form of column encryption in the Sybase ASE 15.5:

   EXECUTE sp_configure 'enable encrypted columns', 1'
    EXECUTE sp_encryption 'system_encr_passwd', 's1mpl3p4ssw0Rd''
    CREATE ENCRYPTION KEY safe_key AS DEFAULT FOR AES WITH KEYLENGTH 256'
    ALTER TABLE Users MODIFY Password ENCRYPT decrypt_default '***'
   

Note that using encryption in Sybase ASE requires licensing option ASE_ENCRYPTION. First, we enable encrypted columns support, then set system encryption password and create encryption key bound to AES256 encryption algorithm. This key will be the default. It will be used in subsequent ALTER TABLE for example, since there is no explicit key name provided. The ALTER TABLE provides decrypt_default value which will be seen by users who are not granted explicit decrypt permission but still can select from the table column.

The following code is for Microsoft SQL Server 2008 Enterprise edition:

   USE master
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 's1mpl3p4ssw0Rd'
    CREATE CERTIFICATE DEKcert WITH SUBJECT = 'DEK certificate'
    USE MyDB
    CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE DEKcert
    ALTER DATABASE MyDB SET ENCRYPTION ON
   

Once it is executed, do not forget to backup the certificate used for encrypting the database encryption key and the private key associated with the certificate. The database is now encrypted.

Conclusion

Examine options your database provides for data encryption at rest, consider exiting limitations and your application specifics. It may be a good choice to implement database-level encryption.

References

Database Encryption: http://www-smis.inria.fr/~bouganim/Publis/BOUGA_B6_ENC_CRYPT_2009.pdf

Oracle Advanced Security Transparent Data Encryption Best Practices: http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf

Encrypted Columns in ASE 15: http://www.sybase.com/files/White_Papers/Sybase_ASE_Encrypt_3PSS_wp.pdf

Understanding Transparent Data Encryption (TDE):  http://msdn.microsoft.com/en-us/library/bb934049.aspx