ASp.NET MVC EF4 SQL Table or Field level security

I have an slightly unusual security requirement, and I'm looking for advice on best practice, or at-least non-brittle approaches.

Scenario: Intranet system. Data about a number of related entities is to be considered as private. This is known as the unpublished part of the system. Only certain users can access this data. At some stage the users make a selection from this data, mark some records and publish them to the "published" side. The data published to the published side is removed from the unpublished side. The bulk of the systems users can then work on the records in the published side.

I know this sounds like a bool field on a parent record, flagging as published or unpublished, but let me explain how the system we are replacing tackles the issue - this is the level of security we need to emulate. It is a flat file database (Dataflex) with the unpublished and published data in separate tables, on separate server drives, backed up to separate tapes, in different rooms, with physical access by different people. Network rights ensure that the published users cannot get at the unpublished data. The software also manages rights, but if that goes wrong, or a programmer makes a mistake, they still can't access the wrong data. Sys admins who could potentially broach this data have positive vetted security clearance.

So what I am look开发者_StackOverflow社区ing for is advice on how to achieve an architecture with MVC, EF4, SQL that achieves this. At one extreme I build two systems with two sql databases, different rights: potentially with the differences contained within web.config so its actually the same codebase. At the other end of the scale its the flag in the table with controller methods marked up to deny access. (Thats no good because if a programmer messes up say a search query and it returns data from the wrong category, and it makes it out of the building unnoticed, its likely to result in human sacrifice, dogs and cats living together... mass hysteria)

Sorry for the lengthy description. Best practices or other advice on how to access this sought.

Not an MVC/EF-specific answer but there's an older Technet paper (for SQL Server 2005) that answers a lot of the issues and may be useful:

"Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005"

Available on Technet. The same paper can also be downloaded as a Word document.

Honestly, I don't believe EntityFramework is the ORM you are looking for. You are going to need more control than what it provides - things I am thinking about would be ability to set audit information on the connection so you know who the user is, prehaps run logon triggers and then maybe you want to have code that intercepts relationship and and entity and executes code security. if you are keen on using an ORM, then prehaps read this:

Nhibernate vs EntityFramework in the real world

If the information is as critical or secret as you say, database security is going to have to be implemented, it can't be enough to have app-level secutiry (think defence-in-depth).

Presumably you are deploying a webserver and an application server and connecting the two with wcf, so the quesiton shouldn't be what MVC is going to do, but how MVC, WCF and the ORM, if any, of your choice are going to interact.

Another way, prehaps, is to use updateable views in sql server that mask the unpublished data.

If all your entities share a common base class for its core identity fields it isn't that hard. As long as your entities all share a common base class you can make an extension method for that base which uses the key of the entity and the given id of the user attempting the operation, and you can validate if it is authorized in one line from your data access layer.

public static bool ActionIsAuthorized<T>(this T entity, ActionType actionType, string     actionBy) where T : BaseEntity
{
   bool authorized = false;
    //do your auth lookup here based on which kind of action they are performing. ActionTypes     is an enum
    return authorized;
}

And then just call it like this:

if(!YourEntity.ActionIsAuthorized(ActionType.Update, username))
{
    throw new ActionUnauthorizedException();
}
else
{
    //do data access stuff here
}