---
notion-id: 1e8ca4d7-cb7b-80c5-a27c-f5855ec4b47c
---
重要提醒：
在执行脚本之前，必须将人员ID信息录入到总团队信息→分团队信息部署好权限
[]()
```sql
-- 创建备份表
CREATE TABLE wiz_kb_user_role_backup AS SELECT * FROM wiz_kb_user_role;
```
- 备份数据表

```sql
-- 检查将要添加的数据
SELECT 
    t.KB_GUID as '团队ID',
    u.USER_GUID as '用户ID'
FROM (
    SELECT DISTINCT KB_GUID FROM wiz_kb_user_role
) t
CROSS JOIN (
    SELECT DISTINCT USER_GUID FROM wiz_kb_user_role
) u
WHERE NOT EXISTS (
    SELECT 1 
    FROM wiz_kb_user_role e
    WHERE e.KB_GUID = t.KB_GUID 
    AND e.USER_GUID = u.USER_GUID
)
ORDER BY t.KB_GUID, u.USER_GUID;
```
- 只用于查看将要添加的数据
- 不会修改任何数据
- 安全可靠
- 建议先执行这个检查数据

```sql
USE wizasent;

-- 创建临时表来存储所有用户
CREATE TEMPORARY TABLE temp_all_users AS 
SELECT DISTINCT USER_GUID FROM wiz_kb_user_role;

-- 创建临时表来存储所有团队
CREATE TEMPORARY TABLE temp_all_teams AS 
SELECT DISTINCT KB_GUID FROM wiz_kb_user_role;

-- 创建临时表来存储已存在的组合
CREATE TEMPORARY TABLE temp_existing_combinations AS 
SELECT DISTINCT KB_GUID, USER_GUID FROM wiz_kb_user_role;

-- 插入新组合
INSERT INTO wiz_kb_user_role (
    KB_GUID,
    USER_GUID,
    USER_GROUP,
    DT_CREATED,
    ROLE_STATE,
    ROLE_NOTE
)
SELECT 
    t.KB_GUID,
    u.USER_GUID,
    1000,  -- 设置权限为读者
    NOW(), -- 当前时间
    1,     -- 状态为1
    'admin accept'  -- 备注
FROM temp_all_teams t
CROSS JOIN temp_all_users u
WHERE NOT EXISTS (
    SELECT 1 
    FROM temp_existing_combinations e
    WHERE e.KB_GUID = t.KB_GUID 
    AND e.USER_GUID = u.USER_GUID
);

-- 清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_all_users;
DROP TEMPORARY TABLE IF EXISTS temp_all_teams;
DROP TEMPORARY TABLE IF EXISTS temp_existing_combinations;
```
- 实际执行数据添加操作
- 使用临时表提高效率
- 最后会清理所有临时表
- 建议在检查数据无误后执行