MySQL - Structure for Permissions to Objects

Question

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
admin (bool)
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.

There will be a separate table for roles of users/admins, which means they can change the permissions on users below groups they can control.

So, as a question, should I use the above structure? Or can someone point me in the direction of a better one?

---

EDIT

Alternative is to create a permission table for every type of object.

Answer 1

I suggest you add a "last_update" timestamp and a "last_updated_by_user" column so you have some hope of tracking changes to this table in your running system.

You could consider adding a permission -- grant. A user having the grant permission for an object would be able to grant access to other users to the object in question.

Be careful with "needs to scale rapidly." It's hard to guess without real-world production experience what a scaled-up system really needs.

Also, be careful not to over-complicate a permissions system, because an overly complex system will be hard to verify and therefore easier to crack. A simple system will be much easier to refactor for scaleup than a more complex one.

Your schema seems to relate users to objects. Do you want your primary key and your unique index to be (user_id, object_id)? That is, do you want each user to have either zero or one permission entry for each object? If so, use the primary key to enforce that, rather than using the surrogate permission_id key you propose.

For your objects that exist in hierarchies, you should make one of two choices systemwide:

1. a grant to an object with subobjects implicitly grants access to only the object, or...

2. it also grants access to all subobjects.

The second choice reduces the burden of explicit permission granting when new subobjects are created. The first choice is more secure.

The second choice makes it harder to determine whether a user has access to a particular object, because you have to walk the object hierarchy toward the root of the tree looking for access grants on parent objects when verifying whether a user has access. That performance issue should dominate your decision making. Will your users create a few objects and access them often? Or will they create many objects and subobjects and access them rarely? If access is more frequent than creation, you want the first choice. Take the permission-granting overhead hit at object creation time, rather than a permission-searching hit at object access time.

I think the first choice is probably superior. I suggest this table layout:

user_id (int)
object_id (int)
type (varchar)  (not sure what you have this column for)
admin (bool)
read (bool)
write (bool)
edit (bool)
grant (bool)
delete (bool)
last_update (timestamp)
last_updated_by_user_id (int)
primary key = user_id, object_id.

You could also use this table layout, and have a row in the table for each distinct permission granted to each user for each object. This one scales up more easily if you add more types of permissions.

user_id (int)
object_id (int)
permission_enum (admin/read/write/edit/grant/delete)
type (varchar)  (not sure what you have this column for)
last_update (timestamp)
last_updated_by_user_id (int)
primary key = user_id, object_id, permission_enum