正确的方式来管理用户权限（用户层次结构） [英] The right way to manage user privileges (user hierarchy)

问题描述

我想允许我的应用程式的使用者新增子使用者，并为每个子使用者设定允许查看或执行的权限。

I want to allow users of my application to add sub-users and set privileges for what each sub-user is allowed to view or do.

我的提议具有单独的PRIVILEGES表，如下所示：

My ideas is to have a separate PRIVILEGES table, like this:

+-----------------+--------+
|privilege        |  value |
+-----------------+--------+
|create sub users |    1   |
|edit own profile |    2   |
|add new site     |    3   |
|delete site      |    4   |
+-----------------+--------+

然后当主用户选择权限时，使用该值更新子用户权限列，例如：

Then when the main user selects privileges update the sub users privilege column with the value, for example:

+--------------+-----------+
|user_id       | privilege |
+--------------+-----------+
|user_1        |     4     | 
|user_2        |     2     |
|user_3        |     1     |
|user_4        |     2     |
+--------------+-----------+

但是这些值没有给出唯一的金额。例如：

But the values do not give unique amounts. For example:

privileges
1 -> create sub users
+
2 -> edit own profile
= privilege 3 (create sub users, edit own profile)

有价值3的另一个特权（添加新网站），所以这将不工作。

but also there is another privilege for value 3 (add new site) so this will not work.

所以我的问题是：如何使任何可能的权限组合独特

So my question is: How do I make any possible privilege combination unique?

推荐答案

如果要将此列保留为一列，请使用 base 2 占位符。

If you want to keep this as one column, use base 2 placeholders.

1 - represents priv 1
2 - represents priv 2
4 - represents priv 3
8 - represents priv 4
16 - represents priv 5
32 - represents priv 6

然后你可以取每个的模数来确定他们是否有priv

Then you can take a modulus of each to determine if they have that priv.

所以...

3 = priv 1 and priv 2
9 = priv 1 and priv 4

63 = all privs.

等。

编辑：如果您仍然希望使用单个列来存储priv，请添加另一列，用于存储哪些

If you still wish to use the single column to store priv, add another column that stores who gave the permission.

但我仍然建议单独存储每个priv。
在priv，user_id和grantor 上创建一个带有组合主键的表。
组合的主键将确保每个priv是唯一的，所以您不需要在插入之前检查。要创建组合主键：

But... I'd still suggest storing each priv separately. Create a table with a combined primary key on priv, user_id, and grantor. The combined primary key will ensure that each priv is unique so you don't need to check before inserting. To create a combined primary key:

ALTER TABLE priv ADD PRIMARY KEY (user_id,grantor,priv_id);

然后添加或重置私人， REPLACE INTO priv ，priv_id）VALUES（？，？，？）

Then to add or reset a priv, REPLACE INTO priv (user_id,grantor,priv_id) VALUES (?,?,?)

要删除用户的priv， DELETE FROM priv WHERE user_id =？ AND priv_id =？

To delete a priv for a user, DELETE FROM priv WHERE user_id = ? AND priv_id = ?

要删除用户的所有priv， DELETE FROM priv WHERE user_id =？

To delete all priv for a user, DELETE FROM priv WHERE user_id = ?

删除授权人的所有子用户... DELETE FROM priv WHERE grantor =？

To delete all sub users for a grantor... DELETE FROM priv WHERE grantor = ?

获取授权人中的用户的所有priv： SELECT * FROM priv WHERE user_id =？ AND grantor =？

Getting all privs for a user within a grantor: SELECT * FROM priv WHERE user_id = ? AND grantor = ?

这篇关于正确的方式来管理用户权限（用户层次结构）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！