MySQL 基于角色的访问控制的数据库架构

DB Schema of a Role Based Access Control

提问by sled

I'm currently developing a member administration for a local association here and I'm developing the database schema at the moment. I'd like to share it with you to improve it and give other an example of a Role Based Access Model (RBAC). I'd appreciate any constructive criticism especially about the relationships I used between the tables.

我目前正在为当地协会开发成员管理，目前我正在开发数据库模式。我想与您分享它以改进它，并提供一个基于角色的访问模型 (RBAC) 的示例。我很感激任何建设性的批评，特别是关于我在表格之间使用的关系。

Link to highres: http://i.stack.imgur.com/WG3Vz.png

高分辨率链接：http: //i.stack.imgur.com/WG3Vz.png

Heres the schema:

这是架构：

How it works:

这个怎么运作：

I'm mapping existing clients (actually members of the association) from an external application into my administration application. (clients table)

我正在将现有客户端（实际上是协会成员）从外部应用程序映射到我的管理应用程序。（客户表）

The association is structured in Division, Subdivisions, etc. (intern_structures table). Every client can be a member in multiple Division, Subdivisions, Sections etc.

关联的结构分为部门、细分等（intern_structures 表）。每个客户都可以是多个部门、细分部门、部门等的成员。

Every client can have one or multiple roles in such memberships (divisions,...) like President, Actuary, Treasurer etc. and each role has certain privileges which the owner of the role can apply on others in his Division,Subdivision,Section etc.

每个客户都可以在这样的成员资格（部门，...）中担任一个或多个角色，例如总裁、精算师、财务主管等，并且每个角色都具有某些特权，该角色的所有者可以将这些特权应用于其部门、分部、部门等中的其他人.

A credential is connected to a certain action of an application. The owner of the credential may execute this action on other members in his scope. There can be multiple "standalone" applications but they all share the same authentication/authorization system.

凭据与应用程序的某个操作相关联。凭证的所有者可以对他范围内的其他成员执行此操作。可以有多个“独立”应用程序，但它们都共享相同的身份验证/授权系统。

An application is structured in Modules/Submodules/Actions etc. An example could be a "Personal Details" module and this module contains a submodule called "Picture" and you could apply the actions "view,delete,edit" on this picture. But you can't delete any picture unless the person whose picture you try to delete is in a division/section where you have the adequate role to do so.

应用程序由模块/子模块/动作等构成。一个例子可以是“个人详细信息”模块，该模块包含一个名为“图片”的子模块，您可以在这张图片上应用“查看、删除、编辑”动作。但是您不能删除任何图片，除非您尝试删除其图片的人在您有足够角色执行此操作的部门/部门。

The internal and application structure are both trees, implemented as adjacency list andnested set. The adjacency list ensures the integrity and the nested set allows me to traverse the tree quickly.

内部结构和应用结构都是树，实现为邻接表和嵌套集。邻接表保证了完整性，嵌套集可以让我快速遍历树。

An exception is that you can give someone certain credentials directly (client_credentials). This is needed if someone needs to perform certain actions on somebody who isn't in his divsion/section.

一个例外是您可以直接向某人提供某些凭据 (client_credentials)。如果有人需要对不在他的部门/部门中的人执行某些操作，则需要这样做。

So, someone can be a member in multiple divsions/sections and obtain multiple roles in every division/section he's a member of. I'm going to merge all credentials someone has through his multiple roles. And credentials are always positive, means restrictive credentials are not possible.

因此，某人可以成为多个部门/部门的成员，并在他所属的每个部门/部门中获得多个角色。我将合并某人通过他的多个角色拥有的所有凭据。并且凭据始终是正的，这意味着限制性凭据是不可能的。

回答by AnaZgombic

I'm going to give another example of an RBAC system I really like. please check out the radicore framework by Tony Marston here.

我将给出另一个我非常喜欢的 RBAC 系统的例子。请在此处查看Tony Marston 的 radicore 框架。

I'm not sure if it meets all of your requirements but something you can compare your work with can help.

我不确定它是否满足您的所有要求，但是您可以将您的工作与之进行比较的东西会有所帮助。

回答by Jeach

I don't seem to be seeing much of the RBAC mappings, such as:

我似乎没有看到很多 RBAC 映射，例如：

Operation  = Any action, such as CRUD operations
Object     = Reference to any object instance

Permission = Mapping of 'Operation' + 'Object'

I'm not sure what all your "credential" tables are? A credential normally holds properties to prove one's identity (ie: username/password). Why do you have credentials for roles?

我不确定你所有的“凭证”表是什么？凭证通常包含证明一个人身份的属性（即：用户名/密码）。为什么你有角色的凭据？