How to simplify this Role and Permission management design?

Question

I'm working on a web application that has users with multiple roles and each user can perform multiple operations, based on user's role, and the permission level the role has on the operation. I came up with the following schema.

Users

+--------+-------------------+
| UserID | UserName          |
+--------+-------------------+
| 1      | Alice             |
+--------+-------------------+
| 2      | Bob               |
+--------+-------------------+
| 3      | Charlie           |
+--------+-------------------+
| 4      | David             |
+--------+-------------------+

Roles

+--------+-----------------+
| RoleID | RoleName        |
+--------+-----------------+
| 1      | Tech_Admin      |
+--------+-----------------+
| 2      | Tech_Normal     |
+--------+-----------------+
| 3      | Non_Tech_Admin  |
+--------+-----------------+
| 4      | Non_Tech_Normal |
+--------+-----------------+

PermissionLevels

+-------------------+----------------------+
| PermissionLevelID | PermissionLevel      |
+-------------------+----------------------+
| 1                 | Tech_Account         |
+-------------------+----------------------+
| 2                 | Non_Tech_Own_Account |
+-------------------+----------------------+
| 3                 | Non_Tech_Any_Account |
+-------------------+----------------------+
| 4                 | Own_User             |
+-------------------+----------------------+

UserRoles

+--------+--------+
| UserID | RoleID |
+--------+--------+
| 1      | 1      |
+--------+--------+
| 2      | 2      |
+--------+--------+
| 3      | 3      |
+--------+--------+
| 4      | 4      |
+--------+--------+

Commands

+-----------+--------------+
| CommandID | CommandName  |
+-----------+--------------+
| 1         | CREATE_USER  |
+-----------+--------------+
| 2         | EDIT_USER    |
+-----------+--------------+
| 3         | VIEW_USER    |
+-----------+--------------+
| 4         | EDIT_PROFILE |
+-----------+--------------+
| 5         | VIEW_PROFILE |
+-----------+--------------+
| 6         | SUSPEND_USER |
+-----------+--------------+

RoleCommands

+--------+-----------+-------------------+
| RoleID | CommandID | PermissionLevelID |
+--------+-----------+-------------------+
| 1      | 1         | 1                 |
+--------+-----------+-------------------+
| 1      | 1         | 3                 |
+--------+-----------+-------------------+
| 2      | 2         | 1                 |
+--------+-----------+-------------------+
| 3      | 2         | 2                 |
+--------+-----------+-------------------+
| 4      | 5         | 4                 |
+--------+-----------+-------------------+

For simplicity, I have not described account details but each user belongs to an Account - 'Tech' or 'Non-Tech'. There is only 1 Tech Account in the System.

Here are sample business rules as per RoleCommands table.

• Tech Admin can Create Users in Tech Account.
• Tech Admin can Create Users in any Non Tech Account.
• Tech Normal can Edit Users in Tech Account.
• Non Tech Admin can Edit Users in their own Non Tech Account.
• Non Tech Normal can view their own profile - which from the table means other users cannot view this user's profile.

When I receive a new REST API request, I will identify the operation based on the request paramters and verify if the user has permission to perform the operations based on RoleCommands table. Does this look like a reasonable design for Role and Permission management ?

Update

It looks like there will be too many records in the RoleCommands table because, for each command, there will be several combinations with roles and permission levels. For a given object, there can be n (for eg: 10) statuses in which the object can be. I want to give View_Object_Status1 command permission to a user so that the user can view the object when its status is Status1. That is blowing up the RoleCommands table. What is the best way to simplify this ?

Answer 1

Your current design is this:

You should ask yourself:

• does entity PERMISSION_LEVEL represent actual levels?

Is they are actual levels it means that when a user is granted two different roles and both roles have the same command but with different levels, the app should take the highest level of them. In role-permission models with no permission level, set logic is used, so if an user ends up with the same permission twice, it doesn't matter. What matters is that the permission exists in the set, not how many times it exists. But your model has permission level and so you should decide what to do when a user ends up having the same role command more than once with different permission levels.

In the other hand in the update of your question you mention a new entity that is not shown in the model which is OBJECT.

• Where does object fall into the model?

For what I can see the model would be updated like this:

That would actually considerably increase the row number of the resulting table. But that should not worry you too much if you create the proper indexes and FK. What does worry me is the complexity assembling the roles but at least that should be done only once.

