SQL 数据权限的一种实现形式

采用用户组织文章来举例子

用户有唯一组织； 文章有多个组织可见； 只要用户在组织下就可以查看到文章。

-- 创建示例表 user_organization_permissions
CREATE TABLE IF NOT EXISTS user_organization_permissions (
    user_id INT,
    organization_id INT
);

-- 创建示例表 article_organizations
CREATE TABLE IF NOT EXISTS article_organizations (
    article_id INT,
    organization_ids VARCHAR(255)
);

-- 插入示例数据到 user_organization_permissions
INSERT INTO user_organization_permissions (user_id, organization_id) VALUES
(1, 1),
(2, 3),
(3, 4);

-- 插入示例数据到 article_organizations
INSERT INTO article_organizations (article_id, organization_ids) VALUES
(101, '1,2,3'),
(102, '4,5,6'),
(103, '2,3,7');

-- 查询用户有权查看的文章
SELECT 
    ao.article_id,
    uop.user_id
FROM 
    article_organizations ao
JOIN 
    user_organization_permissions uop ON FIND_IN_SET(CAST(uop.organization_id AS CHAR), ao.organization_ids) > 0;

输出

+------------+---------+
| article_id | user_id |
+------------+---------+
| 101        | 1       |  
| 101        | 2       | 
| 103        | 2       |  
| 102        | 3       |
+------------+---------+