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

Question

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.

Answer 1

I Think bitwise operator are the best way to implement user permission. Here I am showing how we can implement it with Mysql.

Below is a sample tables with some sample data:

Table 1 : Permission table to store permission name along with it bit like 1,2,4,8..etc (multiple of 2)

CREATE TABLE IF NOT EXISTS `permission` (
  `bit` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`bit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert some sample data into the table.

INSERT INTO `permission` (`bit`, `name`) VALUES
(1, 'User-Add'),
(2, 'User-Edit'),
(4, 'User-Delete'),
(8, 'User-View'),
(16, 'Blog-Add'),
(32, 'Blog-Edit'),
(64, 'Blog-Delete'),
(128, 'Blog-View');

Table 2: User table to store user id,name and role. Role will be calculated as sum of permissions.
Example :
If user 'Ketan' having permission of 'User-Add' (bit=1) and 'Blog-Delete' (bit-64) so role will be 65 (1+64).
If user 'Mehata' having permission of 'Blog-View' (bit=128) and 'User-Delete' (bit-4) so role will be 132 (128+4).

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `role` int(11) NOT NULL,
  `created_date` datetime NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Sample data-

INSERT INTO `user` (`id`, `name`, `role`, `created_date`)
   VALUES (NULL, 'Ketan', '65', '2013-01-09 00:00:00'),
   (NULL, 'Mehata', '132', '2013-01-09 00:00:00');

Loding permission of user After login if we want to load user permission than we can query below to get the permissions:

SELECT permission.bit,permission.name  
   FROM user LEFT JOIN permission ON user.role & permission.bit
 WHERE user.id = 1

Here user.role "&" permission.bit is a Bitwise operator which will give output as -

User-Add - 1
Blog-Delete - 64

If we want to check weather a particular user have user-edit permission or not-

  SELECT * FROM `user` 
     WHERE role & (select bit from permission where name='user-edit')

Output = No rows.

You can see also : http://goo.gl/ATnj6j

Answer 2

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