Best user role permissions database design practic

问题:

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?

回答1:

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.