In Common Database Vulnerabilities: Backup Data Exposure I told you the story about a lost backup file that lead to data exposure. I used that story to remind you that you need to protect your backups at least as well as your live data. The lost backup however was not the root cause of the problem. The root cause was flawed key management.
That client called me after one of their employees had lost their laptop. Now, I am neither in law enforcement nor do I work for an insurance company. So why did they call me about this?
The forgetful employee was a developer and stored on the laptop in question was a backup of the company's database. That backup contained real customer data, which amongst other things included credit card numbers.
I had not worked with that client before and they really called me only for their own peace of mind. While the backup file itself was not encrypted, the client was confident that all the important data (the PCI data) was encrypted within the database. All they wanted from me was to confirm that there was no problem.
I connected to the database and found the credit card information indeed encrypted. However, I also found a couple of views like this one:
CREATE VIEW dbo.CustomerCreditCards AS SELECT C.first_name, C.last_name, CAST(DECRYPTBYPASSPHRASE('asw36D2k7XgFcjm',CC.card_number) AS VARCHAR(30)) AS card_number, CAST(DECRYPTBYPASSPHRASE('asw36D2k7XgFcjm',CC.expiration) AS CHAR(4)) AS expiration FROM dbo.Customers AS C JOIN dbo.CreditCards AS CC ON C.customer_id = CC.customer_id;
To protect the customer's interests, I changed the table and column names and the actual passphrase, but this view closely resembles one I actually found in that database.
The customer was not too happy when I told them, that their encryption was entirely useless, as anybody capable of restoring that backup file would be able to run a simple select to get to the credit card numbers in clear text. What followed for them was an unpleasant encounter with the legal side of the PCI compliance rules. This included paying fines and penalties and also quite some reputational damage, as all their customers had to be notified individually of the leak.
It is important to encrypt important data like PCI data or PHI in your database. But it is also important to manage your encryption keys correctly. The above view, I can only assume, was an attempt to implement something similar to transparent data encryption. While they succeeded to make it transparent to the application, they also succeeded in making it transparent to any attacker. In fact, and attacker would not even need access to a backup file to exploit this problem. Essentially, the data is stored unencrypted.
Getting encryption key management implemented correctly is not a trivial task. You need to balance the need to access the data through the application with the requirement not to be able to access the data directly. You might have to change the encryption keys regularly but you should not give your DBAs access to the key. There are many seemingly conflicting requirements and even more ways to get the implementation wrong. Sometimes that leads to the key being accessible to outsiders, negating the entire encryption effort. Sometimes it leads to keys getting lost, leaving you locked out, without access to your important data. To avoid these pitfalls, you need to plan and execute your key management strategy carefully.
The time to act is now. Review your encryption requirements and make sure that all data that should be encrypted is encrypted. But, even more important, make sure that the keys to your encrypted data are not easily accessible to an intruder. Instead, they should be stored in a safe place and backed up. If you are unsure about how to make any of this work, get help from someone with experience in this field.
Flawed Key Management is one of the most commonly encountered database vulnerabilities. In this series of posts, I discuss 10 of them. Below are the ones that are published so far: