平时工作使用jira&confluence挺多的,忽然有一天,安全来检查,领导要求查出confluence所有空间关联的组及活跃的人员域账号,研究了一两个小时,写出来了,特此记录!
select sp.SPACENAME,sp.SPACETYPE,sms.PERMGROUPNAME,cu.lower_display_name
from spacepermissions as sms
join spaces as sp on sp.SPACEID = sms.SPACEID
join cwd_group as cg on sms.PERMGROUPNAME = cg.group_name
join cwd_membership as cm on cm.parent_id = cg.id
join cwd_user as cu on cu.id = cm.child_user_id
where sp.SPACETYPE = "global"
and cu.active = "T";