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 see you've had no answers yet - I'm certainly not sure of what the right answer is for you, but here's my view for what it's worth.

I might have misunderstood, but if I was trying to enforce fine-grained access controls for roles/groups to resources, I would do it in the application rather than in the data storage solution.

I don't know if that's an option for you in this case, but if you do that security in the data layer, you might run into problems down the line with a lack of flexibility and perhaps even vendor lock-in if you end up using proprietary extensions.

Very interested in any supporting/conflicting perspectives here.

Answer 2

Your idea about using 'expressions' for filters and dynamically creating SQL statements (right?) to filter data should work. The only other real alternative would seem to require a fixed set of filter 'axes' on which user access to records could be defined. Deciding between the two basic mechanisms requires more details about your application. As an (obvious) example, in my employer's application every client (record) is assigned to an 'office' and we provide security mechanisms for controlling which users can access clients in which offices. This is implemented using the second mechanism I describe because it's so crucial to our application design. If you're building more of a 'meta-app', you may wish to implement a more general solution so you can change this behavior more readily.

Answer 3

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.

Answer 4

We have an AccessControlEntry table that looks like this:

CREATE TABLE [dbo].[AccessControlEntry]
(
    [subjectId] [nvarchar](256) NOT NULL,
    [objectType] [varchar](256) NOT NULL,
    [objectId] [int] NOT NULL,
    [permission] [varchar](50) NOT NULL,
    [flag] [int] NOT NULL DEFAULT (0),
    [applyToChildren] [int] NOT NULL DEFAULT (1),
    CONSTRAINT [PK_AccessControlEntry] PRIMARY KEY CLUSTERED ([subjectId] ASC, [objectType] ASC, [objectId] ASC, [permission] ASC)
)

The user id we are setting permissions for is the subjectId, the objectType and objectId identify the object we are setting permissions on (the Resource in your terminology). In our case we have individual records for each permission (i.e. List, View, Create, Modify, Delete) - but you could just as easily have a column for each permission.

We then created a table-valued function that accepts a subjectId, objectType and optionally an objectId and returns the permissions.

Once you have that, you end up with this table valued function showing up in pretty much all of your queries, so you can filter what gets returned by the permissions the user has on the Resource.

Since our data is hierarchical in nature (kind of like Business Units in a company) our permission system handles a hierarchy of permissions and inheritance. I am not familiar with your data model, so I have no idea of you have a requirement for that as well - but the idea is similar to how you apply security permissions to directories and files.