Role Based Access Controls (RBAC) with context?

Question

I'm trying to implement RBAC, but a User can have multiple roles, each under a particular context.

The context is based on 3 factors, Company, Product and Region.

So User A could be an Admin of Company 1, and also be a Viewer of Company 2 > Product 3 > Region 4

What is the best approach for this kind of RBAC set up?

I'm hand rolling this at the moment - so looking for the best way to structure the DB tables that would allow for this level of granular access.

Unfortunately this is on a legacy application with runs PHP CodeIgniter / mySQL - so any modern existing libs are probably not compatible.

UPDATE

I have this so far. Permissions table maps to Roles, Roles are then assigned to users, and given context.

account_id | role_id | company_id | product_id | region_id |
------------------------------------------------------------
     1     |   1    |            |            |            |    
     2     |   2    |      1     |            |            |    
     2     |   3    |      2     |    1       |            |    
     3     |   4    |      3     |    1       |     2      |
     3     |   4    |      3     |    1       |     3      |
     3     |   4    |      3     |    1       |     4      |

The SQL for this looks a bit like this...

SELECT DISTINCT account_id
FROM   user_roles ur, role_permissions rp
JOIN permissions p ON rp.permission_id=p.id

WHERE (
    #correct user that has a role with the permission we want
    ur.account_id = 1 
    AND rp.permission_id IN (1,2,3) 
    AND ur.role_id = rp.role_id

    #if the user is sys admin they can see everything
    AND (
        ur.role_id = 1

        #if the user is company admin
        OR (  ur.role_id = 2 AND ur.company_id=@cid )

        #if the user is product admin
        OR ( ur.role_id = 3 AND ur.company_id=@cid AND  ur.product_id=@pid )

        #if the user is any other role then the need explicit access
        OR ( ur.role_id > 3 AND ur.company_id=@cid AND ur.product_id=@pid AND ur.country_code=@cc )
    )

);

This works, but I'm sure there must be a better way to do this.

The next issue is how to then apply a hierarchy to the roles?
So a 'Admin' of a company > product > region can only create users, of their role or lower?

I would have to look up the role they have assigned at the context we are in, and then return every role who has a lower rank than the users one?

Could this context be better placed as a string? '1.3.gb' / '1.2' ? And then when looking up the role, I form the context string first?

Jaquarh Jaquarh · Accepted Answer · 2016-06-08T13:45:41

User table can have a cross reference to a roll table, each roll has a specific role identification number with a can_write, can_read column (ect...)

Your heircachy could look something like this:

Users:
    uid(int) auto_increment primary
    role_id(int)

Roles:
    rid(int) auto_increment primary
    description(varchar)
    // any access values ie: can_read(int)

Then you could set-up a sub roles table which allows viewings on regions.

Some example code for the accessing of these tables:

$stmp = (new PDO(...))->Prepare("SELECT role_id FROM Users WHERE uid = :userid");
$stmp->bindValue(":userid", $id); // I'd suggest storing a login key using sessions
$stmp->execute();
$user_permissions = $stmp->Fetch()['role_id'];

// Reference the Roles table for permission lists
$stmp = (new PDO(...))->Prepare("SELECT * FROM Roles WHERE rid = :roleid");
$stmp->bindValue(":roleid", $user_permissions);
[...]

Hope this helped.

Role Based Access Controls (RBAC) with context?

1 Answers