SQL Server 2005 Encrypting column by more than one key?

Question

SQL Server 2005 allows you to encrypt a column like a social security number but we have user belonging to different subsidiaries that we want to be able to view only the encrypted SSNs on their records but not the others.

Creating different tables for each subsidiary becomes too difficullt to program for and manage.

Different columns for each subsidiary requires too much maintenance when a new subsidiary is added.

So I am looking for a solution where the records you create you can read but not those that other subsidiaries create.

Answer 1

This is an interesting problem, you don't say how you know which person is in which subsidiary. That's going to have an impact on how to limit the rows...



On the other hand, for a column like SSN, I would want to know that my SSN is protected from anyone that doesn't absolutely need to see it. Maybe having a column for the last 4 digits that is not encrypted would help. Also, you could use column level security to not allow that column to be viewed without a certain level of security.



You could also use a stored procedure or view to only return back rows in the current user's subsidiary so that the other rows aren't returned when the user is looking for SSN.



I hope this gives you a starting point for some ideas.



Good luck!

Jeff

Answer 2

Since SQL 2008 has been out for a while I would stay away from 2000 and focus on 2005 and 2008. Don't assume any shop will have a single version of the DB around. We are currently deploying 2008 for new systems we setup and existing 2005 systems will be migrated over the next year. I wouldn't waste any time on 2000 unless there is a specific job you are going for that uses it.

Answer 3

You need to secure your data by row, the problem is that you can't. A 'Row' is not a securable in SQL Server 2005.



The best you can manage in this scenario is to create a Stored procedure that takes the subsidiary ID as a parameter.

Answer 4

You will have to use the CLR tools to create a special stored procedure to do that. You cannot do it natively. Sorry.