Página inicial Explorar Ajuda
Registrar Entrar
151-template-132
/
d8d-vite-starter
fork de 141-template-118/d8d-vite-starter
2
0
Fork 0
Arquivos
Tree: 8b2d33dde1
Branches Tags
d8d-vite-starte...
/
docs
/
crm-permission-init.md

crm-permission-init.md 16 KB
Histórico Raw

CRM系统权限体系初始化SQL脚本

概述

基于CRM权限体系改进方案的完整SQL初始化脚本，包含所有权限、角色、角色权限关联及默认管理员用户配置。

数据库结构

  • permission - 权限表
  • role - 角色表
  • role_permission - 角色权限关联表
  • users - 用户表
  • user_roles - 用户角色关联表

完整初始化脚本

1. 清除旧数据（谨慎使用）

-- 清除旧权限数据（生产环境慎用）
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE role_permission;
TRUNCATE TABLE permission;
TRUNCATE TABLE role;
SET FOREIGN_KEY_CHECKS = 1;

2. 插入基础权限数据

-- 系统管理权限
INSERT INTO permission (code, name, type, module, action, description, sort_order, is_active, parent_id) VALUES
('system:user:create', '创建用户', 'operation', 'system', 'user:create', '允许创建新用户', 110, 1, NULL),
('system:user:update', '编辑用户', 'operation', 'system', 'user:update', '允许编辑用户信息', 120, 1, NULL),
('system:user:delete', '删除用户', 'operation', 'system', 'user:delete', '允许删除用户', 130, 1, NULL),
('system:user:view:own', '查看个人用户', 'data', 'system', 'user:view', '仅查看自己创建的用户', 141, 1, NULL),
('system:user:view:department', '查看部门用户', 'data', 'system', 'user:view', '查看所在部门用户', 142, 1, NULL),
('system:user:view:sub_department', '查看部门及下级用户', 'data', 'system', 'user:view', '查看所在部门及下级部门用户', 143, 1, NULL),
('system:user:view:all', '查看所有用户', 'data', 'system', 'user:view', '查看系统所有用户', 144, 1, NULL),

('system:role:create', '创建角色', 'operation', 'system', 'role:create', '允许创建新角色', 210, 1, NULL),
('system:role:update', '编辑角色', 'operation', 'system', 'role:update', '允许编辑角色信息', 220, 1, NULL),
('system:role:delete', '删除角色', 'operation', 'system', 'role:delete', '允许删除角色', 230, 1, NULL),
('system:role:view', '查看角色', 'operation', 'system', 'role:view', '允许查看角色列表', 240, 1, NULL),

('system:department:create', '创建部门', 'operation', 'system', 'department:create', '允许创建新部门', 310, 1, NULL),
('system:department:update', '编辑部门', 'operation', 'system', 'department:update', '允许编辑部门信息', 320, 1, NULL),
('system:department:delete', '删除部门', 'operation', 'system', 'department:delete', '允许删除部门', 330, 1, NULL),
('system:department:view', '查看部门', 'operation', 'system', 'department:view', '允许查看部门列表', 340, 1, NULL),

-- 客户管理权限
('client:create', '创建客户', 'operation', 'client', 'create', '允许创建新客户', 410, 1, NULL),
('client:update', '编辑客户', 'operation', 'client', 'update', '允许编辑客户信息', 420, 1, NULL),
('client:delete', '删除客户', 'operation', 'client', 'delete', '允许删除客户', 430, 1, NULL),
('client:view:own', '查看个人客户', 'data', 'client', 'view', '仅查看自己创建的客户', 441, 1, NULL),
('client:view:department', '查看部门客户', 'data', 'client', 'view', '查看所在部门客户', 442, 1, NULL),
('client:view:sub_department', '查看部门及下级客户', 'data', 'client', 'view', '查看所在部门及下级部门客户', 443, 1, NULL),
('client:view:all', '查看所有客户', 'data', 'client', 'view', '查看系统所有客户', 444, 1, NULL),
('client:assign', '分配客户', 'operation', 'client', 'assign', '允许将客户分配给其他用户', 450, 1, NULL),
('client:transfer', '转移客户', 'operation', 'client', 'transfer', '允许将客户转移到其他部门', 460, 1, NULL),

