0

I am attempting to create a small application which will have various user groups, around 6. I am attempting to figure out the best way to create the access control model. Here are few requirements:

  • The permissions should be easily editable as the application evolves and new permissions are needed or old ones become redundant
  • A user can be a member of multiple groups
  • Groups should have a flexible set of permissions which can easily be edited
  • Users can also have individual permissions which should override those stated in the group permissions controller

Here is the table layout I have come up with through researching various different methods/ideas.

enter image description here My thought was that by using this table structure I can run an SQL query like this (using the user_id from the currently logged in user):

SELECT users.id, users.forename, permissions.name
FROM permissions
INNER JOIN groups_permissions ON permissions.id = groups_permissions.groups_permissions_permissions_id
INNER JOIN groups ON groups_permissions.groups_permissions_groups_id = groups.id
INNER JOIN groups_users ON groups_permissions.groups_permissions_groups_id = groups_users.groups_id
INNER JOIN users ON groups_users.users_id = users.id
WHERE users.id =4
UNION 
SELECT users.id, users.forename, permissions.name
FROM permissions
INNER JOIN users_permissions ON permissions.id = users_permissions.users_permissions_permissions_id
INNER JOIN users ON users_permissions.users_permissions_users_id = users.id
WHERE users.id =4

That should give me a combined list of permissions, including all group and any user specific permissions. I can then cache this query locally so I don't need to run it multiple times. (I guess I will setup some kind of method to clear the cache if the permissions table is changed.)

Does this seem like a reasonable plan or are their obvious negative aspects that I am overlooking?

Thanks

user1098178
  • 697
  • 3
  • 9
  • 25
  • I'm only adding a comment, but I've researched this user/group idea very deeply for my own past project. One idea I found useful was for users to not have any permissions, but only inherit them from the groups that they're members of. (that is, users belong to groups, groups hold permissions). I also borrowed heavily from Linux filesystem access concepts (i.e., permissions are defined at the file level, rather than user account level). Lastly, I was inspired by this whitepaper: https://resources.docs.salesforce.com/sfdc/pdf/salesforce_record_access_under_the_hood.pdf hope I helped somehow – SomeDude Dec 19 '16 at 23:06
  • Hi SomeDude, thanks for your comment. Can you tell me why you decided to go down the just group permissions route? Don't you end up with lots of groups if you, for example, want to adjust one or two permissions only on a few users? What are the benefits of using just the groups? Thanks – user1098178 Dec 20 '16 at 09:13
  • My main reason for a groups-only approach was to simplify where my users would view/change settings. Groups make it easier to change a few settings for many users, which I felt was more likely to happen. In fact, if you expect many users of your program, use groups like this to simplify mass changes. If you expect only a few users, then use only users (no groups). It's an economics of scale here. Users and groups cause such a mess with exactly these situations that some designers have disposed of groups and users in favor of just "roles," like many database products including PostgreSQL. – SomeDude Dec 21 '16 at 15:39
  • An example topic: http://stackoverflow.com/questions/8485387/why-did-postgresql-merge-users-and-groups-into-roles – SomeDude Dec 21 '16 at 15:46

0 Answers0