How to build Role-Based Access Control in SQL?

Role-based access control (RBAC) is a method of regulating access to computer or network resources based on the roles of individual users within an enterprise. (see here for more detail)

In above diagram, we model the relationship between User (u), Group/Role (g), and Permission (p) by three tables, i.e. u_g, g_p, and g_g, which are flexible enough to cover any trees or networks. All the following statements are true in this model:

• a user can be linked to many groups;
• a group can be linked to many users;
• a permission can be linked to many groups;
• a group can be linked to many permissions;
• a group can be linked to many children groups;
• a group can be linked to many parent groups;

However, the difficult part of this model is how to deal with hierarchies of roles/groups in SQL (see here, here, and here for detail) due to the recursive nature of navigating between group (g_g) relationship.

One solution is to use SQL stored routines. Followings are my design in MySQL and you can simply:

• call get_permisions('u2');
• call get_users('p1');

to get all permissions of a user or all users who have a permission.

-----------------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS test.get_users;
CREATE PROCEDURE test.`get_users`(p VARCHAR(20))
BEGIN
      SELECT group_concat(gID)
        INTO @groups
        FROM g_p
       WHERE pID = p;

      SELECT DISTINCT uID
        FROM u_g
       WHERE FIND_IN_SET(gID, parents(@groups)) > 0;
   END;

DROP PROCEDURE IF EXISTS test.get_permisions;

CREATE PROCEDURE test.`get_permisions`(u VARCHAR(20))

BEGIN

      SELECT group_concat(gID)

        INTO @groups

        FROM u_g

       WHERE uID = u;

      SELECT DISTINCT pID

        FROM g_p

       WHERE FIND_IN_SET(gID, children(@groups)) > 0;

   END;

DROP FUNCTION IF EXISTS test.children;

CREATE FUNCTION test.`children`(parents VARCHAR(255)) RETURNS varchar(255) CHARSET utf8

BEGIN

      DECLARE x   VARCHAR(255);

      SET @@SESSION.max_sp_recursion_depth = 25;

      CALL get_children(parents, x);

      RETURN x;

   END;

DROP PROCEDURE IF EXISTS test.get_children;
CREATE PROCEDURE test.`get_children`(IN  parents    VARCHAR(255),
                                     OUT children   VARCHAR(255))
BEGIN
      DECLARE x   VARCHAR(255);
      SET children = parents;

      SELECT group_concat(DISTINCT child)
        INTO x
        FROM g_g
       WHERE FIND_IN_SET(parent, parents);

      IF (x IS NOT NULL)
      THEN
         CALL get_children(x, x);
         SET children = concat(parents, ',', x);
      END IF;
   END;

DROP FUNCTION IF EXISTS test.parents;

CREATE FUNCTION test.`parents`(children VARCHAR(255)) RETURNS varchar(255) CHARSET utf8

BEGIN

      DECLARE x   VARCHAR(255);

      SET @@SESSION.max_sp_recursion_depth = 25;

      CALL get_parents(children, x);

      RETURN x;

   END;

DROP PROCEDURE IF EXISTS test.get_parents;

CREATE PROCEDURE test.`get_parents`(IN  children   VARCHAR(255),

                                    OUT parents    VARCHAR(255))

BEGIN

      DECLARE x   VARCHAR(255);

      SET parents = children;

      SELECT group_concat(DISTINCT parent)

        INTO x

        FROM g_g

       WHERE FIND_IN_SET(child, children);

      IF (x IS NOT NULL)

      THEN

         CALL get_parents(x, x);

         SET parents = concat(children, ',', x);

      END IF;

   END;