A simple user permission model for sophisticated systems

Introduction

A friend asked me to detail the permission system that we have evolved over the years for use in some applications I maintain. These systems are ERP solutions wherein managers need to manage users' access to certain features and entities. These applications have been in continuous development for 15+ years and have implementations at some customers that have passed the 10 year mark. I think what we've arrived at is relatively easy for users to understand and manage and for programmers to utilize.

The system could be utilized in any front-end language, but we store most of the business logic in MySQL so that node/graphql and c++ front-ends can get the exact same behavior. Of course, if you have a back-end API you implement this there and gain the same benefits. My examples will all be in MySQL.

Goals:

• Allow users to be assigned to groups (user definable)
• Create a table for all possible permissions (defined by devs)
• Allow permission access levels to be granted directly to users or to groups (user definable)
• Implement functions for checking permission at a particular level (can be checked by back end code)
• Properly handle granting access when a user can have a user-level access as well as group-level access from any number of groups they are part of (users can grant privileges, and the procedures need to handle it correctly).

Terminology:

• Site: many systems incorporate the idea that a company can span many plants/stores/sites and users can have access to one or more of these. I may use store/storeid interchangeably with site/siteid. Admins can grant users access to any number of sites. This controls where they can log in as well as which entities and relationships they can modify given their permissions and site ownership.
• Access Level: a user or group can be granted a permission at None (no access), Site (access at all sites the user has been granted), or Global (access to all site-owned objects as well as global configurations).
• Group: something that users can be part of. Groups can represent responsibilities or roles in the system or job titles or other configuration levels. We let the user define these. Permissions can be granted to groups
• Private Sites: a private site is one in which a user is denied access unless they are logged into that site, even if they would normally have access at that site.

Abstract Schema (not language dependent):

• useraccount: should have login info, but no permissions or sites, those are defined later
• site: a list of locales the business operates in. Simpler systems may eschew this if they believe their customers will never be multi-location. I'd suggest doing it though, as its harder to add in later than it is to ignore in the short-term.
• usersite: many to many table for granting a useraccount access to a site
• permission: defines each permission with a short user visible description group: defines groups that users can be part of and permissions can be granted to
• usergroup: many to many table for users' membership in groups
• userpermission: a place where users can be granted permissions Globally, at the Site level, or not at all
• grouppermission: a place where groups can be granted permissions Globally, at the site level, or not at all

I'll leave the implementation of most of these tables to the user, as they'll be mostly application-specific, but I'd like to show a couple tables MySQL implementation to talk about suggestions.

Access:

This system generally boils down to asking the question: for the given user and a given permission, what level of access do they have. The answer could be None, Site level access, or Global access. If a user has Global access, they should be allowed through, and the opposite is true for None level. If they have site level access, it will depend on the sites they have access to (through usersite) and the site that owns the document/object that the user would like to modify or create at.

When checking access for modifying an entity or relationship, the site that owns the object should be considered. Often, this is the same site the user is logged into in their session, but depending on your system, they may be able to see/modify documents and objects from other sites. In such a case, the owner site is the one the user needs access at to perform the operation. For example, if they load a document created at another site, and they don't have a usersite entry for it, they won't have access unless they were granted 'Global' permission. Also, if they want to create a document at that site, they will need access as well.

In this system, if a user does not have a userpermission row for a given permissionid and is not part of any group that has a grouppermission row for a given permissionid, they are assumed to have the None access level (access denied).

Combining Access From More Than One Source:

As is probably evident from this design, a user can have permissions granted directly and they can be part of any number of groups, each of which could have their own access levels. This design operates on a UNION model, essentially granting the user the 'most generous' access level that they have access to from any source they are part of. I believe this is generally how role-based permissions work in modern operating systems as well.

So, if a user has no userpermission row for permission SALES_ORDERS_CAN_EDIT, and is part of a 'Salespeople' group that has that permission at the Site level, but is also part of the 'Sales Managers' group that has that permission at the Global level, they would effectively have Global access level for permission SALES_ORDERS_CAN_EDIT.

Later, I'll show an example MySQL stored function that, given a useraccountid, a permission's codename, and a site, can return a boolean answer as to whether the user should be granted access.

Example MySQL Implementation:

useraccount:

