My last question about this subject was totally misunderstood, at stackoverflow, asp.net forums and code project! But now, I know more about my needs and requirements and what it's actually called. I've read this question and I'm also familiar with CSLA .NET. Is there any more straight forward way to implement table or even better row level security in Entity Framework 6 and ASP.NET MVC 5? In fact this custom approach sounds much better and easier. What do you think based on your experience?
For some reasons, our users need to have their Database logins. But we don't want them to be able to miss around. So the perfect solution is that authentication and authorization happens at DB level. I'll be thankful if someone was able to clarify the steps and process more. What I have come up with so far is to have different DB
logins for different users and use that
login to connect to database. So when a new user is registered, actually a new database login is created and security permissions are all implemented at database.
I'm not sure what I have to do for this need. How it should be implemented in the database and can it be done with Entity Framework or I should write it by hand? Any out of the box solution? Any explanation about this subject is welcome.
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.:
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.:
SELECT * FROM medicalrecords
SELECT * FROM medicalrecords WHERE medicalrecords.assignedDoctor='Alice'
The following links may be useful to you: