How to manage User Roles in a Database?

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_Name1       Standard User2       Super User3       Guest

PERMISSION Table

Permission_ID Permission_Name1             View User List2             Update Own User Account3             Update Any User Account

ROLE_PERMISSION Table

Role_ID Permission_ID1       1    // Role 1 (Standard User) grants View User List1       2    //        and Update Own User Account2       1    // Role 2 (Super User) grants View User List,2       2    //        Update Own User Account,2       3    //        and Update Any User Account3       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 

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 userAfter 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 - 1Blog-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

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.