设计支持层级权限查询的用户管理系统数据库

针对你需要的经理可查看全下属层级用户的用户管理系统，我整理了一套落地性强的实现方案：

一、数据库表设计 #

首先需要一张用户表来存储层级关系，核心字段要包含用户身份、上级关联：

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    role ENUM('manager', 'user') NOT NULL, -- 区分经理/普通用户
    parent_id INT NULL, -- 上级用户ID，顶级经理该字段为NULL
    FOREIGN KEY (parent_id) REFERENCES users(user_id)
);

按照你的示例插入数据的话，关联关系如下：

• M1的parent_id为NULL（顶级经理）
• U1、U2、M11的parent_id是M1的user_id
• U3、U4、M12的parent_id是M11的user_id
• U5、U6、U7的parent_id是M12的user_id

二、递归查询获取下属列表 #

因为是多层级的下属关系，需要用递归查询来获取当前经理所有层级的下属（包括下属经理的下属）。这里以MySQL 8.0+支持的WITH RECURSIVE为例：

示例：当M1登录时，查询所有可查看的用户 #

假设M1的user_id是1，执行以下查询：

WITH RECURSIVE subordinate_tree AS (
    -- 起始节点：当前登录的经理
    SELECT user_id, username, role FROM users WHERE user_id = 1
    UNION ALL
    -- 递归获取所有下属
    SELECT u.user_id, u.username, u.role 
    FROM users u
    JOIN subordinate_tree st ON u.parent_id = st.user_id
)
SELECT * FROM subordinate_tree;

这个查询会返回M1自己、U1、U2、M11、U3、U4、M12、U5、U6、U7，完全符合你的需求。

示例：当M12登录时，查询所有可查看的用户 #

假设M12的user_id是6，查询语句只需要把起始节点的user_id改成6：

WITH RECURSIVE subordinate_tree AS (
    SELECT user_id, username, role FROM users WHERE user_id = 6
    UNION ALL
    SELECT u.user_id, u.username, u.role 
    FROM users u
    JOIN subordinate_tree st ON u.parent_id = st.user_id
)
SELECT * FROM subordinate_tree;

结果会返回M12自己、U5、U6、U7，满足权限要求。

三、业务逻辑层的权限控制 #

在代码层面，你需要做这几步：

• 用户登录后，获取当前用户的user_id和role
• 如果是普通用户，只能查看自己的数据
• 如果是经理，先通过上面的递归查询获取所有可查看的user_id列表
• 之后所有用户数据的查询接口，都要加上user_id IN (可查看列表)的过滤条件，确保不会越权

比如用Python伪代码示例：

def get_viewable_user_ids(current_user_id, role):
    if role == 'user':
        return [current_user_id]
    # 执行递归查询获取所有下属及自己的user_id
    recursive_query = """
        WITH RECURSIVE subordinate_tree AS (
            SELECT user_id FROM users WHERE user_id = %s
            UNION ALL
            SELECT u.user_id FROM users u JOIN subordinate_tree st ON u.parent_id = st.user_id
        )
        SELECT user_id FROM subordinate_tree;
    """
    # 执行SQL并返回结果列表
    return db.execute(recursive_query, (current_user_id,)).fetchall()

# 在查询用户数据时使用
viewable_ids = get_viewable_user_ids(current_user.id, current_user.role)
user_data = User.query.filter(User.id.in_(viewable_ids)).all()

四、额外优化建议 #

• 可以给经理的下属列表做缓存，避免频繁执行递归查询，提升性能
• 在前端展示时，可以用树形组件来呈现层级关系，让经理更直观地查看下属结构

内容的提问来源于stack exchange，提问作者Victor