User Tools

Site Tools


auth_synchronization

타 시스템에 설정된 권한을 TOGA에 동기화 사례

개념도

권한_동기화.pptx

실제 쿼리문

<sxh sql>

CREATE OR REPLACE PROCEDURE MIS_USR.SP_TM_AUTH_SYNCHRONIZATION (

	r_ERRCODE          OUT CHAR,            -- 에러코드
	r_ERRMSG           OUT VARCHAR2         -- 에러메시지

)

/*

Procedure ID   : SP_TM_AUTH_SYNCHRONIZATION
Procedure Name : 권한 동기화
Procedure Desc : 통합시스템과 TOGA 권한을 동기화한다.

*/

AS

  max_acl number; -- acl max number;
max_ace number; -- ace max number;
BEGIN
	SELECT NVL(CAST(MAX(SUBSTR(ACL_ID,2,5)) AS NUMERIC),0) INTO max_acl FROM KONA_ACL;
  1. - 1. 등록된 모든 메뉴에 접근제어리스트(ACCESS CONTROL LIST)를 부여한다.

BEGIN

		DBMS_OUTPUT.PUT_LINE('INSERT OR UPDATE KONA_ACL Start');
		MERGE INTO KONA_ACL TARGET
		USING
		(
			SELECT
				ROWNUM AS RNUM
				,MENU_ID
				,'com.eyeq.kona.model.portal.PortalMenu' AS OBJ_TYPE
			FROM
				KONA_PORTAL_MENU
			WHERE
				PORTAL_ID='P0002' -- 포털 2에만 적용
		) SRC
		ON
		(
			SRC.MENU_ID = TARGET.OBJ_ID
			AND SRC.OBJ_TYPE=TARGET.OBJ_TYPE
		)
		WHEN MATCHED THEN
			UPDATE SET
				INHERIT='N'
		WHEN NOT MATCHED THEN
			INSERT (ACL_ID, OBJ_ID, OBJ_TYPE, INHERIT, OWNER, FLAG)
			VALUES
			(
				CONCAT('A',LPAD(max_acl + SRC.RNUM ,5,'0'))
				,SRC.MENU_ID
				,'com.eyeq.kona.model.portal.PortalMenu'
				,'N'
				,'admin'
				,'CONFIRM'
			);
		r_ERRMSG  := '[정상종료] INSERT OR UPDATE KONA_ACL 처리건수 - ' || SQL%ROWCOUNT || '건 ';
		COMMIT;
		DBMS_OUTPUT.PUT_LINE('INSERT OR UPDATE KONA_ACL End');
		EXCEPTION WHEN OTHERS THEN
			r_ERRCODE := 'E';
			r_ERRMSG := '[이상종료 - INSERT OR UPDATE KONA_ACL] ' || SQLErrm;
			GOTO CLOSE_ROUTINE;
	END;
	BEGIN
		/*
		2-1. SSG의 그룹정보를 TOGA 그룹정보 테이블에 이관한다.
		*/
		DBMS_OUTPUT.PUT_LINE('INSERT OR UPDATE KONA_GROUP Start');
		MERGE INTO KONA_GROUP TARGET
		USING
		(
			SELECT
				A.USER_GROUP_CD
				,A.USER_GROUP_NAME
				,NULL AS GROUP_DESC
			FROM
				TI_USER_GROUP A
				INNER JOIN
				TI_USER B
				ON
				A.USER_GROUP_CD = B.USER_GROUP_CD
			GROUP BY
				A.USER_GROUP_CD
				,A.USER_GROUP_NAME
		) SRC
		ON
		(
			SRC.USER_GROUP_CD = TARGET.GROUP_ID
		)
		WHEN MATCHED THEN
			UPDATE SET
				TARGET.GROUP_NAME = SRC.USER_GROUP_NAME
		WHEN NOT MATCHED THEN
			INSERT (GROUP_ID, GROUP_NAME, FLAG)
			VALUES
			(
				SRC.USER_GROUP_CD
				,SRC.USER_GROUP_NAME
				,'CONFIRM'
			);
		r_ERRMSG  := '[정상종료] INSERT OR UPDATE KONA_GROUP 처리건수 - ' || SQL%ROWCOUNT || '건 ';
		COMMIT;
		DBMS_OUTPUT.PUT_LINE('INSERT OR UPDATE KONA_GROUP End');
		EXCEPTION WHEN OTHERS THEN
			r_ERRCODE := 'E';
			r_ERRMSG := '[이상종료 - INSERT OR UPDATE KONA_GROUP] ' || SQLErrm;
			GOTO CLOSE_ROUTINE;
	END;
	BEGIN
		/*
		2-2. SSG의 그룹별 사용자 정보를 TOGA 그룹별 사용자 테이블에 이관한다.
		*/
		MERGE INTO KONA_GROUP_USER TARGET
		USING
		(
			SELECT
				B.USER_ID
				,B.USER_GROUP_CD
			FROM
				TI_USER_GROUP A
				INNER JOIN
				TI_USER B
				ON
				A.USER_GROUP_CD = B.USER_GROUP_CD
			GROUP BY
				B.USER_ID
				,B.USER_GROUP_CD
		) SRC
		ON
		(
			SRC.USER_ID = TARGET.USER_ID
		)
		WHEN MATCHED THEN
			UPDATE SET
				TARGET.GROUP_ID= SRC.USER_GROUP_CD
		WHEN NOT MATCHED THEN
			INSERT (USER_ID, GROUP_ID, FLAG)
			VALUES
			(
				SRC.USER_ID
				,SRC.USER_GROUP_CD
                  ,'CONFIRM'
              );
          r_ERRMSG  := '[정상종료] INSERT OR UPDATE KONA_GROUP_USER 처리건수 - ' || SQL%ROWCOUNT || '건 ';
          COMMIT;
          DBMS_OUTPUT.PUT_LINE('INSERT OR UPDATE KONA_GROUP_USER End');
          EXCEPTION WHEN OTHERS THEN
              r_ERRCODE := 'E';
              r_ERRMSG := '[이상종료 - INSERT OR UPDATE KONA_GROUP_USER] ' || SQLErrm;
              GOTO CLOSE_ROUTINE;
      END;
      /*
          3. 각 그룹별 권한 삽입. 스케쥴로 동기화된 데이터를 제거 후 다시 삽입
      */
      BEGIN
          DELETE FROM KONA_ACE WHERE FLAG='CONFIRM';
          r_ERRMSG  := '[정상종료] DELETE FROM KONA_ACE WHERE FLAG=CONFIRM 처리건수 - ' || SQL%ROWCOUNT || '건 ';
          COMMIT;
          DBMS_OUTPUT.PUT_LINE('DELETE FROM KONA_ACE WHERE FLAG=CONFIRM End');
          EXCEPTION WHEN OTHERS THEN
              r_ERRCODE := 'E';
              r_ERRMSG := '[이상종료 - DELETE FROM KONA_ACE WHERE FLAG=CONFIRM  ' || SQLErrm;
              GOTO CLOSE_ROUTINE;
      END;
      BEGIN
          DELETE FROM KONA_ACE WHERE FLAG='MENU';
          r_ERRMSG  := '[정상종료] DELETE FROM KONA_ACE WHERE FLAG=MENU 처리건수 - ' || SQL%ROWCOUNT || '건 ';
          COMMIT;
          DBMS_OUTPUT.PUT_LINE('DELETE FROM KONA_ACE WHERE FLAG=MENU End');
          EXCEPTION WHEN OTHERS THEN
              r_ERRCODE := 'E';
              r_ERRMSG := '[이상종료 - DELETE FROM KONA_ACE WHERE FLAG=MENU ' || SQLErrm;
              GOTO CLOSE_ROUTINE;
      END;
      BEGIN
          DELETE FROM KONA_ACE WHERE FLAG='R_CONFIRM';
          r_ERRMSG  := '[정상종료] DELETE FROM KONA_ACE WHERE FLAG=R_CONFIRM 처리건수 - ' || SQL%ROWCOUNT || '건 ';
          COMMIT;
          DBMS_OUTPUT.PUT_LINE('DELETE FROM KONA_ACE WHERE FLAG=R_CONFIRM End');
          EXCEPTION WHEN OTHERS THEN
              r_ERRCODE := 'E';
              r_ERRMSG := '[이상종료 - DELETE FROM KONA_ACE WHERE FLAG=R_CONFIRM  ' || SQLErrm;
              GOTO CLOSE_ROUTINE;
      END;
      SELECT NVL(CAST(MAX(SUBSTR(ACE_ID,2,5)) AS NUMERIC),0) INTO max_ace FROM KONA_ACE;
  1. - 4. 전체 메뉴에 ROLE_SYSTEM_ADMIN 권한을 부여

