13

I was discussing with someone ways to prevent data disclosure from a compromised admin account on a database server. The other person proposed encrypting the data at rest within the database. It sounds like a good idea, but I wasn't sure if that would protect the data in this scenario.

My thinking is if the attacker has an admin account, they'll also have the ability to access the encryption key. In a typical configuration, would this be the case? Could the system be engineered so that it wouldn't, while still being practical to administer and use? I could see having the admin provide the key when they log in, but I am ignorant on the security implications of that...

My threat model is as follows: A legitimate admin account's username and password is compromised. Our attacker uses that account to log in remotely and download the database. I understand MFA and other access controls would protect here, but assume they've failed or otherwise been circumvented. I'm merely curious of encryption's effect here.

Now I realize this would definitely come down to implementation specific details, however I am not super familiar with database administration, so I'm struggling to provide a detailed setup for a hypothetical system.

Kevin Mirsky
  • 494
  • 3
  • 13
  • 7
    A subtle point to be made here is that you're using *"admin"* in the sense of *"a single, all powerful role'* whereas, in organizations that are large and sensitive enough, it's very common to segregate duties very strictly around sensitive data, such that the damage done by one single set of credentials is narrow. – dwizum May 09 '19 at 17:05
  • Just wanted to add: your threat model is a bit narrow. I mean, if the DB Admin's account gets hacked, it's not like stuff like Application Level Encryption or User Provided Keys will save the day. You're still getting Ransomware'd, or DOS'ed, or Rooted, etc. – Kevin May 09 '19 at 18:18
  • Related, see [Homomorphic Encryption](https://duckduckgo.com/?q=homomorphic+encryption) and [Semi-Homomorphic Encryption](https://duckduckgo.com/?q=semi+homomorphic+encryption). Using HE or SHE you can [theoretically] encrypt the fields in the database tables and still compute on them, like SELECT and ORDER BY. The data would be safe from admins at Amazon, Dropbox, Salesforce, etc. The cloud would only have the encrypted data. The data would likely be breached by an admin within the organization, however. A local admin with access to private key can decrypt the query results. –  May 09 '19 at 21:04
  • If you're interested in looking at some bleeding edge research on the topic: Patent 8504876 B2 Aug 6 2013 The thumbnail is that it uses machine learning to define normal use and can block or notify abnormal use, such as attempting to get everything, even by an admin. [Anomaly detection for database systems](https://www.mitre.org/research/technology-transfer/patents/anomaly-detection-for-database-systems) – user10216038 May 09 '19 at 20:50

3 Answers3

18

Yes, such a system exists; it's called Application-Level Encryption. Under that system the encryption keys (or at least the Key-Encrypting Key, or KEK) are only available to the application. Data is encrypted by the application before being stored in the database, and encrypted blobs are retrieved from the database to be decrypted by the application.

The advantage of this is a sort of dual control - the DBAs, who can perform bulk actions against the entire database without much limitation - can only extract encrypted data. The application administrators, who hold the KEK, can decrypt anything in the database, but are limited to approved interfaces which may work piecemeal but not in bulk. It makes abuse of the decrypted data harder to perform and easier to detect.

There are disadvantages as well, primarily the inability to index or search upon plaintext characteristics of the encrypted data in the database. Also, it requires the application and database be written to support it; unlike whole-disk or whole-database encryption it's not as easy as flipping a switch.

gowenfawr
  • 71,975
  • 17
  • 161
  • 198
  • Good point and could be the best solution in the right situation...but I hope it is a small dataset and you don't have a large number of different applications that need to access it. – DarkMatter May 09 '19 at 15:33
  • @DarkMatter I've seen it done successfully with a large dataset and multiple applications; it depends on the usage profile. It would be completely inappropriate for, say, data mining. But for transactional data or anything where there's sufficient non-sensitive metadata to use to address the sensitive data, it works just fine. – gowenfawr May 09 '19 at 16:40
  • 3
    A benefit to application level encryption is that it allows you to be selective. You can choose to encrypt something sensitive (a field containing SSNs) but not encrypt things that are less sensitive. So - @DarkMatter - you get the flexibility to work around performance or access needs vs security. – dwizum May 09 '19 at 17:02
  • It would also render a lot of the features useless e.g. range indexing and aggregations (for a relational DB) or am I missing something? – JimmyJames May 09 '19 at 17:31
  • @JimmyJames I don't really see why it would necessarily. To the database, it's all the same. As stored, the data *looks* like gobbledygook to a human viewer, *but the database doesn't really care* as long as it's *all* gobbledygook. Since in this scenario the application is responsible for performing the encryption and decryption, it would be. Yes, you lose some potential flexibility, performance, etc., but that's the usual tradeoff where especially confidentiality (and to some extent also availability and integrity) is concerned. – user May 09 '19 at 17:36
  • @JimmyJames you're not missing something, it does inhibit what you can do with encrypted fields (...but you can encrypt, or not, on a field-by-field basis). It's definitely a trade-off, but can be useful where encryption is a requirement (e.g. PCI). – gowenfawr May 09 '19 at 17:36
  • 1
    @a Cvn Most indexes in a DB allow for range searches to be optimized. For example if I want to be able to select all the people in the DB who's name starts with "Wa", an sorted index will make that fast. The encrypted form of "Walen" and "Watkins" would not be expected to be similar. And if an amount column is encrypted, I can't select the `sum` of the column and the expect the decrypted value to be correct. – JimmyJames May 09 '19 at 17:53
  • 1
    @JimmyJames a sorted index would not work, but you could have something akin to a hash index, where you have 26 buckets each assigned to a letter of the alphabet that retain the ID number of the row where the decrypted value starts with that letter. That way, you won't have to decrypt every entry (on average around 5% accounting for letter popularity) while sacrificing only minimal security. it's similar to how Troy Hunt set up his HIBP Azure Table Storage system, but instead of the first X characters of the password hash or the email domain, it's grouped by the first letter. – Nzall May 10 '19 at 12:53
3

On a database server, two different admin accounts can exist: the system admin account(s) and the database admin accounts. @gowenfawr's answer already addresses the database admin case, so I will focus on the system admin one.

In that case, you have lost. It is not possible to protect a machine from its administrator, because they have a full access on any file on the system. As a server normally supports unattended reboots, the application will have to be able to extract the database decryption key, and someone with admin priviledges should be able to extract that key too, because it could impersonate the application user.

There are some possible mitigation ways. One is when the data is encrypted client side. In that case, the application only processes encrypted data with no possible access to the clear text one, so even with full access to the machine and to the database it is not possible to decrypt anything. This is a very secure but rather inconvenient way: if users lose their key, the data is definitely lost. Because of that, system admins are generally very reluctant to a security model that ignores them.

Any other way can only be obfuscation: the key or at least the decrypting procedure has to be accessible to the machine. It can be made very complex to find it so that you can hope being able to close the door before the attacker could really extract any clear text data, but it only make sense if you have a global security system which analyses even apparently legitimate accesses to raise warning when an account is used in an unusual way. You end in the classical threat-risk/mitigation-cost question...

The best way would be to separate machines and admins for the database server and the application server. That way the database machine does not know the decryption key hence the database admin cannot extract it. And an application admin has no way to extract everything from the database. But it can still access any data the application can access. Simply having different admin groups has a cost and here again the threat-risk/mitigation-cost question applies.

Serge Ballesta
  • 25,636
  • 4
  • 42
  • 84
  • 3
    This is only really relevant if the app is residing on the same server. You can be system admin of the host holding the DB... but if you're not admin of the box holding the apps that interface in with it, you won't be able to decrypt the DBs contents. For example, if I have \\HostDB\ and \\HostApp\, and the \\HostDB\'s admin creds were compromised, if all the encrypting/decrypting was done on \\HostApp, then the breach wouldn't expose any unencrypted data - because the attacker doesn't have any code/apps/etc that can read the data. – Kevin May 09 '19 at 18:01
  • @Kevin: you are absolutely right. In that case the higher risk could come from the application server admin. I have edited my post. – Serge Ballesta May 10 '19 at 07:37
2

My threat model is as follows: A legitimate admin account's username and password is compromised. Our attacker uses that account to log in remotely and download the database.

Any remote connection to your network should be protected by 2FA (Especially any admin connection). This would mitigate your threat here. Furthermore, depending on the business needs I would like to dissallow access from the VPN VLAN to any VLAN which contains sensitive data such as the DB in question...this may be impossible if you are a virtual company.

My threat model is as follows: A legitimate admin account's username and password is compromised. Our attacker uses that account to log in remotely and download the database. I understand MFA and other access controls would protect here, but assume they've failed or otherwise been circumvented. I'm merely curious of encryption's effect here.

As for the use of encryption...one could store encryption keys in a secure vault which gives role-based access on a need to know basis. This would mitigate the risk for the specific DB in question if this admin isn't the admin of this DB.

DarkMatter
  • 2,671
  • 2
  • 5
  • 23
  • 1
    "Any remote connection to your network should be protected by 2FA" -- Ah, I should've noted that in the question. I edited it to clarify. I'm aware of other ways to defend against compromised accounts, but I'm mostly curious as to whether the suggestion of encryption was a valid defense. Still, those answers are helpful to those who might come across this. – Kevin Mirsky May 09 '19 at 15:19
  • 1
    @KevinMirsky I edited to reflect the new threat model as well. – DarkMatter May 09 '19 at 15:26