问题描述
我想允许我的应用程式的使用者新增子使用者，并为每个子使用者设定允许查看或执行的权限。
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 = ?
这篇关于正确的方式来管理用户权限（用户层次结构）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！