Custom ACL for Row-Level Security

Question

I have seen a few implementation of Row-Level ACL using a Permission table having a structure such as

User_Id 
Subject_Class
Subject_Id
Permission_Id

where Permission_Id is (Read, Write, Update, Delete, Approve, etc.)

I was wondering if there is any benefit to describe a relationship (Relationship_Id) with the data instead of describing a permission.

The idea we would to describe that a user is an "Owner", "Approver", "Reviewer", "Public Viewer", etc.

This relationship would then define a set of permissions. This could reduce the size of the Permission.

Any thoughts on this methodology?

Answer 1

Roles, as you note, are combinations of permissions. If you have a permission set of size n, you can have 2^n combinations of all of these permissions. This means that if you have 5 permissions, you'd need 32 roles. If you have 10 permissions, you'd have 1024 roles!

Using the permission route also appears to be easier for maintainability - say that you want to remove access permissions from a group of users. You'd just do a simple query to remove the rows that give access privileges to those users. But if you had roles, you'd first need to find all the roles that include access, remove those from the specific users, and then find all the roles that are like the previous role of each user minus the access permission, and assign those roles to the users. Seems like a lot of work.