AiDOPWarehouse/doc/_grant_uat_menus.py

# -*- coding: utf-8 -*-
"""一次性：为 UAT 账号角色补授 数据对接服务+智慧诊断 菜单（sys_role_menu 插 10 条，可逆）。

用法：
  python _grant_uat_menus.py check   # 只读检查表结构与现状
  python _grant_uat_menus.py grant   # 执行授权（幂等：已存在的跳过）
  python _grant_uat_menus.py revoke  # 撤销本次授权
"""
from __future__ import annotations

import sys

import pymysql

CONN = dict(host="123.60.180.165", port=3306, user="aidopremote", password="1234567890aiDOP#",
            database="aidopdev", charset="utf8mb4", connect_timeout=15)

ROLE_ID = 838258976469061  # UAT业务操作员（UATAdminA 挂载角色）
MENU_IDS = [
    1320990000400,  # 数据中台管理（数据对接服务分组）
    1320990000401,  # 数据中台工作台
    1320990000402,  # 数据地图
    1320990000403,  # 数据源管理
    1320990000404,  # 同步配置中心
    1320990000405,  # 数据任务日志
    1320990000406,  # MDP运行监控
    1320990000407,  # 业务动作日志
    1320990000408,  # 出站回写队列
    1320990000201,  # 智慧诊断
]
# 本次插入的记录 Id 段（撤销时按此范围 + RoleId 精确删除）
ID_BASE = 8382990000400


def main():
    mode = sys.argv[1] if len(sys.argv) > 1 else "check"
    conn = pymysql.connect(**CONN)
    try:
        cur = conn.cursor()
        cur.execute("SHOW COLUMNS FROM SysRoleMenu")
        cols = [(r[0], r[1], r[2], r[4]) for r in cur.fetchall()]
        print("COLUMNS", cols)
        id_col, role_col, menu_col = cols[0][0], None, None
        for name, *_ in cols:
            low = name.lower()
            if low == "roleid":
                role_col = name
            elif low == "menuid":
                menu_col = name
        print("USE COLS", id_col, role_col, menu_col)

        marks = ",".join(str(m) for m in MENU_IDS)
        cur.execute(
            f"SELECT {menu_col} FROM SysRoleMenu WHERE {role_col}=%s AND {menu_col} IN ({marks})",
            (ROLE_ID,),
        )
        existing = {r[0] for r in cur.fetchall()}
        print("EXISTING", sorted(existing))

        if mode == "grant":
            todo = [m for m in MENU_IDS if m not in existing]
            for i, mid in enumerate(todo):
                cur.execute(
                    f"INSERT INTO SysRoleMenu ({id_col}, {role_col}, {menu_col}) VALUES (%s, %s, %s)",
                    (ID_BASE + i, ROLE_ID, mid),
                )
            conn.commit()
            print("GRANTED", todo)
        elif mode == "revoke":
            cur.execute(
                f"DELETE FROM SysRoleMenu WHERE {role_col}=%s AND {id_col} BETWEEN %s AND %s",
                (ROLE_ID, ID_BASE, ID_BASE + 99),
            )
            conn.commit()
            print("REVOKED rows:", cur.rowcount)
        else:
            print("CHECK ONLY")
    finally:
        conn.close()


if __name__ == "__main__":
    main()