Table or row level security in ASP.NET MVC 5 and Entity Framework 6

Question

I've already asked this question on Stack Overflow, but no one answered, so maybe it's a more appropriate place to discuss it. Please let me know if I'm wrong and having duplicated question on different networks is not acceptable.

I'm using ASP.NET MVC 5, Entity Framework 6 and SQL Server 2008 to build my Intranet web application. Two security needs have been assigned for this project and I'm not able to change them.

1. Application logins to database should be permission limited. Remember it's an Intranet app and will be installed on customer's server. They'll have the database login but still we don't want them to be able to mess around. The suggested approach to implement this requirement was to use database logins for app authentication. In other words, every user will have his or her own database logins. App doesn't have any default login, so when user tries to login into app, authentication controller tries to use that login credential to connect to the database. On success, user will be authenticated into the application, otherwise the login action will fail.

2. Authorization at row level. Each user and therefore database login will have not just limited permissions on tables but also on rows. I think I can implement this need in the app alone and forget about the database.

I'm so confused, any guidance and personal experience about these requirements is welcome. I think I need to have an authentication system that works this way and is able to use database logins. Can I override MS Identity? And what else does this project need!?

Answer 1

I will post an alternate solution to the one that is proposed in MSDN's "Granting Row Level permissions in SQL Server": https://msdn.microsoft.com/en-us/library/bb669076%28v=vs.110%29.aspx.

In my solution, you have a meta table for each table where row level permissions are required. I like to call that meta table as an ACL (Access Control List) table. So as an example, your target table is called Contacts, then you create another table call Contacts_ACL. Now in that table, I put in Contact_Id, User_Id and Permission (or Permission_Id if we have a separate Permissions table).

create table Contact (
    contact_id INT PRIMARY KEY,
    ...
);

create table Contact_ACL (
    contact_id INT REFERENCES Contact(contact_id),
    username varchar(100),
    permission varchar(20)
);

I do this because I feel Stored Procedures is an overkill for something that is very simple (authorization). It also imposes additional code maintenance as everything you change the base table, you have to change the views and the stored procedures.

Here is an example LINQ Query to retrieve stuff:

from contact in Contact
join acl in Contact_ACL on contact.contact_id equals acl.contact_id
where acl.username == <username>
select new {
    contact.contact_id,
    ...,
    acl.permission
}

Answer 2

You need something called dynamic data masking.

The company I work for, Axiomatics (disclaimer - I work for that company), has a policy-driven solution that achieves data filtering and masking. It means that based on policies and attributes, it is possible to define what a user can SELECT / INSERT / DELETE ...

The way it works is that you define a policy e.g.:

• User with the role == doctor can do the action == SELECT on the table == MEDICALRECORD if and only if userId == assignedDoctor.

Then, between the application and the database, you deploy a proxy which will intercept the flow and append the relevant SQL filter statement (a WHERE clause typically) e.g.:

• SQL intercepted: SELECT * FROM medicalrecords
• WHERE clause generated: WHERE medicalrecords.assignedDoctor='Alice'
• Final SQL statement sent to the db: SELECT * FROM medicalrecords WHERE medicalrecords.assignedDoctor='Alice'

The policies are in a standard format called xacml and enable attribute-based access control (also known as abac).

The following links may be useful to you:

• Blog post on data filtering
• Blog post on data masking
• Demo video of how it all works