-- 合同管理权限
('contract:create', '创建合同', 'operation', 'contract', 'create', '允许创建新合同', 510, 1, NULL),
('contract:update', '编辑合同', 'operation', 'contract', 'update', '允许编辑合同信息', 520, 1, NULL),
('contract:delete', '删除合同', 'operation', 'contract', 'delete', '允许删除合同', 530, 1, NULL),
('contract:view:own', '查看个人合同', 'data', 'contract', 'view', '仅查看自己创建的合同', 541, 1, NULL),
('contract:view:department', '查看部门合同', 'data', 'contract', 'view', '查看所在部门合同', 542, 1, NULL),
('contract:view:sub_department', '查看部门及下级合同', 'data', 'contract', 'view', '查看所在部门及下级部门合同', 543, 1, NULL),
('contract:view:all', '查看所有合同', 'data', 'contract', 'view', '查看系统所有合同', 544, 1, NULL),
('contract:approve', '审批合同', 'operation', 'contract', 'approve', '允许审批合同', 550, 1, NULL),
('contract:renew', '续签合同', 'operation', 'contract', 'renew', '允许续签合同', 560, 1, NULL),

-- 跟进记录权限
('follow_up:create', '创建跟进', 'operation', 'follow_up', 'create', '允许创建跟进记录', 610, 1, NULL),
('follow_up:update', '编辑跟进', 'operation', 'follow_up', 'update', '允许编辑跟进记录', 620, 1, NULL),
('follow_up:delete', '删除跟进', 'operation', 'follow_up', 'delete', '允许删除跟进记录', 630, 1, NULL),
('follow_up:view:own', '查看个人跟进', 'data', 'follow_up', 'view', '仅查看自己创建的跟进记录', 641, 1, NULL),
('follow_up:view:department', '查看部门跟进', 'data', 'follow_up', 'view', '查看所在部门跟进记录', 642, 1, NULL),
('follow_up:view:sub_department', '查看部门及下级跟进', 'data', 'follow_up', 'view', '查看所在部门及下级部门跟进记录', 643, 1, NULL),
('follow_up:view:all', '查看所有跟进', 'data', 'follow_up', 'view', '查看系统所有跟进记录', 644, 1, NULL),

-- 订单管理权限
('order:create', '创建订单', 'operation', 'order', 'create', '允许创建新订单', 710, 1, NULL),
('order:update', '编辑订单', 'operation', 'order', 'update', '允许编辑订单信息', 720, 1, NULL),
('order:delete', '删除订单', 'operation', 'order', 'delete', '允许删除订单', 730, 1, NULL),
('order:view:own', '查看个人订单', 'data', 'order', 'view', '仅查看自己创建的订单', 741, 1, NULL),
('order:view:department', '查看部门订单', 'data', 'order', 'view', '查看所在部门订单', 742, 1, NULL),
('order:view:sub_department', '查看部门及下级订单', 'data', 'order', 'view', '查看所在部门及下级部门订单', 743, 1, NULL),
('order:view:all', '查看所有订单', 'data', 'order', 'view', '查看系统所有订单', 744, 1, NULL),

-- 费用管理权限
('expense:create', '创建费用', 'operation', 'expense', 'create', '允许创建新费用记录', 810, 1, NULL),
('expense:update', '编辑费用', 'operation', 'expense', 'update', '允许编辑费用记录', 820, 1, NULL),
('expense:delete', '删除费用', 'operation', 'expense', 'delete', '允许删除费用记录', 830, 1, NULL),
('expense:view:own', '查看个人费用', 'data', 'expense', 'view', '仅查看自己创建的费用记录', 841, 1, NULL),
('expense:view:department', '查看部门费用', 'data', 'expense', 'view', '查看所在部门费用记录', 842, 1, NULL),
('expense:view:sub_department', '查看部门及下级费用', 'data', 'expense', 'view', '查看所在部门及下级部门费用记录', 843, 1, NULL),
('expense:view:all', '查看所有费用', 'data', 'expense', 'view', '查看系统所有费用记录', 844, 1, NULL),

