湛兮
28 lines
1.3 KiB
SQL
28 lines
1.3 KiB
SQL
-- 006_create_role_permissions.sql
|
|
-- 角色权限关系表：把每个角色拥有的权限点落库，后台才能动态分配权限。
|
|
|
|
CREATE TABLE IF NOT EXISTS role_permissions (
|
|
role_id INT UNSIGNED NOT NULL COMMENT '角色 ID',
|
|
permission_code VARCHAR(100) NOT NULL COMMENT '权限点编码',
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (role_id, permission_code),
|
|
KEY idx_role_permissions_permission_code (permission_code),
|
|
CONSTRAINT fk_role_permissions_role_id FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色权限关系表';
|
|
|
|
-- 初始化内置角色的默认后台权限，保持历史行为，同时给管理员开放权限分配能力。
|
|
INSERT IGNORE INTO role_permissions (role_id, permission_code)
|
|
SELECT r.id, p.permission_code
|
|
FROM roles r
|
|
INNER JOIN (
|
|
SELECT 'admin' AS role_code, 'store:view' AS permission_code
|
|
UNION ALL SELECT 'admin', 'store:manage'
|
|
UNION ALL SELECT 'admin', 'role:view'
|
|
UNION ALL SELECT 'admin', 'role:manage'
|
|
UNION ALL SELECT 'admin', 'employee:view:all'
|
|
UNION ALL SELECT 'admin', 'employee:manage'
|
|
UNION ALL SELECT 'admin', 'permission:view'
|
|
UNION ALL SELECT 'admin', 'permission:manage'
|
|
UNION ALL SELECT 'store_manager', 'employee:view:store'
|
|
) p ON p.role_code = r.code;
|