User rights in a webserver, CRUD is too course grained

Question

In our user-rights management system we basically follow "CRUD", where at each incoming request the user is checked against his rights, and see if he has create, read update or destroy rights for the data he wishes to access.

This system is created by my predecessor, however I'm constantly in doubt if this is the right system to use. Say I have an "admin" user, which is below the "rootadmin" - admins can modify the accounts but not the server itself. Rootadmin could do anything, including resetting the whole server and other such fun things.

However since it can modify user accounts it has UPDATE access on the "role-field" of a user, and could update any user to any level, including "rootadmin". So it seems simple CRUD is not enough.

---

Another example (to make it clear that this is a case that's not unique to rootadmin privileges):

An employee is given rights to update the production status of "items". However each employee works only in a part of the full company: so he shouldn't given rights to update (or read) items that are outside his production line.

---

Is there a well known method to handle user rights, in a generalized system? Or does everyone roll his own implementation for each application a new set of rules?

---

My only solution so far is to store "lambdas" in the database, where together with the right, there is a "limitation". It would then read: "user X has right Y (update field A in B) but only if lambda Z is returns true.

However this would either make the database really language dependent, and store raw code on the database. Or one would have to write another layer to interpret some kind of dsl.

Answer 1

Its not clear from the question if the users have direct access to the db or if it is just coming from the application that is making use of the db. As mentioned by Robert you can do things in the db layer to control permissions via views etc.

Another option depending on where in the system you have access to code is easiest for you is to add a repository model between the database access and the rest of the code above the db layer. You could then make a system to generate custom data access objects that apply the permissions granted to the user ie: set all fields to null/no change that the user doesn't have permission to edit/read etc as data passes between the view and the model.

The benefit of that is your permission/security system becomes testable without the need for a database. You can mock out all sorts of weird roles with different permissions and confirm whether or not a user with that role has access to do things as expected without having to touch the database.

Answer 2

Wherever you say you need lambdas, you can always co-opt the database to do the work for you. For example, Project Managers normally have access to only the projects they are managing. That's simply a WHERE clause with two conditions.

For security at the column level, make better use of Views. Views can be used in combination with mechanisms in both SQL Server and Oracle to enforce column-level security.

Further Reading
Filtering SQL Server Columns Using Column Level Permissions
SQL Server column level security