用户具有多重角色，角色拥有可重复权限，确定用户具有权限的数据库设计方案（关系型数据库）

问题：系统有多用户，用户可以拥有不止一个角色（又称身份、职业、类型等），每一个角色可以有很多种权限，不同角色的权限允许重复。

怎么才能方便地记录、检索用户和权限的对应关系呢？

按照标准的数据建模原则，应该建立如下的表，表示三种数据实体：用户、角色以及权限

CREATE TABLE User
(
	id	INTEGER,
	name	VARCHAR(16),
	CONSTRAINT cst_User_pk PRIMARY KEY (id)
);

CREATE TABLE Role
(
	id	INTEGER,
	info	VARCHAR(16),
	CONSTRAINT cst_Role_pk PRIMARY KEY (id)
);

CREATE TABLE Right
(
	id	INTEGER,
	info	VARCHAR(16),
	CONSTRAINT cst_Right_pk PRIMARY KEY (id)
);

接下来，按照标准数据建模原则，应该建立如下二张表，用来表示实体之间的多多对应关系。

CREATE TABLE UserRole
(
	userId	INTEGER,
	roleId	INTEGER,
	CONSTRAINT cst_UserRole_fk_userId FOREIGN KEY (userId) REFERENCES User(id),
	CONSTRAINT cst_UserRole_fk_roleId FOREIGN KEY (roleId) REFERENCES Role(id)
);

<pre name="code" class="sql">CREATE TABLE RoleRight
(
	roleId	INTEGER,
	rightId	INTEGER,
	CONSTRAINT cst_RoleRight_fk_userId FOREIGN KEY (roleId) REFERENCES Role(id),
	CONSTRAINT cst_RoleRight_fk_rightId FOREIGN KEY (rightId) REFERENCES Right(id)
);

这就是标准做法。能够严格地表现一份数据就是一个实体行，绝无数据冗余和不安全关系隐患。问题是，这么设计过之后，检索用户所具有权限的理论遍历量是：
最大理论遍历量
= 用户数量 x 用户角色关系数 x 角色数 x 角色权限关系数 x 权限数
= cUs * (aUR * cUs) * (aRR * cRo) * cRi
= aUR * aRR * cUs^2 * cRo * cRi
其中 aUR 是平均一个用户有几种角色，aRR 是平均一个角色有多少权限；此二者可以认为是常数
cUs 是用户数量，cRo 是角色数量，cRi 是权限数量。此三者由系统的大小和类型决定。

对于一个 100 用户，15 种角色，平均一个用户 3 种角色，一共 100 种权限，平均一个角色 20 种权限；就这么一个中小型的系统；标准数据建模原则指导下建成的数据表，将为了一个简单的用户权限查询进行 9 0000 0000 ，也就是接近十亿次最大理论遍历量。当然主键索引可以极大程度地缩减查询时间，但无论如何都无法掩盖这种数据建模方式直接造成了用户权限检索消耗是用户数量二次方的函数。这将造成用户权限查询难度随用户规模扩大二次方级地加剧。

常见的解决方法一般是制作一些数据冗余。比如如果权限表字段比较少，可以考虑取消权限表，将其内容重复地放入 RoleRight 表

CREATE TABLE RoleRight ( roleId INTEGER, rightId INTEGER, rightInfo VARCHAR(16), CONSTRAINT cst_RoleRight_fk_roleId FOREIGN KEY (roleId) REFERENCES Role(id), INDEX idx_RoleRight_roleId USING BTREE (rightId) );数据多了为了索引效率，也可以制作 (roleId, rightId) 的主键。但这都不解决根本问题，无法消除多表联查查询成本随用户规模二次方增长的问题。因为用户表一般都很大，没办法专门冗余到权限相关的表中来——就算能这么冗余，从逻辑上也不该做这样的设计。

除了增加数据冗余，还可以通过少量削弱数据完整性控制来让查询一个人是否具备什么权限，或者查询一个人具备哪些权限变得简单，同时也为增加不属于任何角色的单独权限赋予用户提供了数据结构上的便利。
CREATE TABLE UserRight ( userId INTEGER, rightId INTEGER, CONSTRAINT cst_UserRight_fk_userId FOREIGN KEY (userId) REFERENCES User(id), CONSTRAINT cst_UserRight_fk_rightId FOREIGN KEY (rightId) REFERENCES Right(id) );这种方案检索成本确实非常低。最大遍历量是 aUR * aRR * cUs 。不过使用时，每次改动了人员和角色的关系，每次改动角色和权限的关系，都要重新刷这张表，根据当时的对应关系，把每个用户和他具备什么权限查询出来，记录在这张表中。这种方式的问题由此而来；如果改动角色和权限的关系，则会有较大成本的 update ，实算的结果是与其 update 不如直接把表删掉重新 insert 一遍。这是因为如果要 update 的话，我们得通过联查才能知道一次修改删除了哪些权限，增加了哪些权限。然后用对应的角色标识知道哪些人有这个角色，在检索这些人其余的角色中是不是有对已删除权限的重复；如果有重复则不能删除对应的人-权限关系。

前天建库，自己发明了更好用的方式。舍弃 UserRole 和 RoleRight 二张关系表，制作三字段 UserRoleRight 关系表。CREATE TABLE UserRoleRight ( userId INTEGER, roldId INTEGER, rightId INTEGER, CONSTRAINT ctx_UserRoleRight_fk_userId FOREIGN KEY (userId) REFERENCES User(id), CONSTRAINT ctx_UserRoleRight_fk_roleId FOREIGN KEY (roldId) REFERENCES Role(id), CONSTRAINT ctx_UserRoleRight_fk_rightId FOREIGN KEY (rightId) REFERENCES Right(id) );把所有的关系都冗余在这里边。为了防止某个某一个角色没有任何人具有，从而丢失了其与权限的对应关系，我们需要添加一个特殊用户，具有所有的角色。同样，为了避免某一个角色没有任何权限，造成丢失这个角色和用户的对应关系，也需要制作一个通用权限，所有的角色都有这个权限。这种方式的检索最大遍历量：
最大理论遍历量
= 正常用户记录量 + 特殊用户记录量
= cUs * aUR * (aRR + 1) + cRo * (aRR + 1)
= (aUR * aRR * cUs) + aUR * cUs + (aRR + 1) * cRo

我们可以看到这种方式检索成本也是随用户量增大一次方增大的。比上述难以维护的增加用户权限关系表的方案，多了 aUR * cUs + (aRR + 1) * cRo 这么多检索量。所以我们求算一下增加量所占检索成本的比例。
(aUR * cUs + (aRR + 1) * cRo) : (aUR * aRR * cUs)
约= 1/aRR + cRo/(aUR * cUs)
如果一个系统设计的角色比较精简，每个用户重用角色比较充分，此设计就几乎没有增加什么检索负担。但是因为存在了作为模板的特殊用户，所以修改用户角色关系，修改角色权限关系都变得非常简单。

posted on 2012-04-17 04:56 springside5 阅读(858) 评论(0) 编辑收藏举报