SQL 案例分析:用户权限管理

现代用户权限管理系统通常采用基于角色的访问控制方式。也就是说,用户可以拥有角色,角色可以拥有权限。另外,用户也可以直接拥有权限。这种方式使用的表结构如下图所示。

图片

基于角色的用户权限

图中共计包含了6张表,它们的简单描述如下:

  • 用户表(t_user)。用户编号(user_id)是主键,其他字段包括用户名(user_name)、密码(password)、电子邮箱(email)以及最后一次登录时间(last_login)。
  • 角色表(t_role)。角色编号(role_id)是主键,其他字段包括角色名(role_name)。
  • 权限表(t_permission)。权限编号(permission_id)是主键,其他字段包括权限名(permission_name)、父级权限(parent_permission)以及排序顺序(sort_order)。
  • 用户所属角色表(t_user_role)。通过用户编号(user_id)和用户表关联,通过角色编号(role_id)和角色表关联。
  • 用户权限表(t_user_permission)。通过用户编号(user_id)和用户表关联,通过权限编号(permission_id)和权限表关联。
  • 角色权限表(t_role_permission)。通过角色编号(role_id)和角色表关联,通过权限编号(permission_id)和权限表关联。

创建示例表的脚本如下:

-- 创建权限表t_permission
CREATE TABLE t_permission (
  permission_id INTEGER NOT NULL PRIMARY KEY,
  permission_name VARCHAR(100) NULL UNIQUE,
  parent_permission INTEGER NULL,
  sort_order INTEGER NULL DEFAULT 100
);
INSERT INTO t_permission VALUES (1, '系统管理', NULL, 1);
INSERT INTO t_permission VALUES (2, '员工管理', NULL, 11);
INSERT INTO t_permission VALUES (3, '查看员工信息', 2, 12);
INSERT INTO t_permission VALUES (4, '修改员工信息', 2, 13);

-- 创建用户表t_user
CREATE TABLE t_user (
  user_id INTEGER NOT NULL PRIMARY KEY,
  user_name VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(50) NOT NULL,
  email VARCHAR(200) NOT NULL,
  last_login timestamp
);
INSERT INTO t_user VALUES (1, 'Admin', 'e10adc3949ba59abbe56e057f20f883e', 'admin@shuguo.com', NULL);
INSERT INTO t_user VALUES (2, 'Tony', 'eee7ac208064d408e84ab5e26d24b278', 'tony@shuguo.com', NULL);

-- 创建用户权限表t_user_permission
CREATE TABLE t_user_permission (
  user_id INTEGER NOT NULL,
  permission_id INTEGER NOT NULL,
  CONSTRAINT pk_user_permission PRIMARY KEY (user_id, permission_id),
  CONSTRAINT fk_user_permission_user FOREIGN KEY (user_id) REFERENCES t_user(user_id),
  CONSTRAINT fk_user_permission_permission FOREIGN KEY (permission_id) REFERENCES t_permission(permission_id)
);
INSERT INTO t_user_permission VALUES (2, 2);
INSERT INTO t_user_permission VALUES (2, 3);

-- 创建角色表t_role
CREATE TABLE t_role (
  role_id INTEGER NOT NULL PRIMARY KEY,
  role_name  VARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO t_role VALUES (1, '系统管理员');
INSERT INTO t_role VALUES (2, '人力资源专员');

-- 创建角色权限表t_role_permission
CREATE TABLE t_role_permission (
  role_id INTEGER NOT NULL,
  permission_id INTEGER NOT NULL,
  CONSTRAINT pk_role_permission PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_role_permission_user FOREIGN KEY (role_id) REFERENCES t_role(role_id),
  CONSTRAINT fk_role_permission_permission FOREIGN KEY (permission_id) REFERENCES t_permission(permission_id)
);
INSERT INTO t_role_permission VALUES (1, 1);
INSERT INTO t_role_permission VALUES (2, 2);
INSERT INTO t_role_permission VALUES (2, 3);
INSERT INTO t_role_permission VALUES (2, 4);

-- 创建用户角色表t_user_role
CREATE TABLE t_user_role (
  user_id INTEGER NOT NULL,
  role_id INTEGER NOT NULL,
  CONSTRAINT pk_user_role PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_user_role_user FOREIGN KEY (user_id) REFERENCES t_user(user_id),
  CONSTRAINT fk_user_role_role FOREIGN KEY (role_id) REFERENCES t_role(role_id)
);
INSERT INTO t_user_role VALUES (1, 1);
INSERT INTO t_user_role VALUES (2, 2);

下面我们分析一个具体的业务需求。当用户登录系统后,应用前端需要根据用户的权限显示不同的页面,那么我们应该如何获得该用户的权限呢?

用户的权限既可以通过角色获得,也可以直接通过用户权限表获得。因此,我们可以使用UNION运算符实现这一需求:

SELECT p.permission_id, p.permission_name, p.parent_permission
FROM t_user_permission up
JOIN t_permission p ON (p.permission_id = up.permission_id)
WHERE user_id = 2
UNION
SELECT p.permission_id, p.permission_name, p.parent_permission
FROM t_role_permission rp
JOIN t_permission p ON (p.permission_id = rp.permission_id)
JOIN t_user_role ur ON (rp.role_id = ur.role_id)
WHERE ur.user_id = 2;

UNION运算符左边的查询语句返回了用户直接获得的权限,右边的查询语句返回了用户通过角色获得的权限。我们使用了UNION运算符而不是UNION ALL运算符,因为两个查询语句可能会返回重复的权限。查询返回的结果如下。

permission_id|permission_name|parent_permission
-------------|---------------|-----------------
            2|员工管理        |                
            3|查看员工信息    |                2
            4|修改员工信息    |                2

进一步来说,我们不但需要返回用户拥有的权限,还需要知道该用户没有获得的权限,类似于以下结果。

permission_id|permission_name|parent_permission|has_permission
-------------|---------------|-----------------|--------------
            1|系统管理         |                 |N            
            2|员工管理         |                 |Y            
            3|查看员工信息     |                2|Y            
            4|修改员工信息     |                2|Y            

其中,has_permission字段结果为“Y”表示用户拥有该权限,结果为“N”表示用户没有该权限。为此,我们可以在以上示例的基础上使用外连接查询连接权限表:

SELECT p.permission_id, p.permission_name, p.parent_permission,
      COALESCE(t.has_permission,'N') AS has_permission
FROM t_permission p
LEFT JOIN(
     SELECT p.permission_id,'Y' has_permission
     FROM t_user_permission up
     JOIN t_permission p ON (p.permission_id = up.permission_id)
     WHERE user_id = 2
     UNION
     SELECT p.permission_id,'Y'
     FROM t_role_permission rp
     JOIN t_permission p ON (p.permission_id = rp.permission_id)
     JOIN t_user_role ur ON (rp.role_id = ur.role_id)
     WHERE ur.user_id = 2
     ) t ON (t.permission_id= p.permission_id)
ORDER BY p.sort_order;

其中,左连接查询返回了所有的权限,COALESCE函数返回了用户拥有的权限(Y)和用户没有获得的权限(N)。

以上案例来自图书《SQL编程思想》。

发表评论

登录后才能评论
网站客服
网站客服
申请收录 侵权处理
分享本页
返回顶部