I was reading a post talking about some SQL encryption technologies and statements (because, you know, that’s what I do just sitting around…) and found a new potential vulnerability that I’d never really considered before.
I don’t pretend that I know *all the things* about security and encryption – it’s a constant process to keep learning and figuring out what applies to different situations we face. But in this case, I was checking out column value encryption (NOT TDE, etc.) specifically value-by-value columnar encryption.
The example given really caught my eye – they were talking about pay rates in a table. If the table were accessed by someone they would see the data in the table, and of course, the pay rates would be encrypted to protect prying eyes from seeing things they shouldn’t be seeing.
At first glance, I thought this protected the values pretty well, I mean, without knowing the keys and encryption methods and such, it’s pretty clear you’re not going to know the values. And you’re not going to be changing them because you’re really not able to re-encrypt the data as you put it back. Pretty good so far.
In the document though, they talk about the attack vector of discerning the information in the fields in terms of relative value. In this case, the CFO vs. a mailroom clerk. Chances are good that there’s a discrepancy in those salary rates. And, you can infer from the other column values that are seemingly harmless what the title is for the other positions.
What happens if you simply copy the value for salary from the CFO to the other individual’s row? It would work, would decrypt as payroll runs next time and away you go. Suddenly that’s one well-paid mail clerk.
I had never thought of the act of copying data, in its encrypted state, between rows to accomplish a modification that shouldn’t be happening. Great point, and of course SQL Server provides for protection from this.
You can add an authenticator that ties the value in the encrypted column to the specific row – it’s an option on the TSQL statement that will help you lock that row to the correct source row… or at least let you know if it changed and had been moved from elsewhere, rather than just decrypting it and trusting it.
It’s a great option and one that I was glad to learn about and dig into a bit – and something to think about if you’re doing encryption of this type and approach – it’s something to consider as you architect the data security on your systems.