在数据库中存储应用程序权限

问题描述：

我正在为我们的公司开发一个应用程序，最终将有很多方法可以将用户限制为特定的部分/模块.虽然应用程序仍然很小，但我想转到一种存储权限的新方法，随着应用程序的增长，该方法将易于维护和查询.

I'm developing an application for our company that eventually will have lots of ways of restricting users to particular sections/modules. While the application is still small, I'd like to move to a new method of storing permissions that, as the application grows, will remain easy to maintain and query.

当前，在我们的MySQL数据库中，有一个名为"user"的表，该表存储用户的ID，用户名和密码.在一个单独的名为"user_acl"的表中，如下所示:

Currently in our MySQL database we have a table called "user" which stores the user's ID, username and password. In a separate table called "user_acl" is the following:

user_acl_id
acl_root
acl_news_read
acl_news_write
acl_news_modify
acl_reports_read
acl_reports_write
acl_reports_modify
acl_users_read
acl_users_write
acl_users_modify

我们目前只有3个模块，但是随着时间的流逝，还会创建更多模块，并且需要为每个模块添加权限.

We only have 3 modules at the minute, but over time more will be created and permissions for each will need to be added.

除了为每个权限创建一列之外，还有其他方法或存储此信息吗?

Rather than create a column for each permission, is there any other way or storing this information?

答

我会这样做.

table name: permission
columns: id, permission_name

然后我可以使用多对多关系表为用户分配多个权限

and then I can assign multiple permissions to the user using a many to many relationship table

table name: user_permission
columns: permission_id, user_id

此设计将允许我添加所需数量的权限，并将其分配给所需数量的用户.

This design will allow me to add as many permission as I want, and assign it to as many user as i want.

尽管上述设计符合您的要求，但我有自己的方法在应用程序中实现ACL.我在这里发布.

While the above design go with your requirement, I have my own method of implementing ACL in my application. I am posting it here.

我的ACL实现方法如下:

My method of implementation of ACL goes like this:

将为用户分配一个角色(管理员，来宾，工作人员，公共)
角色将被分配一个或多个权限(user_write，user_modify，report_read)等.
该用户的权限将从他/她所担任的角色继承
除了从角色继承的权限外，还可以为用户分配手动权限.

为此，我提出了以下数据库设计.

To do this I have come up with the following database design.

role
I store the role name here 
+----------+
| Field    |
+----------+
| id       |
| roleName |
+----------+

permission:
I store the permission name and key here 
Permission name is for displaying to user.
Permission key is for determining the permission.
+----------------+
| Field          |
+----------------+
| id             |
| permissionName |
| permissionKey  |
+----------------+

role_permission
I assign permission to role here 
+---------------+
| Field         |
+---------------+
| id            |
| role_id       |
| permission_id |
+---------------+

user_role
I assign role to the user here 
+---------------+
| Field         |
+---------------+
| id            |
| user_id       |
| role_id       |
+---------------+

user_permission
I store the manual permission I may allow for the user here 
+---------------+
| Field         |
+---------------+
| id            |
| user_id       |
| permission_id |
+---------------+

这使我可以更好地控制ACL.我可以允许超级管理员自己分配权限，依此类推.正如我所说的，这只是为了给您这个想法.

This gives me more control over the ACL. I can allow superadmins to assign permission by themselves, and so on. As I said this is just to give you the idea.

在数据库中存储应用程序权限

相关推荐