User role permissions for different modules using bitwise operators

Question

So I have an application that has several modules (think of modules as different pages), each module has a set of permissions; view, add, edit, delete

I want each user role to have privileges for each module, for example

Role A Permissions

Module 1 -> view
Module 2 -> add, edit
Module 3 -> view, add, edit, delete
etc.

How can I design the database to support that and how would I go about implementing it using bitwise operators (or would there be a more efficient way for this particular case?)

I already have the user, user_role and role tables but I'm unsure on how to design the Module table.

"think of modules as different pages" - in that case you will usually (/always?) only need to check exactly one permission, like "is x allowed to 'edit' y?" per request? — VolkerK, May 27 '10 at 22:40
well for instance lets say that there's a Contact module which is a contact management system, not all groups can have access to that module or might have limited access (view) etc. So if a group is allowed to edit module A, they might not be allowed to edit module B — Serge, May 27 '10 at 23:16

score 9 · Answer 1 · edited Jan 03 '22 at 16:23

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

Click here for more information.

score 5 · Accepted Answer · answered May 27 '10 at 22:43

5

If you decide to use a bitmask, remember that the number of permissions you can keep track of is limited (you can track 31 permissions in a signed 4-byte integer database column). Each permission would then be assigned a value that is a power of two (1, 2, 4, 8, etc), and you could perform bitwise operations to check for permission matches.

From what you're looking to accomplish, I would suggest creating a role_has_module_privs table instead. This approach is much more scalable, and more efficient from a querying perspective. But if you have a finite number of combinations, bitmasks may be more efficient.

answered May 27 '10 at 22:43

Kenaniah

5,171
24
27

I'm already familiar with `bitmask` I just need to know if there's a way to track permissions for each module. Like I said each module only has 4 different permissions and even if I decided to add more permissions later on I doubt I'll ever go over 31 permissions. Not quite sure how your approach will work, do you mean that `role_has_module_privs` will contain something like **id_module**, **id_role**, **permissions** – Serge May 27 '10 at 23:21
Very close. Schema would probably look like **id_module**, **id_role**, **id_permission**. Using your example, module 3 would have four records in this table. Your primary key would be a composite of **id_module** and **id_role**. – Kenaniah May 28 '10 at 06:46
Performance/maintainability wise what would be better, using bitmask (less rows in db but more lines of code on each page) so that each role will on have one row in `role_has_module_privs` or use your way (have more rows, an extra table but probably less code on each page) – Serge May 28 '10 at 13:47
In general, it's probably a wash. I would recommend one row per permission as that better adheres to the rules of database normalization. – Kenaniah May 28 '10 at 17:58

User role permissions for different modules using bitwise operators

2 Answers2

Linked