Encryption is a funny thing. Since the dawn of communication, whenever people have wanted to keep their secrets secret, they’ve used some sort of encryption. I’m sure parents started spelling things so their kids wouldn’t understand as soon as there was spelling. Using words their kids wouldn’t understand, while the kid sits there thinking “Oh, Dad, you’re so embarrassing, thinking I don’t know what that means…”. Encryption is all about keeping information away from people, particularly those who could use it against you. Ask the folk from Bletchley Park if you don’t realise how this can impact world events.
A quick diversion for a paragraph: In my opinion, there is a big difference between encrypted data that can be encrypted and encrypted data that can’t be. A one-way hash can be used to see if your password is okay, without ever being able to decrypt it. Good systems don’t compare your password to what they store, they compare a hash of your password to what they have stored. They can know you know your password, without ever knowing what your password is. The only thing that should be able to tell you any of your passwords is your password manager. Also, don’t re-use passwords, just in case, and please use multi-factor authentication. Everywhere. Avoid SMS for MFA if you can (given that your phone number can be stolen way too easily). You’re not supposed to be able to remember passwords – so don’t even bother trying.
Anyhoo, Matthew McGiffen (@MattMcgiffen) asks about Encryption and Data Protection. He’s asking about encrypting data that needs to be decryptable. He asks for stories about times that encryption has gone wrong – but I don’t like to give up those secrets. That’s a one-way hash for me. My customers might say “remember the time”, and I’ll recall it, but you won’t find me writing about that here.
There’s always a trade-off between protected and useful. The weakest point of any system are the users, because they need to be able to access the data. But let’s assume that the users are not a weak point, and consider some of the other encryption spots within a SQL environment.
Without encryption, the contents of your SQL database are readable by simply opening the MDF file in a text editor. Words can be read, and other values can be understood just by looking up the details about how they’re stored. It’s not complicated. Transparent Data Encryption will make the data in the files on disk unreadable to anyone who doesn’t have the encryption key. It protects against the attacker who steals a disk. This is a valid thing that needs protecting against, and I’m very much in favour of every database having TDE turned on. And protect the key. Don’t store it on the same disk.
Dynamic Data Masking is not encryption. Nothing is actually secret, but it takes a little more effort to be able to read the data. It’s good for stopping accidental data leakage. Like when you see the name of someone you know in a customer database. If you’re looking for that information, you can still find it. But if you’re trying to keep yourself a little safer by not being able to see that information, then masking can be useful.
Encryption functions are similar – they’re not going to protect your data from someone who has access to your database. And don’t think that having encrypted code in your database is going to help. Decrypting encrypted procedures has been an available option for a long time. If the database can get at the code so that it can execute it, you should assume the DBA can get to it too. If you’re a third party vendor, you’re probably better off assuming these secrets are not actually secret. Sorry. If you’re trying to decrypt them, then be cautious about using code for it that’s around the internet – not all of it is good. Some of it will decrypt the procedure but destroy your procedure in doing so. Make sure you’re rolling any changes back, and that you’re not doing it on a production system.
Always Encrypted involves encrypting the data using keys that are stored on a different machine, that only the application has access to. Therefore, the DBA can’t read the data. If your DBAs are a weak point, then maybe this is a good thing, but get DBAs you can trust. Your application developers might still have access to the system – are they more trustworthy than your DBAs? I would like to think people in both those roles have your interest at heart and don’t want to compromise your system.
You see, there are a plenty of ways of protecting your data. But at the end of the day, your information almost certainly has to exist somewhere in an unencrypted form, whether it’s showing someone their bank transactions or putting their password onto the clipboard so they can log in somewhere. And therefore, there are always going to be weak points, no matter what you do.
Weigh up the risk, protect your customers, and understand that nothing is foolproof.
Sorry.
This Post Has One Comment
Brilliant read. Thanks Rob.