strategy to filter data according to user access level

Question

we currently have a very simple security schema...

we have resources, that roughly maps to tables, we have access to that resources (add, modify, delete, query) and we have groups.

each permission consists of a resource, with a specified access and a group

and each user can belong to many groups...

so, permission is a many-to-many between group, access and resource

and we also have a many-to-many between user and group.

this is just fine for our needs...

what I'm trying to think is a method to grant permission to data, at the record level, with a similar scheme. I need a way to "filter" records according to the user access level.

for example, the users belonging to a certain group can see all records of a table (resource), but users from another group can only see records which satifies a specific condition, that they see the data filtered...

I was thinking about adding a "expression" field to the permission table, so that when accessing a certain resource the filter is applied (in fact it would be a little more complicated, I would have to apply each filter of the groups to which the user belongs, joined with an "or")

I'd like it to be as general and configurable as possible...

How would you handle such a use case?

Answer 1

I would highly recommend looking into an ORM (Object Relational Mapping) framework that has the ability to construct dynamic queries. The basic idea would be that you would construct criteria in application code based on the logged-in user's security, and the framework turns this into SQL that gets executed on the server (so you are not pulling all records into the app tier and filtering there). The difference between this approach and using straight dynamic SQL is that the ORM will allow you to write type-safe code, where straight dynamic SQL is string-based, which makes it prone to human error.

Some of these ORM frameworks come with authorization functionality out of the box, which may (or may not) be different than what I described above, but may also get the job done.

I know for sure that LLBLGen Pro has a very powerful dynamic query engine, and supports row-level authorization. I am not an expert on NHibernate or the Entity Framework, but I'm sure that they also have this support.

Even if you aren't going to use an ORM for persistence (their main purpose), it may still be worthwhile to give them a look for their dynamic query features.