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.
- How much does the solution impact transactional throughput?
- How much does it cost?
- How many different problems does it solve?
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
Reader interactions
5 Replies to “Database Security Fundamentals: Encryption”
Ears burning, Twitter Failwhaling and here I am! thank you both for noticing the blog, what can I say if you don’t know what you want it to do, stop doing it until you have that figured out.
I’d be very interested to hear your views on how encryption can be applied in cloud environments. As we move more toward shared storage and shared I/P/SaaS it seems the only option open to enterprises with data security needs in a cloud environment is encryption of one form or another. How do you see encryption working in the Brave New World? Who holds the keys? Who gets access and how? how do we make the data both accessible and protected?
@Rowan – Yeah, I coincidentally ran across Rik’s post this morning. Most IT personnel don’t fully understand the value different flavors of encryption provide, and (with the exception of PCI-DSS) the laws are so vague they are not sure if they need full disk encryption or just SSL. The question is not _how secure_ your encryption needs to be, but what the heck does your encryption do?
-Adrian
Some very useful thinking here – thought you’d like to know that Trend Micro’s Senior Security Advisor Rik Ferguson has linked to your post over on his Countermeasures blog: http://countermeasures.trendmicro.eu/data-breach-laws-encryption-and-having-a-plan-‘b’/
This subject relates closely to an ongoing Security workshop Trend is running on the Register and on which we’d love to get your input: http://www.theregister.co.uk/security/security_that_fits/
Great question. This is one of the basic issues we tackled in the [database encryption](http://securosis.com/blog/download-our-kick-ass-database-encryption-and-tokenization-paper) paper we just released. You may not have time to comb through the decision making process outlined there, so here is the short answer:
Regulations and privacy laws.
Database encryption, at least transparent DB encryption, is really good at protecting the database files from unwanted inspection, and data being read on backup media. All other attacks, not so much. Keep in mind that this is often deemed sufficient for regulatory controls such as PCI and data privacy laws. While access controls both limit unwanted access to data, segregating which users who should legitimately have access from those who should not. Transparent encryption, as you pointed out, is designed to provide legitimate users with an unencrypted copy of the data. The problem is attackers find ways to acquire permissions, fooling the database, and administrators have super user privileges to see everything*. This can be someone mitigated with separation between the DBA roles, and made _very_ difficult with external key storage, but not entirely addressed. That takes application layer encryption.
Sorry if I don’t follow your “Symmetrix” example; I don’t understand. You can shoot me an email if I failed to address your question.
*Database vendors complain when I say this, but it’s true, a talented DBA will either get direct access to data, or get the keys stored inside the database and gain access to encrypted data.
Adrian: you are the DB Security guru, I a humble CISO; but I’m twisted on the subject of DB encryption. If I’m not encrypting to hide data from the DBA (I suspect DBA’s would be hampered in their duties by this); what then are we protecting? The applications accessing the DB have to be authorized to decrypt, so if I compromise the app, don’t I have the data? OK, so I don’t compromise the app, I use TOAD – but without credentials, can’t access the data, with credentials, transparent decryption gives up the data.
OK, so what, someone steals the Symmetrix fron the data center, drives from the Symmrtrex? – each one with scattered “bits” that form logical rows somehow, with the help of millions of lines of EMC microcode??
OK, so I’m obviously missing something here, otherwise folks (like us) wouldn’t by Oracle Advanced Security features (yep, using that over at our cloud vendor)… Help put me straight on this issue, while I watch a couple more SQL injections take down another few million credit cards…