CREATE TABLE `useraccount` (
  `useraccountid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT 'User''s login',
  `passwordkey` varbinary(64) NOT NULL COMMENT 'The result of secure password encoding of users password',
  `passwordsalt` binary(32) DEFAULT NULL COMMENT 'The unique salt for this user',
  `resettoken` varchar(10) DEFAULT NULL COMMENT 'If the user begins a password reset, this is a one-use token for that',
  `resetexpiration` datetime DEFAULT NULL COMMENT 'If the user begins a password reset, this is when the token expires',
  `status` enum('Pending Activation','Active','Locked','Deactivated') NOT NULL DEFAULT 'Pending Activation' COMMENT 'Status of the user account and whether it is usable',
  `locknotes` varchar(100) DEFAULT NULL COMMENT 'Used to tell the user why they''re locked out',
  `workemail` varchar(320) NOT NULL DEFAULT '' COMMENT 'Contact email address',
  `recoveryemail` varchar(320) DEFAULT NULL COMMENT 'A password recovery email. Must be set by user, not admin for highest security',
  `firstname` varchar(100) NOT NULL DEFAULT '' COMMENT 'User''s first name',
  `lastname` varchar(100) NOT NULL DEFAULT '' COMMENT 'User''s last name',
  PRIMARY KEY (`useraccountid`),
  UNIQUE KEY `username` (`name`)
) ENGINE=InnoDB;

In the useraccount table, make sure you use a real, secure password algorithm, not a basic/cheap hashing algorithm (MD5, SHA). Some examples are bcrypt, scrypt, PBKDF2. Also make sure your salts are generated per user, not globally defined. You can read elsewhere about secure password systems and why they're better than (the recently deprecated) built-in PASSWORD function in MySQL or basic hashing algorithms like MD5 or others. Make sure you store output character arrays in binary format to take up less space.

Also, if your system does password resetting, it is best practice to generate a timed (the limit is negotiable) one use token that will work for only that account.

permission:

CREATE TABLE `permission` (
  `permissionid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique identifier',
  `category` varchar(250) NOT NULL DEFAULT '' COMMENT 'a logical grouping of permissions',
  `displayname` varchar(250) NOT NULL DEFAULT '' COMMENT 'a name to show the user',
  `codename` varchar(100) NOT NULL DEFAULT '' COMMENT 'The key used to reference this item in code',
  `description` text COMMENT 'a full description of the permission',
  PRIMARY KEY (`permissionid`),
  UNIQUE KEY `location` (`category`,`displayname`),
  UNIQUE KEY `codename` (`codename`)
) ENGINE=InnoDB;

This table is pretty straightforward, just define all permissions that can be granted in the system. Category is for grouping permissions into logical areas of the system (for admins browsing and setting permissions). codename is special because it allows a unique behind-the scenes key for looking up permissions that does not rely on the ids being the same on every system deployment. This way code, can refer to more developer-oriented permission names like SALES_ORDERS_CAN_EDIT or SALES_ORDERS_CAN_VIEW instead of id 1 or 2.

I'll skip over the connector tables and some of the more simple ones, as you can figure out a system-appropriate implementation for you without much trouble.

Access Management Schema:

userpermission/grouppermission:

CREATE TABLE `userpermission` (
  `userid` int(10) NOT NULL,
  `permissionid` int(10) NOT NULL,
  `value` enum('None','Site','Global') NOT NULL DEFAULT 'None' COMMENT 'Amount of this permission the user has (none/at his Site/at all Sites)',
  PRIMARY KEY (`userid`,`permissionid`)
) ENGINE=InnoDB;

CREATE TABLE `grouppermission` (
  `groupid` int(11) NOT NULL,
  `permissionid` int(11) NOT NULL,
  `value` enum('None','Site','Global') NOT NULL DEFAULT 'None',
  PRIMARY KEY (`groupid`,`permissionid`)
) ENGINE=InnoDB;

userpermission is for those times when specific users should be granted access levels instead of through their membership in a specific group. One could design a system where there are no user level permissions, and instead only group permissions. In this system, the user admin would have to create a group with only one member if they wished to give specific users access overrides.

grouppermission grants an access level to an entire group, which many users may be part of.

Setting Access:

To grant access, the simplest solution would be to do:

INSERT INTO grouppermission (groupid, permissionid, `value`) VALUES (1, 3, 'Global') ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

This would create or override a permission level for a given group to a given access level. I leave as an exercise a similar query for userpermission.

Internally, we have a stored procedure that sets a user or groups permission level to create a database-level API, but your mileage may vary.

Checking Access:

So the most interesting thing to most devs would be checking to see if a user has a given permission at a given level in order to perform a task. This can be done at the database level with user defined stored procedures or a system can download and cache the current user's permissions in memory or redis or something. For simplicity I will demonstrate directly in MySQL.

Psuedocode For Checking Access:

The following represent language independent psuedocode for how to check a user's access level for a given permission at a given site:

