Best user role permissions database design practice?

Question

I want to design database for a web app in which user can access particular tabs based on the permissions given to a role.

What I have done so far is I created two tables USER_TABLE and USER_ROLES.

USER_TABLE has below fields:

• id (primary key)
• user_name
• password
• first_name
• last_name
• created_date
• role_id_fk (foreign key)

USER_ROLES has below fields:

• id (primary key)

• role_name (e.g. ADMIN, TAB1_USER, TAB2_USER)

• created_date

Here, the user having role_name "ADMIN" can see all the tabs, other users can access specific tabs only.

My question is do I need to create a table USER_PERMISSIONS having foreign key in USER_ROLES table with below fields:

• id (primary key)
• permission_type (ALL, TAB1, TAB2....)

or should I manage this at my code level? What would be the cons and pros of both approaches?

Solution

As krokodilko wrote in his comment, it depends on the level of flexibility you need.
I have implemented role based permissions for one of my clients as follows:

1. User (user id (PK), user name (unique), password (salted and hashed!), first name, last name, phone etc')
2. Role (role id (PK), role name (unique), role description)
3. Permission (permission id (PK), permission name (unique)) - the tabs / screens / actions goes here
4. User To Role (user id, role id) - PK is both columns combined
5. Role to Permission (role id, permission id) - PK is both columns combined

But my requirement was to be as flexible as possible, and it is a system that is still growing (6 years and counting).

I guess a lot of applications can have the user to role as a one to many relationship, instead of a many to many like in my case, but I wouldn't go hard coding permissions or role to permissions in any application.

Further explanation: Role based security database design on What the # do I know?

Comments

[tomrlh]

Can you tell more about the 4 and 5?

[Zohar Peled]

They are just many to many bridge tables, not much to tell about them....

[Stephen Ngethe]

What I get is that ; Role will be like (Admin, Accounts, e.t.c permisions will be the views the roles want to access What I don't get is the remaining part How will I use the table user_to_role and role_to_permission

[Zohar Peled]

User to role enables a many to many relationship between users and roles. Same thing about role to permission.

[user2924019]

Thanks, this has helped me with designing a system that requires multiple levels of access. @njoshsn A user can have multiple roles, and a role can have multiple users, in this case, to link them, you have an additional table known as a "many to many", it's simply a table that has two columns, user_id and role_id, so that you can assign them to each other.

[W1M0R]

Here is an example using the NIST model for role based access control: github.com/morenoh149/postgresDBSamples/blob/master/…

[vikrant]

what could be the values in "tabs/screen/actions" column? also, what could be the flow of logic in backend, if say, a user is trying to access a route. (i mean the sequence of sql queries to find out the permissions / permit or not permit the user). could it be: 1. user logins, we get his id, from id we get roles, then for each role we check permissions. if the route is "/courses/delete/", and there is a permission "delete_courses", then we allow the user to delete the course. am i right?

[Zohar Peled]

@vikrant Actually, Permission is probably not the best name for this table. A more suitable name would be securables - meaning things that can only be accessed by authorization - that table should contain an application-wide (or domain-wide) unique key for each securable - be it a path, a windows form, even a button. While flexible, this is still a simple authorization example - meaning the authorization itself is binary - it either exists or not. A different implementation may contain different authorization levels - something along the lines of "read / write / execute" etc'.

[Zohar Peled]

@vikrant basically, you have two options: Either you get the entire list of authorized actions a user have when they login, or you get specific securable/user combination each time the user attempts to access a securable (of course, these could be cached into memory). It's a business logic decision - the most secure option would be to check the authorization of each securable on demand each time - this way, you can revoke authorization immediately even if the user is already logged in to the system.

[SdSaati]

@ZoharPeled thank you so much for sharing this design with us :), I use these values for the 3) : ex: post.read post.create post.comment.write post.all post.none, all is for super admin role and none if for ban users, and each action in application has a permission entry in 3)

[Nick Bull]

You may also want a role to user bridge table, so that you can view all users that have a particular role

[Zohar Peled]

@NickBull I already have a user to role table, it's easy to query it for all the users that hold a specific role...

[Jashwant]

@ZoharPeled, is there a reason to use both columns as primary key rather than an auto increment primary key in 4th and 5th?

[Zohar Peled]

@Jashwant You can, if you think you need one, but the combination of both columns in these kind of bridge tables is usually the perfect natural key - It makes no sense for either of them to be null, and the combination of both must be unique otherwise you'll get duplicate data in your table. Also, whenever you're using a surrogate key you must also use constraints on your natural key to keep data integrity - and usually these tables don't really have anything you want to join to except the tables they bridge between - so really, what's the point of adding an auto-increment column here?

[Jashwant]

No point actually. But when I think that I won't be needing an auto increment id in future, it usually turns out differently. So, I kind of add auto increment in each table. I only wanted to know whether composite key had other benefits or not. This article helped too.

[Zohar Peled]

@Jashwant The Natural vs surrogate key debate is probably as old as the invention of the first RDBMS, and yet the jury is still out on this one. Personally, I like to use surrogate keys when the composite key is complicated and I need to reference the table from other tables as well, or from the applications using it. This way, I can identify a row using a single value (preferably int) which makes my life easier than using several columns. That being said, it doesn't exempt me from adding constraints to the table that will ensure he data integrity.

[Zohar Peled]

@Jashwant It took me a while but I finally remembered where I wrote about it: zoharpeled.wordpress.com/2019/10/06/…

[Jashwant]

@ZoharPeled, thanks for the article. The gist I am getting is that keeping surrogate key is usually good, but it is not needed for join tables as it is highly unlikely that it will be used as a foreign key, or join tables will have some more columns other than primary keys of tables being joined.

[Zohar Peled]

Yes, that sums it up nicely.