简单又灵活的权限设计？

// resources: 
{ code: 'projects', name: 'projects', type: 'module' },
{ code: 'users', name: 'users', type: 'module' },
复制代码

// roles: 
{ code: 'admin', name: 'admin' },
{ code: 'guess', name: 'guess' },

// role_permissions:
{ roleCode: 'admin', resourceCode: 'projects', operation: 'C' },
{ roleCode: 'admin', resourceCode: 'projects', operation: 'D' },
{ roleCode: 'admin', resourceCode: 'projects', operation: 'R' },
{ roleCode: 'admin', resourceCode: 'projects', operation: 'U' },
{ roleCode: 'admin', resourceCode: 'users', operation: 'C' },
{ roleCode: 'admin', resourceCode: 'users', operation: 'D' },
{ roleCode: 'admin', resourceCode: 'users', operation: 'R' },
{ roleCode: 'admin', resourceCode: 'users', operation: 'U' },
{ roleCode: 'guess', resourceCode: 'projects', operation: 'R' },
复制代码

// users:
{ code: 'user_a', name: 'user_a' },
{ code: 'user_b', name: 'user_b' },

// user_role:
{ userCode: 'user_a', roleCode: 'admin' },
{ userCode: 'user_b', roleCode: 'guess' },
复制代码

SELECT 
    u.code userCode,
    res.code resourceCode,
    GROUP_CONCAT(DISTINCT rp.operation) operations
FROM
    resources res,
    role_permissions rp,
    roles r,
    user_role ur,
    users u
WHERE
    res.code = rp.resource_code
        AND rp.role_code = r.code
        AND r.code = ur.role_code
        AND ur.user_code = u.code
        AND res.type = 'module'
        AND u.code = 'user_a'
GROUP BY u.code , res.code
复制代码

CREATE VIEW `user_module_view` AS
    SELECT 
        ur.user_code,
        rp.resource_code,
        CONCAT('|',
                GROUP_CONCAT(DISTINCT rp.operation
                    SEPARATOR '|'),
                '|') operation
    FROM
        user_role ur,
        role_permissions rp,
        resources rs
    WHERE
        ur.role_code = rp.role_code
            AND rs.code = rp.resource_code
            AND rs.type = 'module'
    GROUP BY rp.resource_code , ur.user_code
复制代码

SELECT 
    *
FROM
    user_module_view
WHERE
    user_code = 'user_a'
复制代码

{ roleCode: 'guess', resourceCode: 'projects', operation: 'R' } 
=>
{ roleCode: 'guess', resourceCode: 'projects', operation: 'R_ORG' },
复制代码

// 1. add resource:  
{ code: 'project_a', name: 'project_a', type: 'project' }


// 2. add roles: 
{ code: 'pro_a_view', name: 'pro_a_view' },
{ code: 'pro_a_edit', name: 'pro_a_edit' },
{ code: 'pro_a_admin', name: 'pro_a_admin' },

// 3. add role_permission:
{ roleCode: 'pro_a_view', resourceCode: 'project_a', operation: 'R' },
{ roleCode: 'pro_a_edit', resourceCode: 'project_a', operation: 'R' },
{ roleCode: 'pro_a_edit', resourceCode: 'project_a', operation: 'U' },
{ roleCode: 'pro_a_admin', resourceCode: 'project_a', operation: 'R' },
{ roleCode: 'pro_a_admin', resourceCode: 'project_a', operation: 'U' },
{ roleCode: 'pro_a_admin', resourceCode: 'project_a', operation: 'D' },
复制代码

CREATE VIEW 'user_project_view' AS 
    SELECT 
        ur.user_code,
        rp.resource_code,
        CONCAT('|',
                GROUP_CONCAT(DISTINCT rp.operation
                    SEPARATOR '|'),
                '|') operation
    FROM
        user_role ur,
        role_permissions rp,
        resources rs
    WHERE
        ur.role_code = rp.role_code
            AND rs.code = rp.resource_code
            AND rs.type = 'project'
    GROUP BY rp.resource_code , ur.user_code
复制代码

SELECT 
    *
FROM
    user_project_view
WHERE
    user_code = 'user_b'
        AND operation LIKE '%|R|%'
复制代码