function: user_has_access
Inputs: user_id, permission_code_name, site_id
Return: TRUE or FALSE
SET permission_id = get_permission_id_with_permission_code(permission_code_name)
SET user_access_level = 'None'

IF (permission_id IS NULL)
   RETURN FALSE

IF (user_permission_table_contains(user_id, permission_id))
   SET user_access_level = user_permission_level(user_id, permission_id)

for each_group_user_is_member_of(user_id)
   SET current_group_access = group_permission_level(group_id, permission_id)

   IF (current_group_access > user_access_level)
       SET user_access_level = current_group_access

IF (user_access_level = 'Global')
   return TRUE
ELSE IF (user_access_level = 'None')
   return FALSE
ELSE
   # The user has site level permission, we need to ensure that they have been granted access to the site in question
   IF (user_site_table_contains(user_id, site_id))
       return TRUE

return FALSE

MySQL Example Stored Procedures:

Here is an example implementation of checking a user's permission at a given site in MySQL:

DROP FUNCTION IF EXISTS `f_check_user_permission`;

CREATE FUNCTION `f_check_user_permission`(nUserID INT, strPermission VARCHAR(100), nSiteID INT) RETURNS tinyint(1)
    READS SQL DATA
    DETERMINISTIC
    COMMENT 'Checks a permission for a particular user at a specific Site'
BEGIN
    DECLARE nPermissionID INT;
    DECLARE nUserAccess INT;
    DECLARE nGroupAccess INT;
    DECLARE strPermissionLevel VARCHAR(50);

    SELECT permissionid INTO nPermissionID FROM permission WHERE codename = strPermission;

    IF (nPermissionID IS NULL) THEN
        RETURN NULL;
    END IF;

    # Access means the user has either:
    # 1. Site permission and this is one of their Sites.
    # 2. Global permission and either (2a. this is one of their Sites or 2b. this is a public Site)
    # Get the ability to access it at a user level
    SELECT COUNT(*) INTO nUserAccess FROM userpermission LEFT JOIN usersite ON (userpermission.userid = usersite.userid 
            AND siteid = nSiteID) 
        LEFT JOIN site ON (site.siteid = nSiteID)
        WHERE userpermission.`value` != 'None' AND userpermission.userid = nUserID AND userpermission.permissionid = nPermissionID
            AND (usersite.siteid IS NOT NULL OR (userpermission.`value` = 'Global' AND site.private = 'False'));
    # As well as if its been granted through any groups
    SELECT COUNT(*) INTO nGroupAccess FROM usergroup LEFT JOIN usersite ON (usergroup.userid = usersite.userid AND siteid = nSiteID) 
        JOIN grouppermission ON (usergroup.groupid = grouppermission.groupid) 
        LEFT JOIN site ON (site.siteid = nSiteID)
        WHERE grouppermission.`value` != 'None' AND usergroup.userid = nUserID AND grouppermission.permissionid = nPermissionID 
            AND (usersite.siteid IS NOT NULL OR (grouppermission.`value` = 'Global' AND site.private = 'False'));

    RETURN IFNULL(nUserAccess, 0) + IFNULL(nGroupAccess, 0) > 0;
END;

Example usage:

# Does user 1 have permission to edit sales at site 2
SELECT f_check_permission(1, 'SALES_ORDERS_CAN_EDIT', 2);
# Does user 1 have global permission to void sales orders
SELECT f_check_permission(1, 'SALES_ORDERS_CAN_VOID', NULL);

What Should Permissions Be?

I think this is probably the thing that will be most dependent on your particular environment, but I think this is often where devs can create issues for themselves. When designing the system and access paradigms, a dev can have trouble knowing exactly what a permission should encompass. Should it be a particular field, an endpoint, a table, a screen? If a user doesn't have access do you show that area but grey it out (for discover-ability), hide it completely, or pop up access denied boxes when they try to go to it?

For all those questions, the unfortunate answer is that it's going to depend. I'm guessing over the years I've probably done all of them, depending on user and system needs. In general though, here are my rules of thumb:

• Create clear permissions with descriptions that outline what granting/denying the permission will do.
• Don't create permissions that control small areas (specific fields) unless they are particularly sensitive.
• Start general, and only create more specific permissions if necessary. If a customer demand or usage patterns determine that users need to be able to do some of what a permission controls and not the rest, break it up or create 'sub-permissions'. Generally, its easier to add a new permission to the application that can be checked in addition to the previous (more general one) than it is for end users to manage huge swathes of field-specific permissions.
• Think about the type of permission (see later session) and the effect it will have on UI as well as APIs. Where will it be checked? Just in the UI to affect user experience, in the UI as well as the backend, or just the backend? Depending on the permission, the answer can vary, and a good user experience generally requires checking in the frontend, and a well secured system requires checking in the backend.

