Efficient way to handle user roles

Question

I am working on one portal where will be few user roles. I have been wondering what is the best way to handle them. I have created separated tables for users and clients, but clients will want the functionality as users and users can become clients easy too.

I also don't want to make many joints, so what I as thinking is this:

I will have 4 different user roles (at least for now) as follow:

user
client
reviewer
admin

I will assing "id" to each role. At the same time I will keep table in mysql with these roles. It will be something like:

1 - admin
2 - reviewer
3 - client
4 - user

This table will be used only upon creation of user, to get the code of user "permissions". So Let's say that there will be a guy who is a user and reviewer. His role would be 24.

login  password  email role     created
----------------------------------------------------------
guy    password  guy@gmail.com  24 2012-12-08 23:12:30

I think this could work pretty well, but still want to ask if you guys think this is good and effective solution. Thanks

Scott Presnell · Answer 1 · 2012-08-26T03:21:54.157

1

The other way to do this would be to have a many to many USER_ROLE table where for your example guy would have the following entires.

login role
guy   2
guy   4

I generally prefer this method of tracking roles. A join against this table in a situation like this should be fast and painless, especially if you move to using a user_id instead of a login, and index appropriately.

edited Aug 26 '12 at 03:21

answered Aug 26 '12 at 03:15

Scott Presnell

1,528
10
23

Of course there would be user_id, i just wrote it fast for example. I was thinking about this too, but still dont really like that join there. – Tomas Aug 26 '12 at 03:29

score 0 · Answer 2 · edited May 23 '17 at 12:00

What you're defining is a Role Based Access Control System (I would suggest looking up resources on this). An RBAC system will have a separate table for users and another table for roles. There will be a many to many relationship between users and roles. Also, you will connect a permissions table to roles in another many to many relationship. The image attached represents how to implement this system:RBAC SYSTEM IN MYSQL

A similar question was asked before: How to design a hierarchical role based access control system

Efficient way to handle user roles

2 Answers2