I'm not sure if I ended up raising more questions that the answers I gave.

Answer 2

Does this look like a reasonable design for Role and Permission management ?

The term "reasonable" is somewhat ambiguous here.

Fit for purpose

This design achieves it's purpose. It enables to determine what operations any logged-in user can do on any specific business object :

• Users are identified
• Roles are identified
• Users are assigned to roles
• Commands are identified - apparently a command refer to an operation on a business object (user accounts in this example)
• Permission levels are identified - apparently the permission level restricts the business objects that are managed in commands
• Commands with permission levels are assigned to roles

Strength

This design allows easy day to day role based access management, as expected in modern business applications.

This design is flexible. It allows to tailor the roles with a very fine granularity so that it enables to implement any possible access configuration that could be desired (assuming that the permission level appropriately represents the segmentation needs in that matter).

Weakness

The construction of roles requires the explicit entering of all the combinations. This could be tedious, if commands or permission levels are too fine grained.

The permission level is common across all business objects:

• If you only manage a couple of business objects, that share similar access rules, it's ok.
• If you'd need diffenet access rules on different business objects, you'd need to add more permission levels, with a risk of inconsistency between the command and the permission level.

By the way, the current list of permission levels doesn't cover unambiguously the whole set of data: I'd suggest to rename "Own" into "Tech_Own" and "Tech" into "Tech_any". I understand that "any" means "any except his own".

Edit:

Following your comment about managing several business objects, I'd suggest that you add a business-object table, and change the command table and the permission-levels, so that they both relate to a business object.

In this way, you will be sure that your profiles will always relate compatible commands and permission levels: you just have to introduce the business-object-id in this table.

This will not make the roles simpler, but this would not only solve your problem but also allow to offer to the role manager a nice user interface to facilitate the entry (always chosing among compatible elements)

Answer 3

Here is how I would approach the situation:

1. You keep your Users table
2. What you consider as Roles are, basically, groups of Users. So, we create a new table Groups
3. What you call PermissionLevel is the equivalent to Roles that members of Groups can have (eg. a user who is Tech_Admin can have the role of Tech_Account and Non_Tech_account
4. I would replace the Commands table with a Table called Modules, where it keeps the different areas that permissions need to be applied (eg. User, Profile, Reports, etc.)
5. In order to keep the Permissions, I would use the CRUD (create, read, update, delete) approach. Although it may be restrictive, it is often used for permissions design. You can store the CRUD value either as a byte where you check the bits or in separate columns

Here we come to an important question about your application and design: what defines the permissions for commands? Is it the Roles, the groups or the user can have individual permissions which override the group permissions?

We can go with the following tables:

GroupPermissions

GPID - ModuleID - CRUD (or separate columns for C-R-U-D)

RolePermissions

RoleID - ModuleID - CRUD

Next question: In your App, can Roles exist independent to the Groups? If yes, the above tables will suffice. Otherwise, you need a table to link the two

GroupsRoles

GRID - GroupID - RoleID

Next question: can a User be only part of a Group or can have a Role as well independently to the group s/he belongs?

If the former, you need a table like this?

GroupsUsers

GUID - UserID - GroupID

If the latter, one more table is required

RolesUsers

RUID - UserID - RoleID

Lastly, if a user can have individual level of permissions regardless the participation to groups or roles, then this table is required

ModulesUsers

MUID - UserID - ModuleID - CRUD

Depending on the policy about the propagation of the permissions, you then can start checking the last three tables and decide on what a user is allowed to do.

Answer 4

PermissionLevels should define command permissions, it would make you design much more simplified, so each Permission level would come with predefined crud permissions.

Another option would be to define permissions and roles as second and first level of a URN of a rest service.

Example: a rest service that have URNs like sales, operation, report, config. Each URN would be a role. Each role could have permissions like CRUD: read, create, update, delete, audit etc. For one user, you could give one or more role permissions, each one telling which crud permissions he/she have.

 User1
 Roles           Permissions
 Sales            CRUD
 Report           R
 Operation        CR

Note: you can also implement role hierarchy, so higher holes have sub-roles authorized by default, which means less database records to apply permissions to a user.

More details in https://en.m.wikipedia.org/wiki/Role-based_access_control