Continuing our theme of quick and effective database security measures, we now move into the data protection phase. The most common (and potentially most effective) security measure for data at rest is encryption. Since we are shooting for fast and effective, we are looking at some form of transparent encryption. Almost every database has transparent encryption built in, and it is effective for securing data files and archives from snooping. Several vendors also offer forms of transparent encryption at the OS/file system level, which behave in a very similar manner, so we will consider those options as well. It’s ironic that I am writing this post today, as I just completed the final editorial sweep through the Securosis Database Encryption & Tokenization paper. Rich and I will be releasing it tomorrow (Cinco de Mayo), so if you want a much deeper dive into the technology tradeoffs and variations, check it paper out when it becomes available (Shameless plug: If you are interested in sponsoring the paper, let us know).

There are a handful of business reasons to use data encryption for databases: to buttress access controls in order to protect against unwanted insider access, to protect data at rest, or to comply with an industry or government regulation. Only the last two are covered by transparent encryption, as the former requires encryption at the application layer. Application level encryption requires code changes, database changes, and application recertification, so I exclude it from this Fundamentals series. Encryption embedded within disk drives is transparent, and it protects files on the disk as well. However, purchasing encrypted drives is a significant investment, does not protect exports or tape archives, and does not protect databases moving around virtual environments. Since we are focused on quick wins here, I am limiting the discussion to transparent database options – either using native database capabilities, or through OS/file system support.

Native database encryption features are embedded within the database. The encryption operations are handled behind the scenes, with no changes to the tables, columns, indices, or queries. Enabling the feature is at most an add-on package, but in some cases as simple as a handful of DDL statements. The database encrypts the data just prior to writing to disk, and decrypts when processing authenticated queries for encrypted data. Key management is either handled internally (with keys stored within system tables and only accessible by DBAs), or externally (with a dedicated key management server). Internal key storage is easier to manage, and simpler in disaster recovery scenarios, at the expense of weaker security. In either case, keys are used without the end user interaction (or even knowledge).

File/OS encryption works by intercepting the database’s writes to disk and encrypting data blocks before storing them. Conversely, data is decrypted as the database requests information from disk. Keys are stored within key management services embedded within the encryption product rather than the database, or provided by external key management products. Keep in mind that this type of product can applied to on specific folders where the data is stored and not just database files. File/OS encryption is attractive for its ability to address both database non-database data security issues.

Two options are not a lot, but both transparent options are effective and offer the same business benefits. The choice comes down to four factors, in order of importance: performance, cost, versatility, and comfort level.

  1. How much does the solution impact transactional throughput?
  2. How much does it cost?
  3. How many different problems does it solve?
  4. How easy is it to use?

Or at least this should be the order of importance, but from experience I know some people reverse that order because they know the database and are comfortable with a particular UI.

If you are the sole DBA, how comfortable you are with the interface, or how easy it is for to use, will be the biggest factor because your time is more important than the other factors. If you have been using Sybase for years and are happy with their tools, odds are you will choose that. Regardless, if you have the opportunity, running a couple performance benchmarks is very handy for getting an idea of how much impact encryption will have. It may be 3%, or 12%. Nobody notices 3%, but 12% may mean calls from users. Run some basic performance tests between a) your unaltered database, b) the database vendor’s solution active, and c) an external tool. Understanding the impact on typical database transaction processing really helps with decision making. Get some pricing estimates from vendors. If there are others in your IT organization who already use file/OS encryption, ask them about usage and performance. Yes, this makes this a two-day task instead of a one-day implementation, but it’s worth it. Testing setup and execution will take at least a day, but will give you greater confidence in your decision and make the final rollout a lot easier.

  1. Select: The question of what type of transparent encryption to select – internal database native or external file/OS – is a murky one. Weigh your options and make your selection. Acquire the tool or licence.
  2. Define Scope: Column level, table level, or entire database? Understand what data you will apply encryption to, read the documentation, and generate your configuration scripts.
  3. Configure & Install: Once you have reached this step, you should be able to implement database encryption within an afternoon. Obviously, the first step in the process is to make sure you have a verified backup prior to the installation process. Once you have installed or configured the encryption engine, the first major step will be to generate the keys. Select a good passphrase (not password) to protect the keys. Produce a verified backup of the key archive. If the keys are stored in a system table, take a fresh backup of the database. If the keys are in an external key management service, before you go any further, make sure you have that backed up and can restore successfully.
  4. Encrypt: You have everything set up, so now you need to encrypt the data: turn on encryption. This will take some time, as your chosen tool must read, encrypt, then rewrite every single block of data to be encrypted. A large database means you may have the database offline for several hours, so plan accordingly. Once encrypted, data will be automatically encrypted as it is written to disk, so there is very little need for you do anything else – except wait until the initial encryption process is complete.
  5. Verify: Now that the database is encrypted, bring the database back online. Verify that applications continue to function normally. You should also perform a test recovery of the backup in a test environment to ensure that the database archive, key management and access controls can be properly synchronized in a disaster recovery situation.
  6. Document: At this point you are done except a few clerical tasks. If you applied encryption to a subset of the database, document which tables or columns. If the passphrase for the keys needs to be entrusted to someone in case you are hit by a bus on the way home from work, do so now. If transparent encryption is being used for regulatory compliance, document how the solution is being used and inform auditors, so they can complete their checklist.

I am a big fan of transparent encryption. It is fast, easy, and effective for addressing one or two very real threats. That said, it’s not a secret that I don’t always see eye-to-eye with the vendor community. I have strong opinions, having worked both sides of the fence, and my comments on transparent database solutions tend to generate friction. When I say that Type A transparent encryption is not always the best answer, I get the same reaction as if I told someone their baby was profoundly ugly. When I say that one option performs better than another – and we are talking about minute differences of a few percentage points of CPU overhead – I get email trying to “educate me” on “the real story”. Blah! The fight for market share between the various vendors can be tenacious, with the players uncharacteristically vociferous over minor points. Don’t let the rhetoric fool you, and don’t base your decision on FUD. Choose the solution that best satisfies your business drivers. If two solutions are basically equivalent, choose the one you are most comfortable with and move forward. You are not really going to make a bad choice, and lock-in of technologies is pretty minimal with transparent solutions, so you can always swap down the road. Regardless, transparent encryption is a very good solution for media protection, and it’s a quick way to satisfy most PCI auditors.

Lastly, both internal and external options allow for encrypting the entire database, or selected columns/tables. While it sounds better to only encrypt the minimum amount of data possible to reduce overhead, in practice there is not much performance gain in limiting what you encrypt. In my experience, once you reach three columns or tables, performance is about the same as encrypting everything. Second, it is possible that other tables or views contain sensitive information, or indicies to leak information. For this fundamentals series, encrypt the entire database. It’s easier and there are fewer chances for mistakes.


Index to other posts in the Database Security Fundamentals series.

  1. Introduction.
  2. Access and Authorization.
  3. Connections and Access Points.
  4. Patching.
  5. Configuration.
  6. Transaction Audit.
  7. Event Monitoring.
Share: