Public-private key pair handling on a Windows ecosystem

Question

I've been thinking about how to architect an infrastructure for one of our business applications with the following requirement:

• Data written by some user can only be read by that user and his superior and a 3rd person yet to be defined

I'm thinking in using some sort of asymmetric algorithm, such as RSA for data encryption.

We can generate a public-private key pair for each user and store the public key of each one openly on a database, but how should we handle the private key ?

If we just let the users manage their private key one day we will have a big headache if one of them loses his key.

If the private key becomes public somehow then the data would become public too, rendering the architecture invalid.

Our infrastructure is heavily Microsoft-oriented: All applications are ASP.Net MVC3, our dabatase is SQL Server 2005 and we use Active Directory for user authentication and basic data, such as e-mail/phone number.

How would you guys architect a solution to solve this problem ?

Bottom-line: This is not a banking or financial application, it's some kind of 360-degree personal feedback system.

EDIT: Adding more requirements, hoping to make it more clear:

• This database will be in production environment, where developers don't have access;
• When the database is restored, developers should not be able to recover the data;
• The DBA should not be able to view the data by SELECT'ing the rows on the tables;

EDIT 2: Bounty info. Bounty went to the answer with more upvotes, although my personal feel is that none of the answers provided addressed the question correctly. For this reason, I'm granting the bounty but not marking the answer as correct.

Answer 1

I would actually recommend against using a key recovery type scheme as you're suggesting. In general, it's complicated, difficult to maintain, and there are easier ways of achieving your goals.

Schneier has some older articles on key recovery, with some embedded links to additional information. They don't directly discuss your scenario, but there is a strong degree of relevance.
Key Recovery
Key Recovery #2

Instead, I would recommend using a hierarchical access model.

For simplicity, I'm assuming that the review comments are being stored in some sort of SQL DB structure.

Each entry will be tagged with two identifiers. First is the employee's DB ID, the second is a group ID. Groups will be defined based upon departments.

The user access table will keep track of the employee DB ID (could also be the primary key), what department ID the employee contributes to, and what department ID the employee can read from.
The "contributes to" department ID will be the department the employee belongs to. The "can read from" department ID field would only be set if the user is a supervisor or is the "3rd party to be designated later".

Selects against the 360 review entries can then be joined against the user access table.

When supervisors change, all that has to be updated is the department ID they can read from. Given your MS environment, it should be pretty easy to set up a scheduled task to synchronize AD information with access in the user access table.

One caveat: It's worth noting that the DBAs will have access to all of the information. Put some auditing / tracking in place to discourage their curiosity. You would still have a similar problem with an encryption key scheme, although it might have been a different person with that ability to circumvent the security settings.

Some additional reading on securing the DB
You'll find the following links of interest for your particular configuration. I selected MS SQL 2005, but the information for other versions can be switched with the drop down at the top of the pages.
MSDN DB Security considerations
Encrypting a column
Backup / Restore security

I think it's important to keep the overall sensitivity / security level of the application in mind. You said this was for 360 style feedback, which I rate as needing to protect to prevent embarrassment / foster open communications. A data breach wouldn't have catastrophic effects upon the company.

Given the relatively low and temporal value of the data, you shouldn't have too many backup files sitting around. If people are afraid that what they said 3 years ago is going to come back to haunt them then they'll circumvent the feedback process and be less than forthcoming in their reviews.

If you're afraid of some lone wolf in the organization who will crack open the feedback and then spread the juicier tidbits, then I'm afraid you've got larger issues that aren't going to be solved with technology. How to deal with that really isn't OT for P.SE since technology generally can't solve personnel problems. Put some reasonable restrictions in place and have the CEO note that willfully violating those restrictions is grounds for termination.

Answer 2

I would load the keys into an obfuscated dll and use active directory information to authenticate the user's key. if done properly, the user will never know their key and it couldn't be decompiled to discover the keys.

Answer 3

Just a thought, you might be getting bogged down in the HOW and forgetting the WHAT. What you want is keep reviews anonymous to anyone but the user, the person who supervises him, and one other person (possibly a third party auditor). I would keep the public/private key as one option, but not the only option. It sounds like you need to create a graph situation where relationships grant certain privileges. I've been looking at using something like Neo4j for a business-oriented internal social graph. You could have more luck using something like this than your traditional relational database. Neo4j can be queried by http/https (so you can do this from MVC). I'm kind of new at playing around with graph databases, but they look promising for doing a lot of these kinds of things.

Answer 4

We have what sounds like a similar system. We had to set up a table with login names matching the Active Directory that specifiy who is whom's superior. If you are using MVC3 with Windows Authenication you should be able to see who is looking up who. Here's an example:

https://stackoverflow.com/questions/1267071/how-to-get-windows-user-name-when-identity-impersonate-true-in-asp-net

One thing to look out for is that people will want to give confidential information about there superiors. Allow feedback to be specified 'for HR only'.