-- 文件管理权限
('file:upload', '上传文件', 'operation', 'file', 'upload', '允许上传文件', 910, 1, NULL),
('file:delete', '删除文件', 'operation', 'file', 'delete', '允许删除文件', 930, 1, NULL),
('file:view:own', '查看个人文件', 'data', 'file', 'view', '仅查看自己上传的文件', 941, 1, NULL),
('file:view:department', '查看部门文件', 'data', 'file', 'view', '查看所在部门文件', 942, 1, NULL),
('file:view:sub_department', '查看部门及下级文件', 'data', 'file', 'view', '查看所在部门及下级部门文件', 943, 1, NULL),
('file:view:all', '查看所有文件', 'data', 'file', 'view', '查看系统所有文件', 944, 1, NULL);

3. 创建系统默认角色

-- 超级管理员角色
INSERT INTO role (name, description, permissions, created_at, updated_at) VALUES
('super_admin', '超级管理员，拥有系统所有权限', '["system:user:create","system:user:update","system:user:delete","system:user:view:all","system:role:create","system:role:update","system:role:delete","system:role:view","system:department:create","system:department:update","system:department:delete","system:department:view","client:create","client:update","client:delete","client:view:all","client:assign","client:transfer","contract:create","contract:update","contract:delete","contract:view:all","contract:approve","contract:renew","follow_up:create","follow_up:update","follow_up:delete","follow_up:view:all","order:create","order:update","order:delete","order:view:all","expense:create","expense:update","expense:delete","expense:view:all","file:upload","file:delete","file:view:all"]', NOW(), NOW()),

-- 系统管理员角色
('admin', '系统管理员，管理用户、角色、部门等', '["system:user:create","system:user:update","system:user:delete","system:user:view:all","system:role:create","system:role:update","system:role:delete","system:role:view","system:department:create","system:department:update","system:department:delete","system:department:view"]', NOW(), NOW()),

-- 部门经理角色
('department_manager', '部门经理，管理部门内所有数据', '["system:user:create","system:user:update","system:user:view:department","system:department:view","client:create","client:update","client:delete","client:view:department","client:assign","contract:create","contract:update","contract:delete","contract:view:department","contract:approve","follow_up:create","follow_up:update","follow_up:delete","follow_up:view:department","order:create","order:update","order:delete","order:view:department","expense:create","expense:update","expense:delete","expense:view:department","file:upload","file:delete","file:view:department"]', NOW(), NOW()),

-- 销售经理角色
('sales_manager', '销售经理，管理客户和销售数据', '["client:create","client:update","client:delete","client:view:sub_department","client:assign","contract:create","contract:update","contract:delete","contract:view:sub_department","contract:approve","follow_up:create","follow_up:update","follow_up:delete","follow_up:view:sub_department","order:create","order:update","order:delete","order:view:sub_department","expense:create","expense:update","expense:delete","expense:view:sub_department","file:upload","file:view:sub_department"]', NOW(), NOW()),

-- 销售人员角色
('sales_person', '销售人员，管理个人客户和数据', '["client:create","client:update","client:view:own","contract:create","contract:update","contract:view:own","follow_up:create","follow_up:update","follow_up:view:own","order:create","order:update","order:view:own","expense:create","expense:update","expense:view:own","file:upload","file:view:own"]', NOW(), NOW()),

-- 财务角色
('accountant', '财务人员，管理费用和财务数据', '["contract:view:all","order:view:all","expense:create","expense:update","expense:delete","expense:view:all","file:upload","file:view:all"]', NOW(), NOW()),

-- 合同管理员角色
('contract_admin', '合同管理员，管理合同和续签', '["contract:create","contract:update","contract:delete","contract:view:all","contract:approve","contract:renew","file:upload","file:view:all"]', NOW(), NOW()),

-- 访客角色（只读权限）
('guest', '访客，只读权限', '["system:user:view:own","client:view:own","contract:view:own","follow_up:view:own","order:view:own","expense:view:own","file:view:own"]', NOW(), NOW());

4. 创建部门数据（示例）

-- 创建根部门（公司）
INSERT INTO department (name, parent_id, manager_id, sort_order, is_active, created_at, updated_at) VALUES
('总公司', NULL, NULL, 1, 1, NOW(), NOW());

