asked
How to prevent rogue dba
Hi All, Sorry if this sounds really bad and is egg on us DBAs faces. But I have come across a scenario where absolute trust was placed in the DBAs BUT one of the high level employees decided to do a runner after deleting data. This prompted a response from management where there is a clear need to establish protocols where if someone takes data stored in our database is useless if taken. There is already TDE in place. There is encryption on connections and transactions after. I suggested the idea of a certificate to encrypt the database BUT thats redundant due to TDE as well as the suggestion was raised what if the network guys cloned the machine and took off. A bit of paranoia atm but I think its a legit request. I am after something where we can encrypt data before any CRUD is performed. Or if someone else can suggest any other way to tackle this. I have said that You cant do much if your head of intelligence takes off :):):). They shared my view but suggested that lets look into it. Any help, ideas will be much appreciated. BTW we use sql server 2012 enterprise edition Regards, Waqar
If you use SQL Server 2016, you can implement Always Encrypted on sensitive data columns. That encrypts the data in such a way that a DBA can not read the content (or rather only gets to read encrypted content). It's not 100% failsafe, since DBAs more often than not are also windows admins on application servers and can therefore obtain or use the column encryption keys. But it makes it a bit harder, and it is at least in theory possible to segregate duties in such a way that the DBA have no access to the certificate used to encrypt/decrypt the column contents. Read more about Always Encrypted:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
In that case, a DBA is a DBA, usually with sysadmin privileges and therefore can read all data in the database. If you want to hide data from the DBA, implement encryption/decryption of sensitive data in the application, so that whatever is stored in the database can't be understood from simply querying the tables. Some things should be encrypted/hashed regardless if you trust your DBA or not - eg credit card numbers and passwords.
Sorry should have mentioned. We used 2012 enterprise edition
I know. I thought about that but I was trying to find a way which would justify the means to an end and I think i found it in the form of symmetric keys. My other half of the problem is integrating my solution in MVC5 using EF6. :)
Encryption at Application level is the way to go. I have found that the use of symmetric keys which can be configured to be used with EF6 when using MVC5 is possible. You can use your db context to open your symmetric key. This key is obviously encrypted against the master key of the instance. The password of the key should only be stuck in the application. Please don't use it in stored procedures (on instance level or db level in any scenario) as DBA can obviously see the code for a sp. Make sure after your transaction has taken place, you close the key. Not sure if it closes itself once the connection is gone. If someone can clarify that it would be great. Please note that you cant encrypt all columns as this is going to create a massive overhead as well as a problem for design and storage as you would need the column to be a varbinary type and I am not sure about the length but I have kept mine at 128. You would open key, write or update your record converting your input to varbinary using the key to encrypt and similarly decrypt when reading it. Now if the DBA makes away with the database, all critical columns which could identify an entity are garbled. Unless a DBA knows the password which was used to create the key, He/She is going to be pretty helpless. I believe on top of that, the masterkey wont be present on the instance on which he/she is trying to open the database and read those record. If a DBA or networking person can clone the whole instance and run away with it, He/She is still without help i think as Unless you can get into the application code and get the keys its not possible. Obviously, you ll find that the internet is littered with the fact that you gotta protect your keys. I also found tutorial on youtube on how to break TDE. I believe that might have been off the back of a non encrypted backup. BUT not sure. But i think that the use of symmetric key protects data on a much better scale, obviously when combined with TDE, connection encryption, Bitlocker etc.. Thanks, Waqar
17 People are following this question.