Overlapping Permissions

Inevitably, you will end up with general permissions "SALES_ORDERS_CAN_CREATE" that control an entire area (creation of sales for example) and more-specific permissions "SALES_ORDERS_CAN_ACCEPT_PAYMENTS" that control sub-functions and sub-roles in that area. Here are some best practices to make it easier for devs to know what permissions to check and for admins to know what to grant:

• Make sure such permissions are easy to tell they're related. In our paradigm, that is through shared categories and good descriptions. I've seen systems where such a permission is a child of the existing one, and granting/denying at one level cascades up or down the tree, but I have found this to be more complicated than its worth in practice.
• In code, check the INTERSECTION (not UNION) of all permissions that affect a particular UI area or endpoint (AND instead of OR). Essentially in code, it can then be easy to show/hide an entire area (or lock down an entire page readonly) based on a general permission, and then make sub-areas/buttons/functions check a more specific permission in addition. If a function/area checks UNION of more general and more specific permissions, it will be unclear when permissions do or do not control things (since a child permission is overriding a more general permission).
• This could lead to a situation where the user has permission to accept payments on a sale even though they don't have privileges to edit or view sales. That user would be unable to perform that task because a more general permission is absent. This may seem strange, but when users' groups represent disparate/overlapping roles, this can sometimes happen (depending on the groups a user is in), and it is generally true that admins will have an easier time understanding why a user can't do something (missing top-level permission) than debugging why they're able to do something despite a well-defined top level permission already being absent.

Types of permissions:

• Sensitive data permissions will hide areas of the software entirely or affect the fields that can be displayed about an object in a view. This is generally a permission that is checked by front-end code.
• Task based permissions generally control whether a user can 'create' or 'edit' entities and relationships in your system that are everyday operations. I find for most major areas, there is an overarching item they are managing there, and creating two permissions (one for creation of new items and one for editing of existing items) is a good place to start. These end up being easy for user managers to understand because they coincide pretty well with the areas of the software that they're familiar with.
• Role permissions generally are bound to specific user groups and represent less common 'administrative tasks' that are often more specific to a particular button or menu option. Things like voiding/deleting/approving something that the average user cannot do. If a user doesn't have one of these permissions, that button or menu option is often inaccessible or hidden. These still generally coincide pretty well with an area of the software and operation that can be easily grokked.
• Cross-cutting permissions. I'm including these here for completeness, but I think they're a thing to avoid. Essentially, it is often tempting to create a permission that seems role-based that in its definition affects many/all areas of the software. An example could be 'access advanced features' or 'view sales activity'. In a software system, advanced features or sales data might be something included on many screens and would require figuring out what it means on every screen. They aren't impossible, but they're often hard to describe to users and have them manage. Often a user will want a user to view sales history on a customer (sales activity) but not sales reports generated by the business intelligence unit, so you don't want to introduce a cross-cutting concern in your software.

Adding New Permissions:

A common concern is, during a product's life-cycle, new permissions will be added that need to be managed by users as they accept updates. Since (as mentioned) omission of access levels mean a user does not have access, new permissions will inherently be denied to existing users, which can lead to an interruption of previous duties before the update.

To solve this, I have a stored procedure that is part of update scripts that I call 'copy grant permissions'. Essentially, newly added permissions are generally either a more specific part of a previously existent permission or are a completely new area whose usage can be inferred based on use of an existing area. So essentially, when I create a new row in the permission table (don't forget to give it a good user-facing description) I generally call this stored procedure and pass it the codename of the new permission as well as a previous permission I'd like to copy from. Essentially that procedure grants the new permission to any users or groups that had the previous permission at the same access levels of the previous permission. At that point, a user administrator can modify these permissions further.

Conclusion:

Hopefully this gives you a reasonable starting point for creating a flexible permission system in your application. I know this doesn't conceptualize permissions in certain ways (there are no sub-permissions) but this has been in use in several production ERP systems two of which have been in use for 10+ years and the other that is at 5+ and I have never found a situation that I was unable to handle.

Most of the remaining challenge comes in crafting permissions to not be too specific (per-field) and not too general. You will probably find when talking to your users or admins what level they wish to control users' access. At that point, its just about naming and describing the permissions well for them to be useful and easily managed. That is very application-dependent so I don't know that my learning on the matter would be as applicable as this framework.