Managing Roles and Statuses in a System

There are many ways to solve a problem, and that’s the case with administering roles and user statuses in software systems. In this article you’ll find a simple evolution of that idea as well some useful tips and code samples.

Basic Idea

In most systems, there is usually a need to have roles and user statuses.

Roles are related to rights that users have while using a system after successfully logging in. Examples of roles are “call center employee”, “call center manager”, “back office employee”, “back office manager”, or “manager”. Generally that means that a user will have access to some functionality if he or she has the appropriate role. It’s wise to assume that a user can have multiple roles at the same time.

Statuses are much stricter and they determine if the user has rights to log into the system or not. A user can have only one status at a time. Examples of statuses would be: “working”, “on vacation”, “on sick leave”, “contract ended”.

When we change a user’s status we can still keep all roles related to that user unchanged. That is very helpful because most of the time we want to change only the user’s status. If a user who works as a call center employee goes on vacation, we can simply change his status to “on vacation” and return it to the status “working” when he gets back.

Testing roles and statuses during login enables us to decide what will happen. For example, maybe we want to forbid login even if the username and password are correct. We could do so if the current user status does not imply that he is working or if the user doesn’t have any role in the system.

In all models given below, the tables status and role are the same.

Table status has the fields id and status_name and the attribute is_active. If the attribute is_active is set to “True”, that means that the user who has that status is currently working. For example, the status “working” would have the attribute is_active with a value of True, while others (“on vacation”, “on sick leave”, “contract ended”) would have a value of False.

The role table has only two fields: id and role_name.

The user_account table is the same as the user_account table presented in this article. Only in the first model does the user_account table contain two extra attributes (role_id and status_id).

A few models will be presented. All of them work and can be used but have their advantages and disadvantages.

Simple Model

The first idea could be that we simply add foreign key relationships to the user_account table, referencing on tables status and role. Both role_id and status_id are mandatory.

This is pretty simple to design and also to handle data with queries but has a few disadvantages:

1. We don’t keep any history (or future) data.

   When we change the status or role we simply update status_id and role_id in the user_account table. That will work fine for now, so when we make a change it will reflect in the system. This is ok if we don’t need to know how statuses and roles have changed historically. Also there is a problem in that we can’t add future role or status without adding extra tables to this model. One situation where we probably would like to have that option is when we know that somebody will be on vacation starting next Monday. Another example is when we have a new employee; maybe we want to enter his status and role now and for it to become valid at some point in future.

   There is also a complication in case we have scheduled events that use roles and statuses. Events that prepare data for the next working day usually run while most users don’t use the system (e.g. during night time). So if somebody won’t work tomorrow we’ll have to wait till the end of the current day and then change his roles and status as appropriate. For instance, if we have employees that currently work and have the role “call center employee”, they will get a list of clients they have to call. If somebody by mistake had that status and role he will also get his clients and we’ll have to spend time correcting it.

2. User can have only one role at a time.

   Generally users should be able to have more than one role in the system. Maybe at the time you’re designing the database there is no need for something like that. Keep in mind that changes in workflow/process could happen. For example, at some time the client could decide to merge two roles into one. One possible solution is to create a new role and assign all functionalities from the previous roles to it. The other solution (if users can have more than one role) would be that the client simply assigns both roles to users that need them. Of course that second solution is more practical and gives the client the ability to adjust the system to his needs faster (which is not supported by this model).

On the other hand, this model also has one big advantage over others. It’s simple and so queries to change statuses and roles would also be simple. Also, a query that checks if the user has rights to login into system is much simpler than in other cases:

select user_account.id, user_account.role_id
from user_account
left join status on user_account.status_id = status.id
where status.is_user_working = True
and user_account.user_name = @user_name
and user_account.password_hash_algorithm = @password;

@user_name and @password are variables from an input form while the query returns the user’s id and the role_id that he has. In cases when user_name or password are not valid, pair user_name and password does not exist, or the user has an assigned status that is not active, the query won’t return any results. That way we can forbid login.

This model could be used in cases when:

• we are sure there would be no changes in process that require users to have more than one role
• we don’t need to track roles/status changes in history
• we don’t expect to have much role/status administration.

Time Component Added

If we need to track a user’s role and status history we must add many to many relationships between the user_account and role and the user_account and status. Of course we’ll remove role_id and status_id from the user_account table. New tables in the model are user_has_role and user_has_status and all fields in them, except end times, are mandatory.

