在我的应用程序中,我使用基于角色的访问控制来对用户进行身份验证和授权。可以为每个用户分配多个角色,这些角色提供访问权限。
每个角色都是层次结构的一部分,Verified角色位于树的顶部。所有其他角色都使用表中的自引用关系从中继承。默认角色表如下所示,其中有一个从Verified继承的Moderator和Developer角色,以及一个从Moderator继承的Admin角色。
roles table
id name parent role
1 verified none
2 moderator verified
3 developer verified
4 admin moderator
在为用户分配角色时,我想强制要求家长是必需的,这意味着如果用户具有管理员角色,他们还必须具有审核人和验证人。这意味着,如果用户失去了"主持人"角色,则其"管理员"角色应自动删除;如果"管理员"的父角色更改为"开发人员",则用户应失去"管理员",因为他们不再具有所需的父角色。
到目前为止,我想到的唯一解决方案包括在链接表上添加一个新列,该列引用已经分配的父列,例如:
role-user link table
id role user_id parent role-user link
1 verified 1 none
2 moderator 1 1
3 admin 1 2
这意味着,如果我从用户1中删除了主持人角色,他们将失去管理员角色,但如果我将管理员角色的父级更改为开发人员,主持人和管理员角色将不会从链接表中删除。有没有一种方法可以在只使用SQL/外键约束的情况下实现此功能?
假设您有一个结构如下的表:
create table role_user (
user_id int,
role_id int,
parent_role_id int,
primary key (user_id, role_id)
);
层次结构关系在表本身中进行了描述。为了增强完整性,可以在(user_id, parent_role_id)
上使用自引用复合外键,该外键引用同一表中(user_id, role_id)
列上的另一行。有了这个设置,您只需要启用选项on delete cascade
就可以获得您想要的行为:
create table role_user (
user_id int,
role_id int,
parent_role_id int,
primary key (user_id, role_id),
foreign key (user_id, parent_role_id)
references role_user(user_id, role_id)
on delete cascade
);
DB Fiddle上的演示:
-- sample data
select * from role_user;
user_id | role_id | parent_role_id
------: | ------: | -------------:
1 | 1 |
1 | 2 | 1
1 | 3 | 2
-- delete the top node in the hierarchy
delete from role_user where user_id = 1 and role_id = 1;
-- all children rows are deleted as well
select * from role_user;
user_id | role_id | parent_role_id
------: | ------: | -------------: