最佳用户角色权限数据库设计实践? [英] Best user role permissions database design practice?
问题描述
我想为Web应用程序设计数据库，在该数据库中，用户可以根据授予角色的权限来访问特定选项卡.
I want to design database for a web app in which user can access particular tabs based on the permissions given to a role.
到目前为止，我已经创建了两个表 USER_TABLE 和 USER_ROLES .
What I have done so far is I created two tables USER_TABLE and USER_ROLES.
USER_TABLE 具有以下字段:
- id(主键)
- 用户名
- 密码
- 名字
- 姓氏
- created_date
- role_id_fk(外键)
USER_ROLES 具有以下字段:
-
id(主键)
id (primary key)
角色名称(例如ADMIN，TAB1_USER，TAB2_USER)
role_name (e.g. ADMIN, TAB1_USER, TAB2_USER)
创建日期
在这里，具有角色名称" ADMIN "的用户可以看到所有选项卡，其他用户只能访问特定的选项卡.
Here, the user having role_name "ADMIN" can see all the tabs, other users can access specific tabs only.
我的问题是我是否需要在具有以下字段的 USER_ROLES 表中创建具有外键的表 USER_PERMISSIONS :
My question is do I need to create a table USER_PERMISSIONS having foreign key in USER_ROLES table with below fields:
- id(主键)
- permission_type(ALL，TAB1，TAB2....)
还是应该在我的代码级别进行管理?两种方法的利弊是什么?
or should I manage this at my code level? What would be the cons and pros of both approaches?
推荐答案
正如krokodilko在其评论中所写，这取决于您所需的灵活性级别.
我已经为我的一个客户实现了基于角色的权限，如下所示:
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:
- 用户(用户ID(PK)，用户名(唯一)，密码(已盐化并哈希化！！ )，名字，姓氏，电话等')
- 角色(角色ID(PK)，角色名称(唯一)，角色描述)
- 权限(权限ID(PK)，权限名称(唯一))-选项卡/屏幕/操作在此处
- 用户到角色(用户ID，角色ID)-PK是两列结合的
- 权限角色(角色ID，权限ID)-PK是两列的组合
- 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.
这篇关于最佳用户角色权限数据库设计实践?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
