Active Directory의 그룹명 변경 시 연동된 Jira / Confluence의 권한 등에 반영되지 않는 현상

    • Jira / Confluence에 그룹명 변경 기능이 없기에 AD에서 그룹명 변경 후 Sync 시에 Jira / Confluence에는 새로운 그룹으로 생성됨
    • Atlassian Support의 공식적인 답변은 안정성을 위해 기본적으로 하나씩 직접 다시 권한을 부여하는 방법을 권장하나, 큰 인스턴스에서 현실적으로 불가능하기에 Staging에서 테스트 및 DB백업 후 다음 쿼리작업을 실행

      Confluence
      UPDATE spacepermissions
      SET permgroupname = '<New Group Name>'
      WHERE permgroupname IN ('<Old Group Name>')
      AND spaceid IS NULL;
       
       
      UPDATE spacepermissions
      SET permgroupname = '<New Group Name>'
      WHERE permgroupname IN ('<Old Group Name>')
      AND spaceid IS NOT NULL;
       
       
      UPDATE content_perm
      SET groupname = '<New Group Name>'
      WHERE groupname IN ('<Old Group Name>');

      Jira Postgresql
      UPDATE cwd_group SET group_name=REPLACE(group_name,"<old-group-name>","<new-group-name>") WHERE INSTR(group_name, "<old-group-name>") > 0
      UPDATE cwd_group SET lower_group_name=REPLACE(lower_group_name,"<old-group-name>","<new-group-name>") WHERE INSTR(lower_group_name, "<old-group-name>") > 0
      UPDATE cwd_membership SET parent_name=REPLACE(parent_name,"<old-group-name>","<new-group-name>") WHERE INSTR(parent_name, "<old-group-name>") > 0
      UPDATE cwd_membership SET lower_parent_name=REPLACE(lower_parent_name,"<old-group-name>","<new-group-name>") WHERE INSTR(lower_parent_name, "<old-group-name>") > 0
      UPDATE filtersubscription SET groupname=REPLACE(groupname,"<old-group-name>","<new-group-name>") WHERE INSTR(groupname, "<old-group-name>") > 0
      UPDATE jiraaction SET actionlevel=REPLACE(actionlevel,"<old-group-name>","<new-group-name>") WHERE INSTR(actionlevel, "<old-group-name>") > 0
      UPDATE notification SET notif_parameter=REPLACE(notif_parameter,"<old-group-name>","<new-group-name>") WHERE INSTR(notif_parameter, "<old-group-name>") > 0
      UPDATE projectroleactor SET ROLETYPEPARAMETER=REPLACE(ROLETYPEPARAMETER,"<old-group-name>","<new-group-name>") WHERE INSTR(ROLETYPEPARAMETER, "<old-group-name>") > 0
      UPDATE searchrequest SET groupname=REPLACE(groupname,"<old-group-name>","<new-group-name>") WHERE INSTR(groupname, "<old-group-name>") > 0
      UPDATE searchrequest SET reqcontent=REPLACE(reqcontent,"<old-group-name>","<new-group-name>") WHERE INSTR(reqcontent, "<old-group-name>") > 0
      UPDATE sharepermissions SET PARAM1=REPLACE(PARAM1,"<old-group-name>","<new-group-name>") WHERE INSTR(PARAM1, "<old-group-name>") > 0
      UPDATE worklog SET grouplevel=REPLACE(grouplevel,"<old-group-name>","<new-group-name>") WHERE INSTR(grouplevel, "<old-group-name>") > 0
      
      UPDATE globalpermissionentry gpe1
      SET group_id = 'NEW_GROUP'
      WHERE gpe1.group_id = 'OLD_GROUP'
      AND NOT EXISTS
      (
          SELECT *
          FROM globalpermissionentry gpe2
          WHERE gpe2.permission = gpe1.permission
          AND gpe2.group_id = 'NEW_GROUP'
      )
      
      UPDATE licenserolesgroup lrg1
      SET group_id = 'NEW_GROUP'
      WHERE lrg1.group_id = 'OLD_GROUP'
      AND NOT EXISTS
      (
          SELECT *
          FROM licenserolesgroup lrg2
          WHERE lrg2.license_role_name = lrg1.license_role_name
          AND lrg2.group_id = 'NEW_GROUP'
      )
      
      UPDATE projectroleactor pra1
      SET roletypeparameter = 'NEW_GROUP'
      WHERE pra1.roletypeparameter = 'OLD_GROUP'
      AND pra1.roletype = 'atlassian-group-role-actor'
      AND NOT EXISTS
      (
          SELECT *
          FROM projectroleactor pra2
          WHERE pra2.pid = pra1.pid
          AND pra2.projectroleid = pra1.projectroleid
          AND pra2.roletypeparameter = 'NEW_GROUP'
          AND pra2.roletype = 'atlassian-group-role-actor'
      )
      
      UPDATE schemeissuesecurities sis1
      SET sec_parameter = 'NEW_GROUP'
      WHERE sis1.sec_parameter = 'OLD_GROUP'
      AND sis1.sec_type = 'group'
      AND NOT EXISTS
      (
          SELECT *
          FROM schemeissuesecurities sis2
          WHERE sis2.scheme = sis1.scheme
          AND sis2.security = sis1.security
          AND sis2.sec_parameter = 'NEW_GROUP'
          AND sis2.sec_type = 'group'
      )
      
      UPDATE schemepermissions sp1
      SET perm_parameter = 'NEW_GROUP'
      WHERE sp1.perm_parameter = 'OLD_GROUP'
      AND sp1.perm_type = 'group'
      AND NOT EXISTS
      (
          SELECT *
          FROM schemepermissions sp2
          WHERE sp2.scheme = sp1.scheme
          AND (sp2.permission = sp1.permission OR sp2.permission_key = sp1.permission_key)
          AND sp2.perm_parameter = 'NEW_GROUP'
          AND sp2.perm_type = 'group'
      )
      
      UPDATE sharepermissions sp1
      SET param1 = 'NEW_GROUP'
      WHERE sp1.param1 = 'OLD_GROUP'
      AND sp1.sharetype = 'group'
      AND NOT EXISTS
      (
          SELECT *
          FROM sharepermissions sp2
          WHERE sp2.entityid = sp1.entityid
          AND sp2.entitytype = sp1.entitytype
          AND sp2.param1 = 'NEW_GROUP'
          AND sp2.sharetype = 'group'
      )
      
      SELECT *
      FROM schemeissuesecurities
      WHERE sec_type = 'groupCF'
      
      SELECT *
      FROM schemepermissions
      WHERE perm_type = 'groupCF'
      
      select * from customfieldvalue where stringvalue like '%<groupname>%';
      select * from customfieldoption where customvalue like '%<groupname>%';
      select * from customfield where id = '<customfield>';

      Jira Mysql
      UPDATE cwd_group SET group_name=REPLACE(group_name,"<Old Group Name>","<New Group Name>") WHERE INSTR(group_name, "<Old Group Name>") > 0
      UPDATE cwd_group SET lower_group_name=REPLACE(lower_group_name,"<Old Group Name>","<New Group Name>") WHERE INSTR(lower_group_name, "<Old Group Name>") > 0
      UPDATE cwd_membership SET parent_name=REPLACE(parent_name,"<Old Group Name>","<New Group Name>") WHERE INSTR(parent_name, "<Old Group Name>") > 0
      UPDATE cwd_membership SET lower_parent_name=REPLACE(lower_parent_name,"<Old Group Name>","<New Group Name>") WHERE INSTR(lower_parent_name, "<Old Group Name>") > 0
      UPDATE filtersubscription SET groupname=REPLACE(groupname,"<Old Group Name>","<New Group Name>") WHERE INSTR(groupname, "<Old Group Name>") > 0
      UPDATE jiraaction SET actionlevel=REPLACE(actionlevel,"<Old Group Name>","<New Group Name>") WHERE INSTR(actionlevel, "<Old Group Name>") > 0
      UPDATE notification SET notif_parameter=REPLACE(notif_parameter,"<Old Group Name>","<New Group Name>") WHERE INSTR(notif_parameter, "<Old Group Name>") > 0
      UPDATE projectroleactor SET ROLETYPEPARAMETER=REPLACE(ROLETYPEPARAMETER,"<Old Group Name>","<New Group Name>") WHERE INSTR(ROLETYPEPARAMETER, "<Old Group Name>") > 0
      UPDATE searchrequest SET groupname=REPLACE(groupname,"<Old Group Name>","<New Group Name>") WHERE INSTR(groupname, "<Old Group Name>") > 0
      UPDATE searchrequest SET reqcontent=REPLACE(reqcontent,"<Old Group Name>","<New Group Name>") WHERE INSTR(reqcontent, "<Old Group Name>") > 0
      UPDATE sharepermissions SET PARAM1=REPLACE(PARAM1,"<Old Group Name>","<New Group Name>") WHERE INSTR(PARAM1, "<Old Group Name>") > 0
      UPDATE worklog SET grouplevel=REPLACE(grouplevel,"<Old Group Name>","<New Group Name>") WHERE INSTR(grouplevel, "<Old Group Name>") > 0
       
       
      UPDATE globalpermissionentry gpe1
      SET gpe1.group_id = '<New Group Name>'
      WHERE gpe1.group_id = '<Old Group Name>'
      AND NOT EXISTS
      (select * from (
          SELECT *
          FROM globalpermissionentry gpe2
          WHERE gpe2.permission = (select permission from globalpermissionentry gpe3 where gpe3.group_id = '<Old Group Name>')
          AND gpe2.group_id = '<New Group Name>'
      ) as temp_table );
        
        
      UPDATE licenserolesgroup lrg1
      SET lrg1.group_id = '<New Group Name>'
      WHERE lrg1.group_id = '<Old Group Name>'
      AND NOT EXISTS
      (select * from (
          SELECT *
          FROM licenserolesgroup lrg2
          WHERE lrg2.license_role_name = (select license_role_name from licenserolesgroup lrg3 where lrg3.group_id = '<Old Group Name>')
          AND lrg2.group_id = '<New Group Name>'
      ) as temp_table );
        
        
      UPDATE projectroleactor pra1
      SET roletypeparameter = '<New Group Name>'
      WHERE pra1.roletypeparameter = '<Old Group Name>'
      AND pra1.roletype = 'atlassian-group-role-actor'
      AND NOT EXISTS
      (
      select *
      from (
      SELECT pra2.*
      FROM projectroleactor pra2
      Join projectroleactor pra3
      on pra2.pid = pra3.pid and pra2.projectroleid = pra3.projectroleid
      AND pra2.roletypeparameter = '<New Group Name>'AND pra2.roletype = 'atlassian-group-role-actor'
      )as aaa
      );
        
        
      UPDATE schemeissuesecurities sis1
      SET sec_parameter = '<New Group Name>'
      WHERE sis1.sec_parameter = '<Old Group Name>'
      AND sis1.sec_type = 'group'
      AND NOT EXISTS
      (
      SELECT *
      FROM (
      SELECT sis2.*
      FROM schemeissuesecurities sis2
      JOIN schemeissuesecurities sis3
      ON sis2.scheme = sis3.scheme
      AND sis2.security = sis3.security
      AND sis2.sec_parameter = '<New Group Name>'
      AND sis2.sec_type = 'group'
      ) as aaa
      );
        
        
      UPDATE schemepermissions sp1
      SET perm_parameter = '<New Group Name>'
      WHERE sp1.perm_parameter = '<Old Group Name>'
      AND sp1.perm_type = 'group'
      AND NOT EXISTS
      (
      SELECT *
      FROM (
      SELECT sp2.*
      FROM schemepermissions sp2
      JOIN schemepermissions sp3
      ON sp2.scheme = sp3.scheme
      AND sp2.permission = sp3.permission
      AND sp2.perm_parameter = '<New Group Name>'
      AND sp2.perm_type = 'group'
      ) as aaa
      );
        
        
      UPDATE sharepermissions sp1
      SET param1 = '<New Group Name>'
      WHERE sp1.param1 = '<Old Group Name>'
      AND sp1.sharetype = 'group'
      AND NOT EXISTS
      (
      SELECT *
      FROM (
      SELECT sp2.*
      FROM sharepermissions sp2
      JOIN sharepermissions sp3
      ON sp2.entityid = sp3.entityid
      AND sp2.entitytype = sp3.entitytype
      AND sp2.param1 = '<New Group Name>'
      AND sp2.sharetype = 'group'
      ) as aaa
      );

    • Jira의 View All Board 클릭 시 에러 해결을 위해 추가로 다음의 작업이 필요

      select distinct `KEY` from AO_60DB71_BOARDADMINS where TYPE = 'GROUP' and `KEY` not in (select group_name from cwd_group);
      select NAME, ID  from AO_60DB71_RAPIDVIEW WHERE ID IN (select RAPID_VIEW_ID from AO_60DB71_BOARDADMINS WHERE `KEY` = '<1번 쿼리에서 출력된 그룹명>');
      http://<Jira BaseURL>/secure/RapidView.jspa?rapidView=<1번 쿼리에서 출력된 ID>&tab=filter
      
      Administrators 클릭 및 edit으로 해당 그룹 삭제

      https://jira.atlassian.com/browse/JSWSERVER-15924

Jira 업그레이드 후, Health Check Indexing에러 및 Re-index 불가능한 현상

Jira의 Log에 다음과 같은 Warning이 많이 발생하는 경우

kjeon's profile image

kjeon

2018-12-26

Read more posts by this author