BEGIN

          DBMS_OUTPUT.PUT_LINE('INSERT OR UPDATE KONA_ACE = ROLE_SYSTEM_ADMIN Start');
          MERGE INTO KONA_ACE TARGET
              USING
              (
              SELECT
              ROWNUM AS RNUM
                  ,ACL_ID
                  ,'ROLE_SYSTEM_ADMIN' SID
                  ,'R' SID_TYPE
              FROM KONA_ACL
              WHERE OBJ_ID IN (SELECT MENU_ID FROM KONA_PORTAL_MENU WHERE PORTAL_ID='P0002') -- 포털2번만
              ) SRC
              ON
              (
              SRC.ACL_ID = TARGET.ACL_ID
              AND SRC.SID = TARGET.SID
              AND SRC.SID_TYPE = TARGET.SID_TYPE
              )
              WHEN MATCHED THEN
              UPDATE SET
                  GRANT_MASK = 31    -- 모든 권한
              WHEN NOT MATCHED THEN
              INSERT( ACE_ID, SID, SID_TYPE, ACL_ID, AUD_SUC, AUD_FAIL, GRANT_MASK, REJECT_MASK,FLAG)
              VALUES
              (
                  CONCAT('E',LPAD(max_ace + SRC.RNUM ,5,'0'))
                  , 'ROLE_SYSTEM_ADMIN'
                  , 'R'
                  , SRC.ACL_ID
                  , 'N', 'N', 31, 0
                  --, (SELECT NVL(TO_NUMBER(MAX(SEQ)),0) FROM KONA_ACE WHERE ACL_ID =SRC.ACL_ID) + TO_NUMBER(ROW_NUMBER() OVER(PARTITION BY SRC.ACL_ID ORDER BY SRC.SID)) AS SEQ
                  ,'R_CONFIRM'
              ) ;
          r_ERRMSG  := '[정상종료] INSERT OR UPDATE KONA_ACE = ROLE_SYSTEM_ADMIN 처리건수 - ' || SQL%ROWCOUNT || '건 ';
          COMMIT;
          DBMS_OUTPUT.PUT_LINE('INSERT OR UPDATE KONA_ACE = ROLE_SYSTEM_ADMIN End');
          EXCEPTION WHEN OTHERS THEN
              r_ERRCODE := 'E';
              r_ERRMSG := '[이상종료 - INSERT OR UPDATE KONA_ACE = ROLE_SYSTEM_ADMIN] ' || SQLErrm;
              GOTO CLOSE_ROUTINE;
      END;
  1. - 5. 사용자 그룹별 메뉴권한 입력

