Mysql – Best Practice for Designing User Roles and Permission System

databasedatabase-designmysql

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.

Best Solution

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/