Best Practice for Designing User Roles and Permission System?

Question

I need to add user roles and permission system into my web application built using PHP/MySQL. I want to have this functionality:

1. One root user can create sub-roots, groups, rules and normal users( all privileges) .
2. Sub-roots can create only rules, permissions and users for his/her own group ( no groups).
3. A user can access either content created by him or his group, based on the permission assigned to him, by group root.

I need the system to be flexible enough, so that new roles and permissions are assigned to content.

I have a users table storing group key along with other information. Currently I am using two feilds in each content table i.e. createdBy and CreatedByGroup, and using that as the point whether a certain user has permissions. But its not flexible enough, because for every new content, I have to go throug all data updates and permission updates. Please help me by discussing your best practices for schema design.

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 then 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://sforsuresh.in/implemention-of-user-permission-with-php-mysql-bitwise-operators/

Answer 2

The pattern that suits your needs is called role-based access control.

There are several good implementations in PHP, including Zend_Acl (good documenation), phpGACL and TinyACL. Most frameworks also have their own implementations of an ACL in some form.

Even if you choose to roll your own, it'll help you to review well factored solutions such as those.