MySQL - Structure for Permissions to Objects

Posted by Kerry on Stack Overflow See other posts from Stack Overflow or by Kerry
Published on 2010-06-13T18:39:27Z Indexed on 2010/06/13 18:42 UTC
Read the original article Hit count: 250

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql