Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
    
Can the 3rd person change over time? How will the 3rd person be identified? Will the 3rd person be the same for all combinations of (data, user) -- assuming (user,supervisor) is fixed at time of writing? This problem resembles a Byzantine Generals' Problem –  Henk Langeveld Aug 9 '12 at 14:51
    
Hi Henk, the 3rd person could be the CEO, for example. He can change over time. The 3rd person will be the same for all combinations of (data, user) at time of writing. I just read the Byzantine General's Problem, and I think you are correct, there are some similarities. –  Machado Aug 10 '12 at 13:12
add comment

4 Answers 4

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.

share|improve this answer
    
Sorry, I think I did not made clear that a DBA with access to the database should not be able to view the comments by SELECT'ting data. We don't want to give access to all the private keys tom someone, and the whole key-creation, delivering and management should be automated. –  Machado Aug 8 '12 at 21:36
    
You can easily automate the scheme that I'm suggesting, so I don't foresee that as a problem. Ultimately, some geek, some where, would be able to break the rules and look at the information if they so chose. Which is why I recommended putting auditing in place. If it's really, really, really a big deal, then create a separate DB and have the System password assigned by a trusted 3rd party or whoever. That can make maintenance a real PITA, so ponder carefully if that's the route you want to go down. You'll have the same problem or worse if you use certificates / keys. –  GlenH7 Aug 9 '12 at 2:49
    
How this would prevent a developer with the restored database to view the data ? –  Machado Aug 9 '12 at 12:15
    
As a side note, I just read both Key Recovery links you provided, and I am convinced that this is not what I want to do. –  Machado Aug 9 '12 at 12:34
add comment

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.

share|improve this answer
    
That's a good idea, but it would create a problem for automation. We would not have ways to automate creation of new keys without recompiling the DLL, and that would require some degree of programmer intervention, and we want the whole process to take place without any human intervention. –  Machado Aug 7 '12 at 12:52
add comment

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.

share|improve this answer
    
Thanks for the reminder (what, not how). Unfortunately we are not able to use anything else than SQL Server, as a Microsoft shop. –  Machado Aug 9 '12 at 12:16
add comment

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:

http://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'.

share|improve this answer
    
Answer out of scope, IMO. I'm not asking how to use Windows Authentication with AD. –  Machado Aug 8 '12 at 21:39
    
Your requirement was:•Data written by some user can only be read by that user and his superior and a 3rd person yet to be defined. My anwser meets that requirement. Please remove your down vote. –  JEC Aug 9 '12 at 21:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.