BEGIN

          DBMS_OUTPUT.PUT_LINE('INSERT KONA_ACE 사용자 그룹별 메뉴권한 입력 Start');
          INSERT INTO KONA_ACE
          SELECT
              CONCAT('E',LPAD(ROWNUM+(SELECT NVL(CAST(MAX(SUBSTR(ACE_ID,2,5)) AS NUMERIC), 0) FROM KONA_ACE),5,'0'))  AS ACE_ID
              ,A.USER_GROUP_CD AS SID
              ,'G' AS SID_TYPE
              ,D.ACL_ID
              ,'N' AS AUD_SUC
              ,'N' AS AUD_FAIL
              ,'1' AS GRANT_MASK
              ,'0' AS REJECT_MASK
              ,(SELECT NVL(TO_NUMBER(MAX(SEQ)),0) FROM KONA_ACE WHERE ACL_ID=D.ACL_ID) + TO_NUMBER(ROW_NUMBER() OVER(PARTITION BY D.ACL_ID ORDER BY A.USER_GROUP_CD)) AS SEQ
              ,'CONFIRM' AS FLAG
          FROM
              TI_USER_GROUP_PROGRAM A
              INNER JOIN
              KONA_REPORT B
              ON
              A.PROGRAM_ID=B.REPORT_KEY
              INNER JOIN
              KONA_PORTAL_MENU C
              ON
              B.REPORT_ID=C.MENU_URL
              INNER JOIN
              KONA_ACL D
              ON
              C.MENU_ID=D.OBJ_ID
              INNER JOIN
              KONA_GROUP E
              ON
              A.USER_GROUP_CD=E.GROUP_ID
          WHERE
              C.PORTAL_ID='P0002';
          r_ERRMSG  := '[정상종료] INSERT KONA_ACE 사용자 그룹별 메뉴권한 입력 처리건수 - ' || SQL%ROWCOUNT || '건 ';
          COMMIT;
          DBMS_OUTPUT.PUT_LINE('INSERT KONA_ACE 사용자 그룹별 메뉴권한 입력 End');
          EXCEPTION WHEN OTHERS THEN
              r_ERRCODE := 'E';
              r_ERRMSG := '[이상종료 - INSERT KONA_ACE 사용자 그룹별 메뉴권한 입력] ' || SQLErrm;
              GOTO CLOSE_ROUTINE;
      END;
  1. - 6. 사용자별 메뉴권한 입력

