Control access to parts of a system, but also to certain pieces of information

Question

This is a tricky question, we've been talking about this for a while (days) and haven't found a convincingly good solution. This is the situation:

• We have users and groups. A user can belong to many groups (many to many relation)
• There are certain parts of the site that need access control, but:
• There are certain ROWS of certain tables that need access control, ie. a certain user (or certain group) should not be able to delete a certain row, but other rows of the same table could have a different permission setting for that user (or group)

Is there an easy way to acomplish this? Are we missing something?

We need to implement this in python (if that's any help).

Answer 1

It's hard to be specific without knowing more about your setup and about why exactly you need different users to have different permissions on different rows. But generally, I would say that whenever you access any data in the database in your code, you should precede it by an authorization check, which examines the current user and group and the row being inserted/updated/deleted/etc. and decides whether the operation should be allowed or not. Consider designing your system in an encapsulated manner - for example you could put all the functions that directly access the database in one module, and make sure that each of them contains the proper authorization check. (Having them all in one file makes it less likely that you'll miss one)

It might be helpful to add a permission_class column to the table, and have another table specifying which users or groups have which permission classes. Then your authorization check simply has to take the value of the permission class for the current row, and see if the permissions table contains an association between that permission class and either the current user or any of his/her groups.

Answer 2

1)create a table with rights, ie delete, update, etc

2)create a three way pivot table on the rights table, whatever table you want row level access for and whatever table contains the unit of access rights (either group or user).

3) check for a relationship in the pivot table before you allow the operation to proceed.

your rights table could look like:

ID   RIGHT
1    DELETE
2    UPDATE

the table that you want row level access control for could look like (say a blog for example):

ID  TITLE           CONTENT
1   blog entry 1    This is a blog entry
2   blog entry 2    This is another blog entry

and your user table could be:

ID   NAME
1    Bob
2    Alice

Then the pivot table would be like

ID USER_ID RIGHT_ID BLOG_ID
1  1       2        1
2  2       1        1
3  2       2        1
4  2       1        2
5  2       2        2

This means that Bob can only update blog entry 1 but Alice can update or delete either blog entry

EDIT: If you want a right to come from the user or the group then you need two pivot tables for each table; one for users and one for groups. You will also have to query the database to check for user level rights and group level rights before you allow or disallow an operation

EDIT2: This is more complicated than David's solution but doesn't require you to compose permission_classes ahead of time: you can mix and match whatever group level and user level permissions you want which is what it seems like you want to do.

Answer 3

Add additional column "category" or "type" to the table(s), that will categorize the rows (or if you will, group/cluster them) - and then create a pivot table that defines the access control between (rowCategory, userGroup). So for each row, by its category you can pull which userGroups have access (and what kind of access).