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.
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.
-
Since sa is a systemadministrator, they have full access (no permission checking).
If normal accounts are used you can make views
CREATE VIEW ABC AS
SELECT ... FROM ...
WHERE entry.USER_ID= SUSER_SID
WITH CHECK
For the supermanager role you can make a second view without the restriction.
Encryption gives another layer (how to working with a where clause?). Is it worth the hassle since the private keys could been read from the database (sa)?
-
Without going to another layer, there's no stopping another member of the sysadmin role from accessing the keys. So if you're not going through, say, an application layer which the users do not have administrative access to, there's no way to stop someone.
K. Brian Kelley
@kbriankelley -
in the tables you can add an owner field and probably you have to create other tables for ownership hierachy.
then you can create two procs one for the super user and another for the secure user like
create proc cp_getBuys
and
create proc cp_getBuysSecure
the first proc gets all the data because its executed by superuser and the secured proc is run by other users who only get to see their data because you will limit them using the owner column in the table
-
True, but if all are sysadmins, there's nothing stopping them from running the superuser procedure.
K. Brian Kelley
@kbriankelley -
You forgot there are many 'sa' who can invoke any procedure if he wants
-
Well, 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?
-
Think of those with sysadmin rights being like a security custodian in the military. Technically the security custodian can get to anything. There's a level of trust that he/she doesn't. The same needs to be true for anyone you're willing to grant sysadmin rights there.
With that said, auditing after the fact is a way to discourage inappropriate access. If all those with sysadmin rights know there's a trace running 24 x 7, they'll tend to stick to what they should be doing. If this is backed with strong language as to the terms of one's employment... you get the idea.
K. Brian Kelley
@kbriankelley -
I think the only way to do that for sure would be to encrypt each person's data with a different key.
Or put the data on different servers with different sa logins. Sounds like a lot of extra trouble in any case.
With sa rights wouldn't they be able to disable any trace running on that server?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply