How to manage User Roles in a Database?

Question

I'm creating a website in which I will be managing users and their permissions. I am looking to implement user roles, and can't seem to wrap my head around how things should work. I want to be able to assign a user a certain role, and have each role contain one to several permissions that can be read easily by my script. Just want to know how I should go about setting up my database to do this easily and efficiently.

In my head I picture 3 tables (users, roles, and permissions). I could give each user a role id that joins the roles table, I just don't know how I can link roles to several permissions.

Answer 1

I just don't know how I can link roles to several permissions.

You use a join table: role_id and permission_id to identify what permissions are associated with which roles

EDIT:

Example tables

ROLE Table

Role_ID Role_Name
1       Standard User
2       Super User
3       Guest

PERMISSION Table

Permission_ID Permission_Name
1             View User List
2             Update Own User Account
3             Update Any User Account

ROLE_PERMISSION Table

Role_ID Permission_ID
1       1    // Role 1 (Standard User) grants View User List
1       2    //        and Update Own User Account
2       1    // Role 2 (Super User) grants View User List,
2       2    //        Update Own User Account,
2       3    //        and Update Any User Account
3       1    // Role 3 (Guest) grants View User List

Listing the permissions for a specified Role_ID

select R.role_id,
       P.permission_id,
       P.permission_name
  from role R,
       permission P,
       role_permission RP
 where RP.permission_id = P.permission_id
   and RP.role_id = R.role_id
   and R.role_id = 1 

Answer 2

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

You can see also : http://goo.gl/ATnj6j

Answer 3

This is how I usually what I do:

You define a set of permissions whose meaning varies from target object to target object, but whose general meaning is the same. For instance:

• read
• write
• append
• delete
• delete contents
• read permissions
• change permissions

Then you assign a bit to each of those:

class Perms {
    const read           = 1;
    const write          = 2;
    const append         = 4;
    const delete         = 8;
    const deleteContents = 16;
    const readPerm       = 32;
    const changePerm     = 64;
    /* shortcuts */
    const fullControl    = 127; 
    const noControl      = 0;
}

Then for each type of object you have a table where you insert pairs (user, perms), (group, perms), (role, perms) or whatever you want to associate with the permissions.

You can query the permissions of the user (which may have several roles) like this:

//this will depend on the database
//you could also use whatever bitwise OR aggregate your database has
//to avoid the foreach loop below
$query = new Query(
    "select perm from objects_permissions as P ".
    "where P.id_object = \$1 and " .
    "   (P.role = any(\$2));",
    $obj->getId(), $user->getRoles()
);

$perms = 0;
foreach ($query as $row) {
    $perms |= $row['perm']; 
}

You can also add deny permissions with little difficulty.