细粒度数据库记录访问权限(例如，组“X”和个人“Smith”可以查看记录Z)
我的记录（联系人、地址等）应可供以下任意人员访问（包括组合，例如 2 个组和 4 个个人）：
- 每个人
- 多个组/部门的成员
- 单个组/部门的成员组/部门
- 多个个体
- 单个个体
实现这一目标的良好数据库结构是什么？基本上，在我的应用程序中，我需要能够限制用户 XYZ 登录时仅向他显示他作为个人、组成员“可查看”的记录，或者因为它们对每个人都可见。
基本上我正在寻找一种有效存储这些关系的方法。它应该很快，因为我会在每次有人查看任何内容时查看关系。
我真的很感激一些关于如何做到这一点的提示！
谢谢你！
编辑：我使用的是 SQL Server 2008 Web 版。
I have records (Contacts, Addresses etc.) which should be accessible to any of the following (incl. combinations, e.g. 2 groups and 4 individuals):
- Everybody
- Members of multiple Groups/Departments
- Members of a single Group/Department
- Multiple Individuals
- Single Individual
What is a good database structure to realize this? Basically in my application I need to be able to restrict when user XYZ is logged in to only show him the records that are "viewable" to him as an individual, member of a group, or because they are visible to everybody.
Basically I'm looking for a way to efficiently store these relationships. It should be fast as I would look at the relationships every time anybody looks at anything.
I'd really appreciate some hints on how to do this!
Thank you!
Edit: I'm using SQL Server 2008 Web Edition.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这正在朝着 RBAC（基于角色的访问控制）的方向发展。您可能还想知道是否使用 LBAC - 基于标签的访问控制。而且，根据您的 DBMS，可能还有其他方法来实现它（例如，考虑 Oracle VPD - 虚拟专用数据库）。所有这些要么是特定于 DBMS 的，要么是非常特定的——针对不同 DBMS 的不同解决方案。
您似乎在谈论行级别的控制。也就是说，联系人表中的一行可能可供所有人访问，而另一行仅可供一组部门访问，另一行仅可供一组人员访问，依此类推。
请记住，关系 DBMS 与集合配合使用效果最佳。单个组是具有一个成员组的一组组；单个用户是一组包含一个成员用户的组。这意味着我们需要处理的案件更少。
如果您想在标准 SQL 中实现它，那么我认为您将需要使用利用与控制表等的联接的视图组合。这种系统的困难部分是填充控制表并限制管理用户（实际上，约束管理员始终是困难的部分之一）。
基本技术是：
现在，关于连接列和控制表...
有人必须指定哪些权限适用于表中新插入的行 - 提供的默认访问权限是什么。并且必须有人定义如何覆盖默认访问权限。两者都可能很混乱。
有多种方法可以构建控制表：
一种机制依赖于基表中的每一行都具有唯一的 ID（可能是自动生成的 ID 或只是主键的值）。然后，控制表包含该唯一 ID 的副本，并定义哪些用户或组可以访问它。这意味着控制表中的给定行可能有多个条目，每个条目对应一个可以访问该行的用户或组。在此方案中，控制表有一个引用基表的外键。
另一种机制将 ID 号嵌入到基表中，该基表是控制表的外键。它基本上标识了一组权限，基表中的引用意味着该行具有与访问控制 ID 关联的访问权限。控制表背后的结构可能是 ID 0 对任何人都没有访问权限（通过视图），ID 1 对每个人都有访问权限，其他值指定用户和组的组合 - 每个不同的组合都有不同的 ID。这样，控制表集中可能有多个表 - 我们还在讨论为每个受保护的表设置一组这些控制表。
显然，对控制表的访问受到严格限制，但对于管理谁可以看到什么也至关重要。
这两者都是管理噩梦 - 这就是为什么您最终可能会得到 DBMS 提供的访问控制机制而不是通用 SQL 解决方案。
This is lurching in the direction of RBAC - role-based access control. You might also wonder about whether to use LBAC - label-based access control. And, depending on your DBMS, there could be other ways to achieve it (consider Oracle VPD - virtual private database - for example). All of this is either rather or very DBMS specific - different solutions for different DBMS.
You seem to be talking about control at the row level. That is, one row in the contact table may be accessible to everybody, while another is accessible only to one set of departments, another is only accessible to one group of people, and so on.
Remember that relational DBMS work best with sets. A single group is a set of groups with one member group; a single user is a set of groups with one member user. This means we have fewer cases to deal with.
If you want to implement it in Standard SQL, then I think you are going to need to use a combination of views exploiting joins with control tables, etc. The hard parts with such a system are populating the control tables and restraining the administrative users (actually, restraining administrators is always one of the hard parts).
The basic technique would be:
Now, about that joining column and the control table...
Someone has to specify what permissions apply to newly inserted rows in the table - what is the default access provided. And someone has to define how the default access can be overridden. Both of those could be messy.
There are several ways to structure the control table:
One mechanism relies on a each row in the base table having a unique ID (which might be an automatic generated ID or just the value of the primary key). The control table then includes a copy of that unique ID, and defines which users or groups can access it. This means that there may be multiple entries in the control table for a given row, one for each user or group that can access the row. In this scheme, the control table has a foreign key that references the base table.
Another mechanism embeds an ID number into the base table that is a foreign key to the control table(s). It basically identifies a set of privileges, and the reference in the base table means that the row has the access permission associated with the access control ID. The structure behind the control table might be that the ID 0 has no access for anyone (via the view), ID 1 has access for everyone, and other values designate combinations of users and groups - each different combination has a different ID. With this, there might be several tables in the set of control tables - and we're also discussing having a set of these control tables for each table that is protected.
Clearly, access to the control tables is strictly limited - but also crucial to the management of who can see what.
Both of these are administrative nightmares - which is why you probably end up with a DBMS-provided access control mechanism rather than a generic SQL solution.
我同意乔纳森关于技术的观点，但不一定同意噩梦。我已经通过权利联盟的单一视图实现了这一点，该视图基于：
基本记录
基本记录
基本记录
不管你信不信，性能都很好，尽管基表从来不超过 250K 条记录……显然，更大的基表可能需要更精细的设计。但就我们而言，它运作良好，管理根本不是什么大问题。创建者和临时用户组分配是唯一实际广泛使用的规则。分配/撤销对组的访问权限是一项持续的任务，但这是一项随地域而来的任务。
I agree with Jonathon about the technique but not necessarily about the nightmare. I've implemented this with a single view of a union of rights based on:
base record
the base record
the base record
Performance was fine, believe it or not, although the base table was never larger than about 250K records... obviously, a larger base table might require a more elaborate design. But in our case, it worked well and administration was not a big deal at all. The created-by and ad hoc user group assignment were the only rules that actually got used on any sort of wide scale. Assigning / revoking access to groups was an ongoing task, but one which comes with the territory.