How to design a hierarchical role based access con

Question

Basic deal is, we have a custom built "kickstart" for our projects. For this we are looking at redoing the user control. I know there are a lot of questions out there about general rbac, but I cannot find any on hierarchical rbac?

Our requirements are:

• Roles can be assigned to group permissions
• If the role does not have a permission entry then it is automatically denied
• A user can be given overriding permissions
• A users overriding permissions is either a grant or deny
• If a user is explicitly denied a permission no matter what roles say "granted" the override wins.
• Users can have multiple roles
• Roles can have hierarchy
• Roles can inherit from other roles (e.g. A "Forum Super Moderator" role is a "Forum Moderator", and a "System Maintainer", and the "Forum Moderator" role already inherits from the "Forum User" role )
• Roles that inherit from another role that deny or grant a privilege override their child permission
• Permissions are grouped by "module" (e.g. a "Blog" module can have an "edit entry" permission, and a "Forum" module can have an "edit entry" permission and they will not clash)
• There is a "Everything and Anything" permission that automatically grants full access

So, with those requirements out of the way, here's how I am thinking of doing it.

Table: Users

id            | int     | unique id

Table: Roles

id            | int     | unique id
--------------|---------------------------------------------
title         | varchar | human readable name

Table: Permissions

id            | int     | unique id
--------------|---------------------------------------------
module        | varchar | module name
--------------|---------------------------------------------
title         | varchar | human readable name
--------------|---------------------------------------------
key           | varchar | key name used in functions

Table: Role_User

role_id       | int     | id from roles table
--------------|---------------------------------------------
user_id       | int     | id from users table

Table: Permission_Role

id            | int     | unique id
--------------|---------------------------------------------
permission_id | int     | id from permissions table
--------------|---------------------------------------------
role_id       | int     | id from roles table
--------------|---------------------------------------------
grant         | tinyint | 0 = deny, 1 = grant

Table: Permission_User

id            | int     | unique id
--------------|---------------------------------------------
permission_id | int     | id from permissions table
--------------|---------------------------------------------
user_id       | int     | id from users table
--------------|---------------------------------------------
grant         | tinyint | 0 = deny, 1 = grant

Well, actually that's half of it, that part I am sure about, the part I am getting stuck on is the hierarchical roles.

So, how do I design this? My idea is that to save on the database queries I am just going to build the permission matrix on login and save it to session so the queries don't have to be too simple as they are only run once for each login.

The issue I see is that, I am going to need to know the hierarchy of the roles so I can resolve the inherited roles permissions before I resolve the inheriting.

The user permissions is the easy part, the per-user permissions are essentially the finally resolved group.

Answer 1

There is a way to implement role inheritance by using recursive relation on table Roles, by making role reference to another record:

This relation will add 1 : n inheritance within Roles record. You might obtain whole hierarchy tree with this stored function:

CREATE FUNCTION `getHierarchy`(`aRole` BIGINT UNSIGNED)
RETURNS VARCHAR(1024)
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE `aResult` VARCHAR(1024) DEFAULT NULL;
DECLARE `aParent` BIGINT UNSIGNED;

SET `aParent` = (SELECT `parent` FROM `Roles` WHERE `id` = `aRole`);

WHILE NOT `aParent` IS NULL DO

    SET `aResult` = CONCAT_WS(',', `aResult`, `aParent`);
    SET `aParent` = (SELECT `parent` FROM `Roles` WHERE `id` = `aParent`);

END WHILE;

RETURN IFNULL(`aResult`, '');
END

Then, you might obtain all granted permissions with something like this:

SELECT
    `permission_id`
FROM
    `Permission_Role`
WHERE
    FIND_IN_SET(`role_id`, `getHierarchy`({$role}))
    AND
    grant;

If it's not enough, then you might do another table for inheritance:

But, in this case, needed another hierarchy obtainment algorithm.

---

To resolve overriding issue you will have to get role permissions and user permissions. Then, write user permissions over roles permissions to session.

---

Also, I suggest to remove grant columns in Permission_Role and Permission_User. There is no need to map every permission for each of them. Just enough to use EXISTS queries: if there is a record, then permission granted, else - it's not. If you need to retrieve all permissions and statuses, you might use LEFT JOINs.