SQL案例分析：应用系统用户权限设计

1.问题描述

　　用户权限管理系统通常采用基于角色的访问控制方式（RBAC）。角色拥有权限，用户拥有角色。另外用户也可以直接拥有权限。如何获取某个登录用户的权限？

2.初始脚本及表结构关系

1.初始脚本

 -- 创建权限表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);
 ​

2.表结构关系

3.思路分析

1. 用户直接拥有的权限

    -- 查询id为2的用户直接拥有的权限
    select tp.permission_id ,tp.permission_name,tp.parent_permission 
    from t_user_permission tup 
    join t_permission tp on tp.permission_id =tup.permission_id 
    where tup.user_id =2
   

2. 用户通过角色获取的权限

    select tp.permission_id ,tp.permission_name,tp.parent_permission 
    from t_role_permission trp 
    join t_permission tp on tp .permission_id =trp.permission_id 
    join t_user_role tur on  trp .role_id =tur.role_id 
    where tur.user_id =2
   

3. 用户的所有权限

    select tp.permission_id ,tp.permission_name,tp.parent_permission 
    from t_user_permission tup 
    join t_permission tp on tp.permission_id =tup.permission_id 
    where tup.user_id =2
    union 
    select tp.permission_id ,tp.permission_name,tp.parent_permission 
    from t_role_permission trp 
    join t_permission tp on tp .permission_id =trp.permission_id 
    join t_user_role tur on  trp .role_id =tur.role_id 
    where tur.user_id =2
   

4. 返回所有权限和改用户是否拥有权限,coalesce ()判断当前字段是否为空得到。

    select tp.permission_id ,tp .permission_name ,tp .permission_name ,coalesce (p.has_permisson,'N')has_permisson
    from t_permission tp 
    left join (
    select tp.permission_id ,tp.permission_name,tp.parent_permission ,'Y' has_permisson 
    from t_user_permission tup 
    join t_permission tp on tp.permission_id =tup.permission_id 
    where tup.user_id =2
    union 
    select tp.permission_id ,tp.permission_name,tp.parent_permission ,'Y' has_permisson 
    from t_role_permission trp 
    join t_permission tp on tp .permission_id =trp.permission_id 
    join t_user_role tur on  trp .role_id =tur.role_id 
    where tur.user_id =2
    ) p on p.permission_id =tp.permission_id 
   

   如下结果：

5. 扩展：MySQL COALESCE函数介绍

   1. COALESCE函数语法

       COALESCE(value1,value2,...);    
      

   2. COALESCE函数需要许多参数，并返回第一个非NULL参数。如果所有参数都为NULL，则COALESCE函数返回NULL。