Design – How to simplify this Role and Permission management design

database-designdesignpermissionsrelational-databaseroles

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 ?

Best Answer

Your current design is this:

enter image description here

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:

enter image description here

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.

Related Topic