use dms;
-- 给表op_permissions增加字段module,service
alter table op_permissions add column module varchar(255);
alter table op_permissions add column service varchar(255);
-- 设置一些权限的service值为sqle
UPDATE op_permissions SET service = 'sqle' WHERE uid IN ('700001', '700002', '700003', '700004', '700006', '700007', '700008', '700009', '700010', '700011', '700012', '700013', '700014', '700015', '700016', '700017', '700018');
-- 给700005权限更名
UPDATE op_permissions SET service = 'dms', module = '账号管理', name = '账号管理', range_type = 'project' WHERE uid = '700005';
-- 创建表member_group_op_permissions,member_op_permissions
create table member_group_op_permissions
(
 member_group_uid varchar(32) not null,
 op_permission_uid varchar(32) not null,
 primary key (member_group_uid, op_permission_uid)
);
create table member_op_permissions
(
 member_uid varchar(32) not null,
 op_permission_uid varchar(32) not null,
 primary key (member_uid, op_permission_uid)
);
-- 某些被赋予了运维工程师角色的用户,需要将这些被添加为成员的用户,赋予账号管理的权限
INSERT INTO member_op_permissions(member_uid, op_permission_uid)
SELECT member_uid, '700005' from member_role_op_ranges member_roles
LEFT JOIN role_op_permissions role_permissions ON member_roles.role_uid = role_permissions.role_uid
LEFT JOIN op_permissions ON role_permissions.op_permission_uid = op_permissions.uid where member_roles.role_uid = '700405' and op_permissions.uid = '700005';
INSERT INTO member_group_op_permissions(member_group_uid, op_permission_uid)
SELECT member_group_uid, '700005' from member_group_role_op_ranges member_groups_roles
LEFT JOIN role_op_permissions role_permissions ON member_groups_roles.role_uid = role_permissions.role_uid
LEFT JOIN op_permissions ON role_permissions.op_permission_uid = op_permissions.uid where member_groups_roles.role_uid = '700405' and op_permissions.uid = '700005';
-- 去除运维工程师账号管理员的权限
DELETE FROM role_op_permissions where role_uid = '700405' and op_permission_uid = '700005';
-- 修改一些权限的名称
UPDATE op_permissions set name = '创建上线工单', module = 'SQL工单' WHERE uid = '700003';
UPDATE op_permissions set name = '审批上线工单', module = 'SQL工单' WHERE uid = '700004';
UPDATE op_permissions set name = '执行上线工单', module = 'SQL工单' WHERE uid = '700006';
UPDATE op_permissions set name = '查看所有工单', module = 'SQL工单' WHERE uid = '700007';
UPDATE op_permissions set name = '访问所有管控SQL', module = 'SQL管控' WHERE uid = '700008';
UPDATE op_permissions set name = '配置SQL管控', module = 'SQL管控' WHERE uid = '700009';
UPDATE op_permissions set name = 'SQL工作台操作权限', module = 'SQL工作台' WHERE uid = '700010';
UPDATE op_permissions set name = '审批导出工单', module = '数据导出' WHERE uid = '700011';
UPDATE op_permissions set name = '创建导出工单', module = '数据导出' WHERE uid = '700012';
UPDATE op_permissions SET module = 'SQL优化' WHERE uid = '700013';
UPDATE op_permissions SET module = 'SQL优化' WHERE uid = '700014';
UPDATE op_permissions set name = '流水线增删改', module = 'CI/CD集成' WHERE uid = '700015';
-- 插入新的权限
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700019', now(), now(), '查看所有操作记录', 'SQL工作台', '', 'db_service', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700020', now(), now(), '查看所有导出任务', '数据导出', '', 'db_service', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700021', now(), now(), '查看所有快捷审核记录', '快捷审核', '', 'db_service', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700022', now(), now(), '查看所有IDE审核记录', 'IDE审核', '', 'db_service', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700024', now(), now(), '查看他人创建的版本记录', '版本管理', '', 'db_service', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700025', now(), now(), '配置版本', '版本管理', '', 'db_service', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700026', now(), now(), '查看所有流水线', 'CI/CD集成', '', 'db_service', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700028', now(), now(), '管理项目数据源', '数据源管理', '', 'project', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700029', now(), now(), '管理审核规则模版', '审核规则模板', '', 'project', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700030', now(), now(), '管理审批流程模版', '审批流模板管理', '', 'project', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700031', now(), now(), '管理成员与权限', '成员与权限', '', 'project', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700032', now(), now(), '管理推送规则', '推送规则', '', 'project', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700033', now(), now(), '审核SQL例外', '审核SQL例外', '', 'project', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700034', now(), now(), '管控SQL例外', '管控SQL例外', '', 'project', 'sqle');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700035', now(), now(), '角色管理权限', '角色管理', '', 'project', 'dms');
INSERT INTO op_permissions (uid, created_at, updated_at, name, module, description, range_type, service) VALUES ('700036', now(), now(), '脱敏规则配置权限', '脱敏规则', '', 'project', 'dms');