1、查询出测试环境按钮授权角色
SELECT
t1.subject_id AS "授权主体【角色】" ,
t1.object_id AS "授权客体【按钮】"
FROM
iplat62.XS_AUTHORIZATION t1
LEFT JOIN iplat62.XS_USER_GROUP t2 ON
t1.subject_id = t2.ID
LEFT JOIN iplat62.XS_USER t3 ON
t1.subject_id = t3.USER_ID
LEFT JOIN iplat62.XS_RESOURCE_GROUP t4 ON
t1.subject_id = t4.ID
LEFT JOIN iplat62.XS_RESOURCE t5 ON
t1.subject_id = t5.ID
LEFT JOIN iplat62.XS_RESOURCE_GROUP t6 ON
t1.object_id = t6.ID
LEFT JOIN iplat62.XS_RESOURCE t7 ON
t1.object_id = t7.ID
WHERE
1 = 1
AND t1.operation_type != 'PROCESS'
AND t1.subject_type = 'USER_GROUP'
AND (t6.resource_group_cname LIKE ('%AQSW0201.SUBMIT%')
OR t7.resource_ename LIKE ('%AQSW0201.SUBMIT%'))
AND t1.operation_type = 'ACCESS'
AND (t6.resource_group_ename = 'AQSW0201.SUBMIT'
OR t7.resource_ename = 'AQSW0201.SUBMIT')
2、使用上述语句查询出来的角色id使用excel拼凑插入语句;
="INSERT INTO iplat62.XS_AUTHORIZATION (SUBJECT_ID, SUBJECT_TYPE,OBJECT_ID,OBJECT_TYPE,OPERATION_TYPE,REC_CREATOR,REC_CREATE_TIME,REC_REVISOR,REC_REVISE_TIME,ARCHIVE_FLAG,SORT_INDEX) VALUES ('"&A2&"','USER_GROUP','"&B2&"','RESOURCE','ACCESS','admin','20220713175200',' ',' ','0',0)"
切记sql里面不能有多余空格/字符,否则会影响excel函数拼凑;
3、效果如下图;
4、变换第一条中的【按钮名字】查询出对应按钮id和角色id依次完成拼凑,执行sql即可。
AQSW0201.SUBMIT(页面号.按钮英文名)