BEGIN

          DBMS_OUTPUT.PUT_LINE('INSERT KONA_ACE 사용자별 메뉴권한 입력 Start');
          INSERT INTO KONA_ACE
          SELECT
              CONCAT('E',LPAD(ROWNUM+(SELECT NVL(CAST(MAX(SUBSTR(ACE_ID,2,5)) AS NUMERIC), 0) FROM KONA_ACE),5,'0'))  AS ACE_ID
              ,A.USER_ID AS SID
              ,'P' AS SID_TYPE
              ,D.ACL_ID
              ,'N' AS AUD_SUC
              ,'N' AS AUD_FAIL
              ,'1' AS GRANT_MASK
              ,'0' AS REJECT_MASK
              ,(SELECT NVL(TO_NUMBER(MAX(SEQ)),0) FROM KONA_ACE WHERE ACL_ID=D.ACL_ID) + TO_NUMBER(ROW_NUMBER() OVER(PARTITION BY D.ACL_ID ORDER BY A.USER_ID)) AS SEQ
              ,'CONFIRM' AS FLAG
          FROM
              TI_USER_PROGRAM A
              INNER JOIN
              KONA_REPORT B
              ON
              A.PROGRAM_ID=B.REPORT_KEY
              INNER JOIN
              KONA_PORTAL_MENU C
              ON
              B.REPORT_ID=C.MENU_URL
              INNER JOIN
              KONA_ACL D
              ON
              C.MENU_ID=D.OBJ_ID
          WHERE
              C.PORTAL_ID='P0002';
          r_ERRMSG  := '[정상종료] INSERT KONA_ACE 사용자별 메뉴권한 입력 처리건수 - ' || SQL%ROWCOUNT || '건 ';
          COMMIT;
          DBMS_OUTPUT.PUT_LINE('INSERT KONA_ACE 사용자별 메뉴권한 입력 End');
          EXCEPTION WHEN OTHERS THEN
              r_ERRCODE := 'E';
              r_ERRMSG := '[이상종료 - INSERT KONA_ACE 사용자별 메뉴권한 입력] ' || SQLErrm;
              GOTO CLOSE_ROUTINE;
      END;
  1. - 7. 상위 메뉴(카테고리)에 대한 권한 입력

