Roles permissions and user roles, proper mapping?

Question

I have a basic role based access control. Single table for permission (name, description), single table for role (name, description). Question is, how to make proper mapping with the users? So in my mind comes 1 solution:

role_permission_set - table

(role_permission_id PK, role_id (many-to-one), permission_id (many-to-one))

user_role - table

(user_role_id PK, user_id (many-to-one), role_permission_id (many-to-one))

Two tables for mapping roles permissions and users roles. Is there a better way of doing this?

Mike Organek Mike Organek · Accepted Answer · 2020-06-27T14:26:47

You have this right.

Break it down into two independent many-to-many relationships. One is between user and role. The other is between role and permission.

Each of those two many-to-many relationships will need a join table similar to what you described.

The only mistake I see in your post is that user_role should contain the role_id instead of the role_permission_id.

create table user_role (
  user_role_id int serial primary key,
  user_id int not null references user(user_id),
  role_id int not null references role(role_id),
  constraint uq_user_role unique(user_id, role_id)
);

When you want to get a list of permission values for a user:

select distinct p.name
  from user_role ur
  join role_permission rp on rp.role_id = ur.role_id
  join permission p on p.permission_id = rp.permission_id
 where ur.user_id = 123;

Roles permissions and user roles, proper mapping?

1 Answers