MySQL - Structure for Permissions to Objects
- by Kerry
What would be an ideal structure for users permissions of objects.
I've seen many related posts for general permissions, or what sections a user can access, which consists of a users, userGroups and userGroupRelations or something of that nature.
In my system there are many different objects that can get created, and each one has to be able to be turned on or off. For instance, take a password manager that has groups and sub groups.
Group 1
Group 2
Group 3
Group 4
Group 5
Group 6
Group 7
Group 8
Group 9
Group 10
Each group can contain a set of passwords. A user can be given read, write, edit and delete permissions to any group. More groups can get created at any point in time.
If someone has permission to a group, I should be able to make him have permissions to all sub groups OR restrict it to just that group.
My current thought is to have a users table, and then a permissions table with columns like:
permission_id (int) PRIMARY_KEY
user_id (int) INDEX
object_id (int) INDEX
type (varchar) INDEX
read (bool)
write (bool)
edit (bool)
delete (bool)
This has worked in the past, but the new system I'm building needs to be able to scale rapidly, and I am unsure if this is the best structure. It also makes the idea of having someone with all subgroup permissions of a group more difficult.
So, as a question, should I use the above structure? Or can someone point me in the direction of a better one?
