phpmysqlpermissionsbit-manipulation

Permissions: bitwise operations or many-to-many child table?


I am trying to understand the best way to work with permissions and as far as I'm aware there are two major options.

The first option is to use bitwise operations, and for this I would use the following database structure:

users

user_id | user_permission
---------------------
1       | 15
2       | 1

permissions

permission_id | permission_name
-----------------------
1             | Read
2             | Write
4             | Execute
8             | Delete

And then to check that the user has permission I would use the operation:

$user_permission & $permission_id

The main benefits I see to this are:

  1. Trivial to set, get, and validate permissions
  2. Less storage (no child database; no additional rows per user permission)

The main drawbacks I see to this are:

  1. Listing users' permissions slightly more complicated
  2. Cannot use foreign key constraints
  3. Limited permissions (64 if using BIGINT)

The second option is to use a many-to-many child table, and for this I would use the following database structure:

users

user_id
-------
1      
2      

permissions

permission_id | permission_name
-----------------------
1             | Read
2             | Write
3             | Execute
4             | Delete

user_permissions

user_id | permission_id
-----------------------
1       | 1
1       | 2
1       | 3
1       | 4
2       | 1

And then to check that the user has permission I would use the operation (where $user_permission is an array of permission_ids):

in_array($permission_id, $user_permission);

The main benefits I see to this are:

  1. Can use foreign key constraints
  2. Trivial to list users' permissions
  3. Allows for a far greater number of permissions

The main drawbacks I see to this are:

  1. Greater storage (child database; additional rows per user permission)
  2. Setting and getting permissions slightly more complicated

Question

Which of these would be the better option? I see benefits and drawbacks to each and am unsure which would be more suitable. Although I am aware that context probably plays a role; so in which situations would bitwise operations be better and in which would a many-to-many child table be better? Or is there a third option of which I'm unaware?

I'm currently more inclined to use a many-to-many table for the benefits of foreign key constraints and a greater number of permission possibilities, but I wonder if I'm missing something else; bitwise operation permissions seem to be quite prevalent so I'd assume there is a good reason for using them.


Solution

  • I would not go with the bitwise operations solution. Unless you are really really cramped for space, breaking this out into its own table and mapping table won't cost that much disk. It would be easier for people who aren't you to understand, and you can more easily enforce FK relationships this way. Also, as you mentioned, the number of permissions can grow virtually without limit. Depending on how you index the table, queries like "show me all users with Read permission" seems like it would be quicker to execute and easier to understand (that's subjective, I realize).