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.
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