How to Design a Hierarchical Role Based Access Control System

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 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.

Designing a role based access control with support for relations

You shouldn't implement a DB model for access control. Your DB model should have the objects you care about (students, teachers, classes...). The rest should be expressed as policies in an externalized authorization model. This is called Attribute-Based Access Control abac. It's also known as policy-based access control (PBAC). Different names, same thing.

In ABAC, you have attributes on the one hand (the fields of your tables in your DB model e.g. username, role, course year...) and policies on the other. For instance:

• Teachers can edit the grade of a student in a class they teach
• Students can view their own grades
• A student can view the course material of a class they belong to

All these are examples of (business) authorization policies.

In ABAC, you then have the notion of a Policy Decision Point (PDP) which evaluates authorization policies and a Policy Enforcement Point (PEP) which intercepts business requests and sends off authorization requests to the PDP. For instance:

• Can Alice the student view grade #123?
• Can Bob the teacher view the profile of student Alice?

The PDP replies with a decision, either Permit or Deny, which the PEP must then enforce. You mention that you have an MVC model for your application. Your PEP would likely be an interceptor or annotation in your controller layer.

There are several ABAC languages and implementations out there:

• open-source:
  
  
  • Open Policy Agent based on the Rego language
  • AuthZForce based on the XACML standard.
• closed-source: Axiomatics Policy Server based on the XACML and ALFA standards.

Hierarchical role/permissions based access

I recommend going with a variation of "Approach #1" - non-hierarchical Roles.

I've worked with similar systems with great success. While at first this approach may appear 'less structured' it is relatively simple and is very flexible; when allowing multiple Roles-per-User and defining rules for aggregating permissions.

Against Hierarchies (for Roles)

Like an 'OO hierarchy', using a role hierarchy leads to a strict substitution-relationship. This makes it harder to define roles based on changing requirements.

For example, maybe there is a future need for an 'Administrator' account which cannot create their own posts. The hierarchy (and the substitution-relationship it has) prevents this without changing the tree structure itself because a "Full Administrator" is-a "Paid User".

Queries against a true hierarchy are more complex in SQL; especially in implementations that do not support recursive queries, like MySQL. Switching to a hierarchy using a nested-set or materialized approach forces an additional structure over just a parent-child relation.

You Just Don't Need It; the more complex software is the harder it is to write and maintain. While hierarchies are very good in certain cases - such as with a Bill of Material or Genealogy or Directory Structure - there is simply not a 'need' in most Role/Group-Permission models.

For (multi) Roles

Roles, without a 'parent type' dependence, function more like 'OO interfaces' - well, maybe Trait composition would be more apt if analogies are to be stretched. The implementation (read: granted Permissions) of each Role can change independent of any other Role making it extremely flexible. And like interfaces, more than one Role can be assigned to a given User/entity.

Queries against a flat User <M-M> Role <M-M> Permission model are much simpler in SQL (with or without recursive support, or additional structure) because there is simply no Role hierarchy to traverse.

Windows ACL Groups (let's ignore nesting groups) work much like Roles; a User belongs to one or more Groups which grants (or denies, but that's a different situation) Permissions.

Have your Cake and Eat it Too

The variation I recommend, and have hinted to above, is to allow aggregation of permissions across Roles. A simple aggregation model is this:

• A User has the union of Permissions from all the Roles they are assigned.

  (The effective permissions would generally be built during authorization, but without a hierarchy it is also relatively simple to query in SQL.)

Thus the Permissions are bound per Role with little or no overlap, as show in "Approach #2", with this difference: There Is No Hierarchy.

For example, to allow a special administrator who can search for posts (and delete 'bad' posts), one would simply assign the "Basic User" and the "Limited Administrator" Roles¹.

Using a non-hierarchical multi-Role capable system allows this cleanly, shrugging off the burden of a hierarchy, while still providing flexible/composable/configurable Role archetypes.

---

¹ This is not a particular good example. In reality the Roles should have different names (eg. "Account Support" or "Content Moderator") and cover different Permission sets; these will likely change over time based on trial and error and flavor-of-the-month business rules.

While I've spoken against a hierarchy for such, in more complex systems there may be a need to allow relationships between Roles, primarily for grouping of such. This relationship should generally be independent of the effective Permissions applied, existing for other managerial purposes.

Typed Hierarchical Access Control System

Anyone with access to Policy's constructors can take a Policy apart and put it back together, possibly in a nonsensical fashion. Don't expose the Policy constructor outside of this module. Instead, provide a smart constructor to create policies that are guaranteed to be well-formed and expose a Monoid interface to compose them without breaking invariants. Keeping the Policy type abstract ensures that all the code which could result in nonsensical policies is kept inside this module.

{-# LANGUAGE GeneralizedNewtypeDeriving #-}

module Policy (
    Role(..),
    Level(..),
    Policy,  -- keep Policy abstract by not exposing the constructor
    can
    ) where

import Data.Semigroup (Semigroup, Max(..))

data Role = Public | Contributor | Owner
    deriving (Eq, Ord, Bounded, Enum, Show, Read)
data Level = None | View | Edit
    deriving (Eq, Ord, Bounded, Enum, Show, Read)

Below I'm using GeneralizedNewtypeDeriving to borrow a pair of Monoid instances from base: the monoid for functions, which lifts another monoid through the function arrow point-wise, and the Max newtype, which turns an Ord instance into a Monoid instance by always choosing the larger of mappend's arguments.

So Policy's Monoid instance will automatically manage the ordering of Level when composing policies: when composing two policies with conflicting levels at a given role we'll always choose the more permissive one. This makes <> an additive operation: you define policies by adding permissions to the "default" policy, mempty, which is the one which grants no permissions to anyone.

newtype Policy = Policy (Role -> Max Level) deriving (Semigroup, Monoid)

grant is a smart constructor which produces policies which respect the ordering properties of Role and Level. Note that I'm comparing roles with >= to ensure that granting a permission to a role also grants that permission to more privileged roles.

grant :: Role -> Level -> Policy
grant r l = Policy (Max . pol)
    where pol r'
            | r' >= r   = l
            | otherwise = None

can is an observation which tells you whether a policy grants a given access level to a given role. Once more I'm using >= to ensure that more-permissive levels imply less-permissive ones.

can :: Role -> Level -> Policy -> Bool
(r `can` l) (Policy f) = getMax (f r) >= l

I was pleasantly surprised by how little code this module took! Leaning on the deriving mechanism, especially GeneralizedNewtypeDeriving, is a really nice way of putting the types in charge of "boring" code so you can focus on the important stuff.

---

Usage of these policies looks like this:

module Client where

import Data.Monoid ((<>))
import Policy

You can use the Monoid class to build complex policies out of simple ones.

ownerEdit, contributorView, myPolicy :: Policy

ownerEdit = grant Owner Edit
contributorView = grant Contributor View
myPolicy = ownerEdit <> contributorView

And you can use the can function to test policies.

canPublicView :: Policy -> Bool
canPublicView = Public `can` View

For example:

ghci> canPublicView myPolicy
False

---

Related Topics