OK, let's talk about encryption for a minute, and why you should and shouldn't do it. Encryption is one of those buzz words that sounds really cool to implement, but so many people don't understand the effect it has on your environment. It's a lot like clustering. Too many people like the sound of that word and end up implementing a clustered solution when they didn't really define what their needs were to begin OK, let’s talk about encryption for a minute, and why you should and shouldn’t do it. Encryption is one of those buzz words that sounds really cool to implement, but so many people don’t understand the effect it has on your environment. It’s a lot like clustering. Too many people like the sound of that word and end up implementing a clustered solution when they didn’t really define what their needs were to begin with. Encryption suffers from this linguistic admiration as well. Before you decide to encrypt data, you have to assess what you’re going to encrypt and why. This sounds trivial, but you’d be surprised how many people miss this step, and how many just flat-out get it wrong.For starters, if you’re encrypting to fulfill an audit, then DON’T. There are none of the mainstream audits that require you to encrypt your data. They require you to protect your data, and a lot of IT guys mistake that for encryption, but the spirit of the control is to lock down access to your box. What they want to see is that people who can see the data are the ones who should be seeing it. That’s about it. Don’t read too much more into it. And by all means, don’t encrypt your data just because you don’t feel like locking down your server. Encryption is a big step to take and a project that limits user rights to the data will be much less invasive.Basically, encryption hijacks your data and is pretty invasive. Usually what happens, is the encryption software steals your table and replaces it with a view that you use to access the data instead. That view points to the original (and renamed) table with encrypt/decrypt functions. In software solutions like DBEncrypt from AppSec Inc, the functions also check your permissions and will either present the data to you or not based on your rights. Other solutions like Ingrian (hardware-based) do basically the same thing, only they ship the data back to the appliance first usually. I think you can set it up to check it locally, but I may be confusing it with something else right now… anyway though… that part doesn’t really matter. What does matter though is how invasive those solutions are. In fact, with the hardware solutions, you’re completely screwed if your appliance goes down because it stores all of your encryption keys and without it, your data is lost. Just think about that before you implement something like that. There’s also what encryption does to your processes. You have to put a lot of thought into testing and do a fair amount of benchmarking before you get into a solution. You never know what’s going to shake out. A good example would be Ingrian. You really wouldn’t want to load a warehouse with it in place because it can’t decrypt in batches. That means that every row gets shipped to the appliance. It’s like a 200mill row cursor. So you can imagine how incredibly slow your load is going to be. I see that a lot though; people making decisions without thinking through every aspect of their business. They have blinders on with only their immediate problem in sight. So for the sake of everyone in your company. When you decide on a plan of action, make sure it will suit every business unit first.Here’s a really good one… make sure you actually need to encrypt. Know what you’re protecting your data from. I had a case once where a company spent something like 50K on an appliance to encrypt 3 columns in their DB. The server was locked down, so only those with access could see it anyway. So why encrypt? You’re only protecting it from people who can already see it anyway? Is it to protect against the data being stolen say from the off-site storage where you send your backup tapes? That’s a valid concern, but encrypting the backup file would be a much cheaper and much less invasive solution. And it would actually suit the purpose with more elegance. And don’t forget… if you implement something like that to keep your backups safe, you have to get all of your backups back from the off-site storage and encrypt them. Otherwise, you’re not any more protected than you were before. Again, it’s a simple thing to ask the question: What are we protecting ourselves from?So when should you encrypt? That turns out to be a very interesting question. The simple answer is when you feel you may be at risk from internal or external forces beyond your control. So, this would be something like having your DB exposed to the DMZ and you have extra-sensitive information that could be compromised should something grave happen to the network security. You could also be in a situation where you just can’t lock down the DB because of some legacy tie-in, and everyone who has unrestricted access to the data doesn’t need it. This could fall under the heading of having NT admins in the SQL admin group and you can’t remove them. Also, if you have to roll your production data to QA and test people, that’s a good time to encrypt. However, I feel that there are steps you could take that would be much less invasive than encryption if this is the case. The point is that encryption can be necessary, but really only under limited circumstances where all other tactics have failed. Basically, you should look at encrypting your data as surgery. Don’t do it unless all of the other avenues have been exhausted, and there’s just no other way to get the gerbil out. Maybe next time I’ll talk about some ways to obscure the data in QA and testing without having to encrypt. Oh yeah… there’s one more thing. Every major DB vendor now has native encryption. This can be far less invasive than their 3rd party counterparts, but you should plan to dig through your code and add the function calls. As of right now, the only vendor I know when any stated plans to add real native encryption functionality is Sybase. Real native encryption being where you don’t have to call a function yourself. You simply mark the column as encrypted and the RDBMS takes care of the rest. You then just assign encrypt/decrypt permissions to your users and there’s nothing left to touch. I don’t know if Sybase has implemented it yet, but they were talking to me about it a while back. Once this ease of use hits all of the DBs, I suspect the encryption companies will have a lot of very large doorstops on their hands. Databases