用户角色权限mysql设计,最佳用户角色权限数据库设计实践？

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?

解决方案

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:

User (user id (PK), user name (unique), password (salted and hashed!), first name, last name, phone etc')

Role (role id (PK), role name (unique), role description)

Permission (permission id (PK), permission name (unique)) - the tabs / screens / actions goes here

User To Role (user id, role id) - PK is both columns combined

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.