BEGIN

          DBMS_OUTPUT.PUT_LINE('INSERT KONA_ACE 상위 메뉴에 대한 권한 입력 Start');
  1. - 자식노드에 부여된 권한을 상위 메뉴에 모두 부여한다.

INSERT INTO KONA_ACE

          SELECT
              CONCAT('E',LPAD(ROWNUM+(SELECT NVL(CAST(MAX(SUBSTR(ACE_ID,2,5)) AS NUMERIC), 0) FROM KONA_ACE),5,'0')) AS ACE_ID
              ,A.SID
              ,A.SID_TYPE
              ,A.ACL_ID
              ,A.AUD_SUC
              ,A.AUD_FAIL
              ,A.GRANT_MASK
              ,A.REJECT_MASK
              ,(SELECT NVL(TO_NUMBER(MAX(SEQ)),0) FROM KONA_ACE WHERE ACL_ID =A.ACL_ID) + TO_NUMBER(ROW_NUMBER() OVER(PARTITION BY A.ACL_ID ORDER BY A.SID)) AS SEQ
              ,'MENU' AS FLAG
          FROM
              (
              SELECT distinct
                  A.SID
                  ,A.SID_TYPE
                  ,A.OBJ_NM
                  ,B.ACL_ID
                  ,'N' AS AUD_SUC
                  ,'N' AS AUD_FAIL
                  ,'1' AS GRANT_MASK
                  ,'0' AS REJECT_MASK
                  ,ROW_NUMBER() OVER(PARTITION BY B.ACL_ID ORDER BY B.ACL_ID) AS SEQ
              FROM
                  (SELECT DISTINCT
                      SID, SID_TYPE, MENU_ID2 AS OBJ_ID, MENU_NM2 AS OBJ_NM
                  FROM
                      (
                      SELECT
                          E.*
                          ,A.MENU_ID AS MENU_ID1
                          ,A.MENU_NM AS MENU_NM1
                          ,B.MENU_ID AS MENU_ID2
                          ,B.MENU_NM AS MENU_NM2
                          ,C.MENU_ID AS MENU_ID3
                          ,C.MENU_NM AS MENU_NM3
                          ,D.MENU_ID AS MENU_ID4
                          ,D.MENU_NM AS MENU_NM4
                      FROM
                          KONA_PORTAL_MENU A
                          LEFT OUTER JOIN
                          KONA_PORTAL_MENU B
                          ON
                          A.PARENT_ID=B.MENU_ID
                          LEFT OUTER JOIN
                          KONA_PORTAL_MENU C
                          ON
                          B.PARENT_ID=C.MENU_ID
                          LEFT OUTER JOIN
                          KONA_PORTAL_MENU D
                          ON
                          C.PARENT_ID=D.MENU_ID
                          INNER JOIN
                          (SELECT distinct
                              A.SID, A.SID_TYPE, B.OBJ_ID
                          FROM
                              KONA_ACE A
                              INNER JOIN
                              KONA_ACL B
                              ON
                              A. ACL_ID=B.ACL_ID
                          WHERE
                              A.SID_TYPE IN ('G','P') -- 그룹권한, 사용자 권한
                              order by 1,3
                          ) E
                          ON
                          A.MENU_ID=E.OBJ_ID
                      ) X
                  WHERE
                      MENU_ID2 IS NOT NULL
                  UNION
                  SELECT DISTINCT
                      SID, SID_TYPE, MENU_ID3 AS OBJ_ID, MENU_NM3 AS OBJ_NM
                  FROM
                      (
                      SELECT
                          E.*
                          ,A.MENU_ID AS MENU_ID1
                          ,A.MENU_NM AS MENU_NM1
                          ,B.MENU_ID AS MENU_ID2
                          ,B.MENU_NM AS MENU_NM2
                          ,C.MENU_ID AS MENU_ID3
                          ,C.MENU_NM AS MENU_NM3
                          ,D.MENU_ID AS MENU_ID4
                          ,D.MENU_NM AS MENU_NM4
                      FROM
                          KONA_PORTAL_MENU A
                          LEFT OUTER JOIN
                          KONA_PORTAL_MENU B
                          ON
                          A.PARENT_ID=B.MENU_ID
                          LEFT OUTER JOIN
                          KONA_PORTAL_MENU C
                          ON
                          B.PARENT_ID=C.MENU_ID
                          LEFT OUTER JOIN
                          KONA_PORTAL_MENU D
                          ON
                          C.PARENT_ID=D.MENU_ID
                          INNER JOIN
                          (SELECT distinct
                              A.SID, A.SID_TYPE, B.OBJ_ID
                          FROM
                              KONA_ACE A
                              INNER JOIN
                              KONA_ACL B
                              ON
                              A. ACL_ID=B.ACL_ID
                          WHERE
                              A.SID_TYPE IN ('G','P') -- 그룹권한, 사용자 권한
                          ) E
                          ON
                          A.MENU_ID=E.OBJ_ID
                      ) X
                  WHERE
                      MENU_ID3 IS NOT NULL
                  UNION
                  SELECT DISTINCT
                      SID, SID_TYPE, MENU_ID4 AS OBJ_ID, MENU_NM4 AS OBJ_NM
                  FROM
                      (
                      SELECT
                          E.*
                          ,A.MENU_ID AS MENU_ID1
                          ,A.MENU_NM AS MENU_NM1
                          ,B.MENU_ID AS MENU_ID2
                          ,B.MENU_NM AS MENU_NM2
                          ,C.MENU_ID AS MENU_ID3
                          ,C.MENU_NM AS MENU_NM3
                          ,D.MENU_ID AS MENU_ID4
                          ,D.MENU_NM AS MENU_NM4
                      FROM
                          KONA_PORTAL_MENU A
                          LEFT OUTER JOIN
                          KONA_PORTAL_MENU B
                          ON
                          A.PARENT_ID=B.MENU_ID
                          LEFT OUTER JOIN
                          KONA_PORTAL_MENU C
                          ON
                          B.PARENT_ID=C.MENU_ID
                          LEFT OUTER JOIN
                          KONA_PORTAL_MENU D
                          ON
                          C.PARENT_ID=D.MENU_ID
                          INNER JOIN
                          (SELECT distinct
                              A.SID, A.SID_TYPE, B.OBJ_ID
                          FROM
                              KONA_ACE A
                              INNER JOIN
                              KONA_ACL B
                              ON
                              A. ACL_ID=B.ACL_ID
                          WHERE
                              A.SID_TYPE IN ('G','P') -- 그룹권한, 사용자 권한
                          ) E
                          ON
                          A.MENU_ID=E.OBJ_ID
                      ) X
                  WHERE
                      MENU_ID4 IS NOT NULL
                  ) A
                  INNER JOIN
                  KONA_ACL B
                  ON
                  A.OBJ_ID=B.OBJ_ID
              ) A;
          r_ERRMSG  := '[정상종료] INSERT KONA_ACE 상위 메뉴에 대한 권한 입력 처리건수 - ' || SQL%ROWCOUNT || '건 ';
          COMMIT;
          DBMS_OUTPUT.PUT_LINE('INSERT KONA_ACE 상위 메뉴에 대한 권한 입력 End');
          EXCEPTION WHEN OTHERS THEN
              r_ERRCODE := 'E';
              r_ERRMSG := '[이상종료 - INSERT KONA_ACE 상위 메뉴에 대한 권한 입력] ' || SQLErrm;
              GOTO CLOSE_ROUTINE;
      END;
      r_ERRCODE := 'S';
      <<CLOSE_ROUTINE>>
      IF r_ERRCODE = 'E' THEN
          ROLLBACK;
      ELSE
          COMMIT;
      END IF;
  END SP_TM_AUTH_SYNCHRONIZATION;

/

</sxh>

auth_synchronization.txt · Last modified: 2012/10/10 09:52 by lakystars