The table user_has_role contains data about all roles that users ever had in the system. The alternate key is (user_account_id, role_id, role_start_time) because there is no point to assigning the same role at the same time to a user more than once.

The table user_has_status contains data about all statuses that users ever had in the system. The alternate key here is (user_account_id, status_start_time) because a user can’t have two statuses that begin at the exact same time.

Start time cannot be null because when we insert a new role/status, we know the moment from which it will start. End time can be null in case we do not know when the role/status would end (e.g. role is valid from tomorrow until something happens in future).

Besides having a complete history, we can now add statuses and roles in the future. But this creates complications because we have to check for overlapping when we do an insert or update.

For instance, user can have only one status at a time. Before we insert a new status we have to compare the start time and end time of a new status with all existing statuses for that user in the database. We can use a query like this:

select *
from user_has_status
where user_has_status.user_account_id = @user_account_id
and 
(
# test if @start_time included in interval of some previous status
(user_has_status.status_start_time <= @start_time and ifnull(user_has_status.status_end_time, "2200-01-01") >= @start_time)
or
# test if @end_time included in interval of some previous status  
(user_has_status.status_start_time <= @end_time and ifnull(user_has_status.status_end_time, "2200-01-01") >= ifnull(@end_time, "2199-12-31"))  
or  
# if @end_time is null we cannot have any statuses after @start_time
(@end_time is null and user_has_status.status_start_time >= @start_time)  
or
# new status "includes" old satus (@start_time <= user_has_status.status_start_time <= @end_time)
(user_has_status.status_start_time >= @start_time and user_has_status.status_start_time <= ifnull(@end_time, "2199-12-31"))  
)

@start_time and @end_time are variables containing the start time and end time of a status we want to insert and @user_account_id is the user id for which we insert it. @end_time can be null and we must handle it in the query. For this purpose, null values are tested with the ifnull() function. If the value is null, a high date value is assigned (high enough that when someone notices an error in the query we’ll be long gone :). The query checks all combinations of start time and end time for a new status compared to the start time and end time of existing statuses. If the query returns any records, then we have overlapping with existing statuses and we should forbid inserting the new status. Also it would be nice to raise a custom error.

If we want to check the list of current roles and statuses (user rights) we simply test using start time and end time.

select user_account.id, user_has_role.id
from user_account
left join user_has_role on user_has_role.user_account_id = user_account.id
left join user_has_status on user_account.id = user_has_status.user_account_id
left join status on user_has_status.status_id = status.id
where user_account.user_name = @user_name
and user_account.password_hash_algorithm = @password
and user_has_role.role_start_time <= @time and ifnull(user_has_role.role_end_time,"2200-01-01") >= @time
and user_has_status.status_start_time <= @time and ifnull(user_has_status.status_end_time,"2200-01-01") >= @time
and status.is_user_working = True

@user_name and @password are variables from the input form while @time could be set to Now(). When a user tries to login we want to check his rights at that time. Result is a list of all roles that a user has in the system in case user_name and password match and the user currently has an active status. If the user has an active status but no roles assigned, thequery won’t return anything.

This query is simpler than the one in section 3 and this model enables us to have a history of statuses and roles. In addition, we can manage statuses and roles for the future and everything will work fine.

Final Model

This is just an idea of how the previous model could be changed if we wanted to improve performance. Since a user can have only one active status at a time we could add status_id into the user_account table (current_status_id). That way, we can test the value of that attribute and won’t have to join the user_has_status table. The modified query would look like this:

select user_account.id, user_has_role.id
from user_account
left join user_has_role on user_has_role.user_account_id = user_account.id
left join status on user_account.current_status_id = status.id
where user_account.user_name = @user_name
and user_account.password_hash_algorithm = @password
and user_has_role.role_start_time <= @time and ifnull(user_has_role.role_end_time,"2200-01-01") >= @time
and status.is_user_working = True

Obviously this simplifies the query and leads to better performance but there is a bigger issue that would need to be solved. The current_status_id in the user_account table should be checked and changed if necessary in the following situations:

• on each insert/update/delete in user_has_status table
• every day in a scheduled event we should check if someone’s status changed (currently active status expired or/and some future status became active) and update it accordingly

It would be wise to save values that queries will use frequently. That way we’ll avoid making the same checks over and over again and split job. Here we’ll avoid joining the user_has_status table and we’ll make changes on current_status_id only when they happen (insert/update/delete) or when the system is not in use so much (scheduled events usually run when most of the users don’t use system). Maybe in this case we wouldn’t gain much from current_status_id but look at this as an idea that can help in similar situations.