RBAC (Role Based Access Control) ER Diagram

Question

I am trying to design an application with Role Based Access Control. And I have also designed the ER Diagram. So here I try to achieve that a USER will have a single ROLE and based on the particular ROLE it will get permissions for RESOURCES set in the resource_role table.

Please suggest if I am making any mistake.

As while searching on the internet for the RBAC ER diagram I have come across some designs like .

Source:- GitHub Link and Youtube

In this ER diagram, I have some questions which are as follows:-

1. How a USER can hold multiple ROLE as there is no relation between the three i.e USER, ROLE, and RESOURCE. How to identify which ROLE to use for the particular user?

2. And how the user_role bridge table linked to role in one and only one relation? As USER can have many ROLE attached to it.

Thanks to all in advance.

Answer 1

You are assuming that it must be necessary to "identify which role to use" for the user, but (1) it isn't absolutely necessary and (2) that (which role am I going to play) can be a piece of information that the user is required to enter during logon and then stored in his session context.

It isn't absolutely necessary because the semantics behind the resource_role table might be like "access to resource RES requires that the user has role ROLE". And then granting the access is just a matter of searching whether the user indeed has the required role, and there is no concept of "which role is that user now playing".