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

基于角色的用户权限

图中共计包含了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_permissionCREATE 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_userCREATE 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_permissionCREATE 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_roleCREATE 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_permissionCREATE 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_roleCREATE 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_permissionFROM t_user_permission upJOIN t_permission p ON (p.permission_id = up.permission_id)WHERE user_id = 2UNIONSELECT p.permission_id, p.permission_name, p.parent_permissionFROM t_role_permission rpJOIN 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_permissionFROM t_permission pLEFT 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编程思想》。

ITPub博客

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

最新文章