SQL Server Performance Forum – Threads Archive

How to secure the SQL table’s content?

Title is not precise. I am in a team where each member has been assigned the ‘sa’ role, except for me. (Crazy!) Now, my manager wants an app: suppose there is a table, each member in this team has different content in this table, each team member is supposed to view their own own content only. However, the manager is a super user, he can view everyone’s content in this table. Please again don’t forget each member is ‘sa’ in the SQL database, this means the content must be encrypted. How to implement this model: Should I generate a private key for each member and store in another table, then use this key to encrypt each member’s content? so that only he can decrypt his own content. But how about the super manager’s role? Do you have any idea?
Thanks in advance. R.X.
Toronto, Canada
MCSD.NET

If they do not have access to the server through EM or QA, only through a client app, then you can create a VIEW that does the filtering according to the login name, and only give a client app that is based on that view. In that case you would not need to worry about encrypting data. But this is futile as long as the users are sysadmin, because there is nothing much you can disallow a sysadmin.

Thanks for your response. Unfortuntely yes they can access to the server through EM/QA. This is an odd and challenging request, really, is it possible to make it? R.X.
Toronto, Canada
MCSD.NET

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Halifaxdal This is an odd and challenging request, really, is it possible to make it? <hr height="1" noshade id="quote"></blockquote id="quote"> Being the cynical, mean-old-dba that I am; I smell a wtf coming… <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

If they want the secrecy, they can’t grant those same users sysadmin rights – they can either have the cake, or eat it.

Hiya, What I’ve done for a password database (i.e. a SQL database storing passwords) is write a .net application to read/write the database based on userid/group and get it to encryptdecrypt all of the passwords using 3DES. In our case the system could be circumvented only by being a domain administrator or by getting the private key used for the encryption/decryption which was an acceptable risk for us In your case it could be more difficult to control access if all of the users can also do things like change their group membership on the domain i.e. you can’t have your cake and eat it to. If you make everyone god-like then you can’t prevent them from spying on eachother <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

I’ve just finished a book review about cryptography in the database, which should be pretty soon here on this website. Now that I am starting to explore this thing, I believe the only way is to encrypt the data in the front-end and store this encrypted data in the database. That you’ll already figured out yourself. Since almost anyone seems to be sa, it yields you nothing, when you generate keys and store them in another table. This data could easily be viewed by anyone. So, I guess the way to go, would be to implement the complete permission logic in the front-end. In combination with the encrypted data they can use EM and/or QA as long as they want, they only see the plain data when using the front-end. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Hi folks, thank you for the discussion. I am not the one who grants god role to everyone except me, so don’t feel bizard for this idea, just want to find a solution. I am thinking there should be lots of similiar senarios in military application, because it’s impossible for letting sa to know the content of a database, even if he is a sa, right? Letting everyone view his own content in plain is not a problem since I can generate private key for everyone and they can change/update their private key. Is knowing another person’s private key a potential risk? Well, yes or no. It depends on which layer you have the access. If the many sa can access only the SQL layer (lower layer), then what they can view is just tons of junks, even they know the key, they don’t know which method I am using to encrypt, am I right? So as long as I block them to the application layer, say, they don’t know which method I am using to encrypt, then I am safe. But if they know the encrypt method, then, there is trouble again. (I assume this barely happen) The challenge key issue is how to let the super user (not sa) view all the contents? Because he has only his own key, he presumebally can decrypt only his own content. How to do this?
R.X.
Toronto, Canada
MCSD.NET

Hi ya, in terms of military systems, they’d tend to have security cleared people working as administrators and yes they do have the ability to see everything in the database… I think that the only way you can manage this is by having a super secret private key embedded in the application, and have the access control in that same application too that does make the application the weak point, but otherwsie I think that the management of the private keys is going to become too onerous… afterall you can’t get a user to change their private key without having to re-encrypt all of the data that they’ve stored to date. In terms of relying on the super user not knowing the encryption algoritm… based on the key there is only a few secure algorithms anyway… It’s like having the right key to a door which has multiple key holes. It wouldn’t take that long before you worked out which hole to use ultimately again, if the data is to be totally secure then don’t store it in a single place where everyone has access… why is it all in one database if they can’t see eachother’s data anyway? yes there is one user who should be able to see it all, but if all users are sa of their own sql instance, then the super user can be sa of all sql instances? just throwing this in there as an option… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

What about installing a ‘personal’ instance of SQL Server for each user. The personal instances contains a database with no user tables, but only views, which in turn query views on the secret instance. The views on the secret instance resolve the filtering according to login name – of course they’ll have to use specific logins, not "sa". Then they won’t need to have sysadmin access to the secret instance, right?

]]>