-- 创建销售部
INSERT INTO department (name, parent_id, manager_id, sort_order, is_active, created_at, updated_at) VALUES
('销售部', 1, NULL, 10, 1, NOW(), NOW());

-- 创建财务部
INSERT INTO department (name, parent_id, manager_id, sort_order, is_active, created_at, updated_at) VALUES
('财务部', 1, NULL, 20, 1, NOW(), NOW());

-- 创建技术部
INSERT INTO department (name, parent_id, manager_id, sort_order, is_active, created_at, updated_at) VALUES
('技术部', 1, NULL, 30, 1, NOW(), NOW());

5. 创建超级管理员用户

-- 创建超级管理员用户（密码：admin123）
INSERT INTO users (username, password, name, nickname, email, phone, is_disabled, is_deleted, default_department_id, data_scope_type, created_at, updated_at) VALUES
('admin', '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '系统管理员', '超管', 'admin@company.com', '13800138000', 0, 0, 1, 'company', NOW(), NOW());

-- 关联超级管理员角色
INSERT INTO user_roles (user_id, role_id) VALUES
(1, 1);

6. 创建测试用户

-- 创建销售经理用户（密码：123456）
INSERT INTO users (username, password, name, nickname, email, phone, is_disabled, is_deleted, default_department_id, data_scope_type, created_at, updated_at) VALUES
('sales_manager', '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '销售经理', '销售经理', 'sales@company.com', '13800138001', 0, 0, 2, 'sub_department', NOW(), NOW());

-- 创建销售人员用户（密码：123456）
INSERT INTO users (username, password, name, nickname, email, phone, is_disabled, is_deleted, default_department_id, data_scope_type, created_at, updated_at) VALUES
('sales_person', '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '销售人员', '销售小李', 'sp@company.com', '13800138002', 0, 0, 2, 'personal', NOW(), NOW());

-- 创建财务用户（密码：123456）
INSERT INTO users (username, password, name, nickname, email, phone, is_disabled, is_deleted, default_department_id, data_scope_type, created_at, updated_at) VALUES
('accountant', '$2b$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '财务人员', '会计小张', 'finance@company.com', '13800138003', 0, 0, 3, 'company', NOW(), NOW());

-- 关联角色
INSERT INTO user_roles (user_id, role_id) VALUES
(2, 4), -- 销售经理
(3, 5), -- 销售人员
(4, 6); -- 财务人员

验证查询

检查权限数据

-- 查看所有权限
SELECT p.id, p.code, p.name, p.type, p.module, p.action, p.description
FROM permission p
ORDER BY p.sort_order;

-- 查看角色权限
SELECT r.name as role_name, p.name as permission_name, p.code as permission_code
FROM role r
JOIN role_permission rp ON r.id = rp.role_id
JOIN permission p ON p.id = rp.permission_id
ORDER BY r.name, p.sort_order;

-- 查看用户角色
SELECT u.username, u.name, r.name as role_name
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN role r ON r.id = ur.role_id;

使用说明

1. 执行顺序

  1. 首先执行清除旧数据的脚本（仅首次部署时使用）
  2. 执行权限数据插入
  3. 执行角色数据插入
  4. 执行部门数据插入
  5. 执行用户数据插入
  6. 执行关联关系插入

2. 密码说明

  • 所有测试用户密码统一为：123456
  • 超级管理员密码为：admin123
  • 密码使用bcrypt加密，实际部署时请修改安全密码

3. 权限说明

  • 超级管理员(super_admin)：拥有系统所有权限
  • 系统管理员(admin)：管理用户、角色、部门等
  • 部门经理(department_manager)：管理部门内所有数据
  • 销售经理(sales_manager)：管理销售相关数据
  • 销售人员(sales_person)：仅管理个人数据
  • 财务(accountant)：管理财务相关数据
  • 合同管理员(contract_admin)：管理合同相关数据
  • 访客(guest)：只读权限

4. 数据范围说明

  • PERSONAL：仅个人数据
  • DEPARTMENT：仅所在部门数据
  • SUB_DEPARTMENT：所在部门及下级部门数据
  • COMPANY：全公司数据
  • CUSTOM：自定义范围数据