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');
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.
Source: http://sforsuresh.in/implemention-of-user-permission-with-php-mysql-bitwise-operators/
Cheerss..!!
