How to design a hierarchical role based access control system

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 DETERMINISTICREADS SQL DATABEGINDECLARE `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.