How to manage User Roles in a Database?

Question

Hi all,

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.

Thanks.

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

If you want to go the route of the 3 tables, you could create your tables like so:

Table       | Rows
User        | id ; name ; dob ; permission_id ; etc...  
Roles       | id ; add_post ; edit_post ; delete_post ; add_user ; etc...
Permissions | id ; user_id ; role_id 

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.