1

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!?

  • Can you have meta tables that store the authorization information on the rows of the actual tables? – Parth Shah Jun 4 '15 at 7:03
  • Yes, that can be. But the preference is SQL Server permissions. – Akbari Jun 5 '15 at 0:40
  • If the preference is SQL Server permissions, then have you had a look at Granting Row Level permissions in SQL Server: msdn.microsoft.com/en-us/library/bb669076%28v=vs.110%29.aspx? If so, what requirements are not being met by the advise offered there? – Parth Shah Jun 5 '15 at 1:58
  • Thanks Parth, I'm more clear now. Using stored procedures and views do the job. I'm OK with the DB Security now, I just don't get how it'll be related to the app security. For example, how should I implement authentication into my app, when all users are database credentials? – Akbari Jun 5 '15 at 2:24
  • 1
    This is now easily achievable with SQL Server 2016's built in row level security. – Mark and Mollie Aug 12 '16 at 19:11
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
}
0

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 and enable attribute-based access control (also known as ).

The following links may be useful to you:

  • Thanks for your replay David, but I need that level of filtering directly at the database, not in a proxy, because users will have their database credentials and with dynamic data masking they're still able to select anything they want from database. – Akbari Jun 2 '15 at 2:34
  • Not if you force the connection via the proxy. You can use the user's db credentials. Alternatively the only on-db solutions I am aware of are oracle and ibm fgac – David Brossard Jun 2 '15 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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