Implementation of User permission with PHP & Mysql Bitwise operators.

Another way to implement user permission in your application and also a good use of bitwise operator. This can be done with PHP as well as Mysql. 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;
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');

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

Hope this will help someone!!!!

Cheerss..!!

Originally published at http://sforsuresh.in.