CREATE OR REPLACE PROCEDURE SP_ADD_SYN_GRANT
/*
-- 2019/10/01
-- 시노님 추가 , 권한 추가
-- 원본테이블에 대한 시노님 생성
-- 권한관리테이블에 USE_YN = 'Y' 인 대상에 권한 부여
-- BY CYKIM
-- ----------------------------------------------------------------
-- 권한관리테이블(TB_MGR_GRANT)
CREATE TABLE TB_MGR_GRANT
(
GRANTEE VARCHAR2(128 BYTE),
OWNER VARCHAR2(128 BYTE),
TABLE_NAME VARCHAR2(128 BYTE),
GRANTOR VARCHAR2(128 BYTE),
PRIVILEGE VARCHAR2(40 BYTE),
GRANTABLE VARCHAR2(3 BYTE),
OBJECT_TYPE VARCHAR2(24 BYTE),
USE_YN CHAR(1 BYTE) DEFAULT 'Y' NOT NULL,
CREATED DATE DEFAULT SYSDATE NOT NULL,
OWNER_TYPE VARCHAR2(2 BYTE) DEFAULT 'R' -- R:scott,O:tiger,AR:AMEX_scott,AO:AMEX_tiger
)
CREATE UNIQUE INDEX IX_MGR_GRANT_01 ON TB_MGR_GRANT
(GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE,
OBJECT_TYPE)
;
-- 권한 추가 로그
CREATE TABLE TB_MGR_GRANT_LOG
(
GRANTEE VARCHAR2(128 BYTE),
OWNER VARCHAR2(128 BYTE),
TABLE_NAME VARCHAR2(128 BYTE),
GRANTOR VARCHAR2(128 BYTE),
PRIVILEGE VARCHAR2(40 BYTE),
OBJECT_TYPE VARCHAR2(24 BYTE),
CREATED DATE DEFAULT SYSDATE NOT NULL,
GRANT_REVOKE_GBN VARCHAR2(30 BYTE),
GRANT_REVOKE_SQL VARCHAR2(4000 BYTE),
GRANTABLE VARCHAR2(3 BYTE),
OWNER_TYPE VARCHAR2(2 BYTE) -- R:scott,O:tiger,AR:AMEX_scott,AO:AMEX_tiger
)
*/
(
p_towner in varchar2 default '', -- 테이블 오너(원본)
p_tname in varchar2 default '', -- 테이블 명(원본)
p_syn_owner in varchar2 default '', -- 생성할 시노님 오너(scott,tiger,WEB,..)
p_amex_user in varchar2 default '', -- amex_유저 AMEX_scott,AMEX_tiger
p_privs in varchar2 default '', -- SELECT,INSERT,UPDATE,DELETE
p_option in varchar2 default 'C', -- Create,New
p_exec in number default 0 -- 0 :SQL만 출력 , 1:실행
)
IS
/* 1.권한 부여 프로그램 */
CURSOR INVALID_OBJECT IS
-- 2.TB_MGR_GRANT에서 관리하는 권한에 빠진 권한 부여
SELECT A.GRANTEE
, A.TABLE_NAME
, LISTAGG(A.PRIVILEGE,',') WITHIN GROUP(ORDER BY 1) PRIVILEGE
FROM TB_MGR_GRANT@DL_scott_DEV_scott_DBA A
WHERE A.GRANTEE = p_syn_owner||'_APP' -- APP계정 기준
AND A.TABLE_NAME= p_tname
AND A.USE_YN ='Y'
GROUP BY A.GRANTEE , A.TABLE_NAME
;
V_S1_SQL VARCHAR2(1000);
V_S1_APP_SQL VARCHAR2(1000);
V_S1_DEV_SQL VARCHAR2(1000);
V_S2_SQL VARCHAR2(1000);
V_S2_APP_SQL VARCHAR2(1000);
V_S2_DEV_SQL VARCHAR2(1000);
V_G1_SQL VARCHAR2(1000);
V_G2_SQL VARCHAR2(1000);
V_P1_SQL VARCHAR2(1000);
V_P2_SQL VARCHAR2(1000);
V_GRANTEE VARCHAR2(100);
-- V_OWNER VARCHAR2(100);
V_TABLE_NAME VARCHAR2(100);
-- V_GRANTOR VARCHAR2(100);
V_PRIVILEGE VARCHAR2(100);
-- V_OBJECT_TYPE VARCHAR2(100);
-- V_GRANT_REVOKE_GBN VARCHAR2(100);
V_MSG long;
BEGIN
DBMS_OUTPUT.ENABLE;
if p_option = 'C' THEN
FOR V_ROW IN INVALID_OBJECT
LOOP
-- 블록 에러 발생시에도 계속 실행토록
BEGIN
V_GRANTEE:= V_ROW.GRANTEE;
V_TABLE_NAME:= V_ROW.TABLE_NAME;
-- V_GRANTOR:= V_ROW.GRANTOR;
V_PRIVILEGE:= NVL(p_privs,V_ROW.PRIVILEGE);
-- 대상 유저 시노님 생성
V_S1_SQL := 'CREATE OR REPLACE SYNONYM '||p_syn_owner||'.'||p_tname||' FOR '||p_towner||'.'||p_tname;
V_S1_APP_SQL := 'CREATE OR REPLACE SYNONYM '||p_syn_owner||'_APP.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;
V_S1_DEV_SQL := 'CREATE OR REPLACE SYNONYM '||p_syn_owner||'_DEV.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;
-- 대상 유저 권한 추가
V_G1_SQL := 'GRANT '||V_PRIVILEGE||' ON '||p_towner ||'.'||p_tname||' TO '||p_syn_owner||' WITH GRANT OPTION';
V_P1_SQL := 'BEGIN '||p_syn_owner||'.SP_ADD_'||p_syn_owner||'_DEVS_GRANT('''||p_tname||''','''||V_PRIVILEGE||'''); END;';
-- amex_user 시노님 생성
V_S2_SQL := 'CREATE OR REPLACE SYNONYM '||p_amex_user||'.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;
V_S2_APP_SQL := 'CREATE OR REPLACE SYNONYM '||p_amex_user||'_APP.'||p_tname||' FOR '||p_amex_user||'.'||p_tname;
V_S2_DEV_SQL := 'CREATE OR REPLACE SYNONYM '||p_amex_user||'_DEV.'||p_tname||' FOR '||p_amex_user||'.'||p_tname;
-- amex_user 권한 부여
V_G2_SQL := 'GRANT '||V_PRIVILEGE||' ON '||p_syn_owner ||'.'||p_tname||' TO '||p_amex_user||' WITH GRANT OPTION';
V_P2_SQL := 'BEGIN '||p_amex_user||'.SP_ADD_'||p_amex_user||'_DEVS_GRANT('''||p_tname||''','''||V_PRIVILEGE||'''); END;';
IF p_exec <> 0 THEN
IF p_towner <> p_syn_owner THEN -- 테이블<>시노님 오너가 다를 경우에는 시노님을 생성
EXECUTE IMMEDIATE V_S1_SQL;
END IF ;
-- _APP,_DEV 시노님 생성
EXECUTE IMMEDIATE V_S1_APP_SQL;
EXECUTE IMMEDIATE V_S1_DEV_SQL;
-- WITH GRANT 권한 부여
EXECUTE IMMEDIATE V_G1_SQL;
-- _APP,_DEV 권한 부여
EXECUTE IMMEDIATE V_P1_SQL;
IF p_amex_user IS NOT NULL THEN
EXECUTE IMMEDIATE V_S2_SQL;
-- AMEX_scott/tiger_APP,AMEX_scott/tiger_DEV 시노님 생성
EXECUTE IMMEDIATE V_S2_APP_SQL;
EXECUTE IMMEDIATE V_S2_APP_SQL;
EXECUTE IMMEDIATE V_G2_SQL;
EXECUTE IMMEDIATE V_P2_SQL;
END IF;
-- LOG 기록
-- INSERT INTO TB_MGR_GRANT_LOG
-- (GRANTEE , OWNER , TABLE_NAME , GRANTOR, PRIVILEGE,OBJECT_TYPE,GRANT_REVOKE_GBN)
-- VALUES (V_ROW.GRANTEE,V_ROW.OWNER,V_ROW.TABLE_NAME,V_ROW.GRANTOR,V_ROW.PRIVILEGE,V_ROW.OBJECT_TYPE,V_GRANT_REVOKE_GBN);
-- COMMIT;
ELSE
dbms_output.enable(4000);
IF p_towner <> p_syn_owner THEN -- 테이블<>시노님 오너가 다를 경우에는 시노님을 생성
dbms_output.put_line('[V_S1_SQL] '||V_S1_SQL);
END IF;
dbms_output.put_line('[V_S1_APP_SQL] '||V_S1_APP_SQL);
dbms_output.put_line('[V_S1_DEV_SQL] '||V_S1_DEV_SQL);
dbms_output.put_line('[V_G1_SQL] '||V_G1_SQL);
dbms_output.put_line('[V_P1_SQL] '||V_P1_SQL);
IF p_amex_user IS NOT NULL THEN
dbms_output.put_line('[V_S2_SQL] '||V_S2_SQL);
dbms_output.put_line('[V_S2_APP_SQL] '||V_S2_APP_SQL);
dbms_output.put_line('[V_S2_DEV_SQL] '||V_S2_DEV_SQL);
dbms_output.put_line('[V_G2_SQL] '||V_G2_SQL);
dbms_output.put_line('[V_P2_SQL] '||V_P2_SQL);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500);
-- INSERT INTO TB_MGR_GRANT_LOG
-- (GRANTEE , OWNER , TABLE_NAME , GRANTOR , PRIVILEGE ,OBJECT_TYPE ,GRANT_REVOKE_GBN,GRANT_REVOKE_SQL)
-- VALUES (V_GRANTEE,V_OWNER,V_TABLE_NAME,V_GRANTOR,V_PRIVILEGE,V_OBJECT_TYPE,V_GRANT_REVOKE_GBN,V_MSG);
-- COMMIT;
END;
END LOOP;
ELSIF p_option = 'N' THEN
V_S1_SQL := 'CREATE OR REPLACE SYNONYM '||p_syn_owner||'.'||p_tname||' FOR '||p_towner||'.'||p_tname;
V_S1_APP_SQL := 'CREATE OR REPLACE SYNONYM '||p_syn_owner||'_APP.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;
V_S1_DEV_SQL := 'CREATE OR REPLACE SYNONYM '||p_syn_owner||'_DEV.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;
V_G1_SQL := 'GRANT '||p_privs||' ON '||p_towner ||'.'||p_tname||' TO '||p_syn_owner||' WITH GRANT OPTION';
V_P1_SQL := 'BEGIN '||p_syn_owner||'.SP_ADD_'||p_syn_owner||'_DEVS_GRANT('''||p_tname||''','''||p_privs||'''); END;';
V_S2_SQL := 'CREATE OR REPLACE SYNONYM '||p_amex_user||'.'||p_tname||' FOR '||p_syn_owner||'.'||p_tname;
V_S2_APP_SQL := 'CREATE OR REPLACE SYNONYM '||p_amex_user||'_APP.'||p_tname||' FOR '||p_amex_user||'.'||p_tname;
V_S2_DEV_SQL := 'CREATE OR REPLACE SYNONYM '||p_amex_user||'_DEV.'||p_tname||' FOR '||p_amex_user||'.'||p_tname;
V_G2_SQL := 'GRANT '||p_privs||' ON '||p_syn_owner ||'.'||p_tname||' TO '||p_amex_user||' WITH GRANT OPTION';
V_P2_SQL := 'BEGIN '||p_amex_user||'.SP_ADD_'||p_amex_user||'_DEVS_GRANT('''||p_tname||''','''||p_privs||'''); END;';
IF p_exec <> 0 THEN
IF p_towner <> p_syn_owner THEN -- 테이블<>시노님 오너가 다를 경우에는 시노님을 생성
EXECUTE IMMEDIATE V_S1_SQL;
END IF ;
-- _APP,_DEV 시노님 생성
EXECUTE IMMEDIATE V_S1_APP_SQL;
EXECUTE IMMEDIATE V_S1_DEV_SQL;
-- WITH GRANT 권한 부여
EXECUTE IMMEDIATE V_G1_SQL;
-- _APP,_DEV 권한 부여
EXECUTE IMMEDIATE V_P1_SQL;
IF p_amex_user IS NOT NULL THEN
EXECUTE IMMEDIATE V_S2_SQL;
-- AMEX_scott/tiger_APP,AMEX_scott/tiger_DEV 시노님 생성
EXECUTE IMMEDIATE V_S2_APP_SQL;
EXECUTE IMMEDIATE V_S2_APP_SQL;
EXECUTE IMMEDIATE V_G2_SQL;
EXECUTE IMMEDIATE V_P2_SQL;
END IF;
-- 권한테이블에 추가
BEGIN SP_ADD_GRANT_TABLE(p_towner,p_tname,1);END;
-- LOG 기록
-- INSERT INTO TB_MGR_GRANT_LOG
-- (GRANTEE , OWNER , TABLE_NAME , GRANTOR, PRIVILEGE,OBJECT_TYPE,GRANT_REVOKE_GBN)
-- VALUES (V_ROW.GRANTEE,V_ROW.OWNER,V_ROW.TABLE_NAME,V_ROW.GRANTOR,V_ROW.PRIVILEGE,V_ROW.OBJECT_TYPE,V_GRANT_REVOKE_GBN);
-- COMMIT;
ELSE
dbms_output.enable(4000);
IF p_towner <> p_syn_owner THEN -- 테이블<>시노님 오너가 다를 경우에는 시노님을 생성
dbms_output.put_line('[V_S1_SQL] '||V_S1_SQL);
END IF;
dbms_output.put_line('[V_S1_APP_SQL] '||V_S1_APP_SQL);
dbms_output.put_line('[V_S1_DEV_SQL] '||V_S1_DEV_SQL);
dbms_output.put_line('[V_G1_SQL] '||V_G1_SQL);
dbms_output.put_line('[V_P1_SQL] '||V_P1_SQL);
IF p_amex_user IS NOT NULL THEN
dbms_output.put_line('[V_S2_SQL] '||V_S2_SQL);
dbms_output.put_line('[V_S2_APP_SQL] '||V_S2_APP_SQL);
dbms_output.put_line('[V_S2_DEV_SQL] '||V_S2_DEV_SQL);
dbms_output.put_line('[V_G2_SQL] '||V_G2_SQL);
dbms_output.put_line('[V_P2_SQL] '||V_P2_SQL);
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END;
/
권한 제거
CREATE OR REPLACE PROCEDURE SP_DEL_GRANT_USER_REVOKE
(
p_tname in varchar2 default '' -- 테이블 명
, p_grantor in varchar2 default '' -- AMEX_scott,AMEX_tiger
, p_grantee in varchar2 default '' -- _DEV,_APP
--, p_gnropt in varchar2 default 'R' -- With Grant Option
, p_option in varchar2 default 'RU' -- RU:revoke USER,RA:revoke All
, p_delmgr in varchar2 default 'Y' -- 권한 관리 테이블에서 삭제 할것인지?
, p_exec in number default 0 -- 0 :SQL만 출력 , 1:실행
) -- authid current_user
is
/* 1.권한관리테이블에 추가 대상 */
CURSOR ADD_OBJECT IS
-- 1.신규 추가된 권한 TB_MGR_INDEX 입력
SELECT A.OWNER
, A.TABLE_NAME
, A.GRANTEE
, A.GRANTOR
, A.GRANTABLE
, A.PRIVILEGE
, A.OBJECT_TYPE
FROM TB_MGR_GRANT@DL_scott_DEV_scott_DBA A
WHERE A.TABLE_NAME = p_tname -- 'TABLE_NAME'
AND A.GRANTOR = p_grantor -- 'AMEX_scott'
-- AND A.GRANTEE IN (p_grantee)
AND A.USE_YN = 'Y'
;
V_G_SQL VARCHAR2(200);
V_MSG long;
begin
-- []------------------------------------------------------------------------------------
IF p_option = 'RU' THEN -- 해당 GRANTOR의 해당 GRANTEE 제거
FOR V_ROW IN ADD_OBJECT
LOOP
-- 명시된 GRANTEE만 제거
IF V_ROW.GRANTOR IN (p_grantee) THEN
V_G_SQL := 'REVOKE '||V_ROW.PRIVILEGE||' ON '||V_ROW.GRANTOR||'.'|| V_ROW.TABLE_NAME ||' FROM '||V_ROW.GRANTEE;
IF p_exec <> 0 THEN
EXECUTE IMMEDIATE V_G_SQL;
-- 권한 테이블에서 제거 하기
IF p_delmgr = 'Y' THEN
DELETE FROM TB_MGR_GRANT@DL_scott_DEV_scott_DBA
WHERE OWNER = V_ROW.OWNER
AND TABLE_NAME = V_ROW.TABLE_NAME
AND GRANTEE = V_ROW.GRANTEE
AND GRANTOR = V_ROW.GRANTOR
AND PRIVILEGE = V_ROW.PRIVILEGE
;
COMMIT;
END IF;
-- LOG 기록
INSERT INTO TB_MGR_GRANT_LOG
( OWNER , TABLE_NAME , GRANTEE , GRANTOR , GRANTABLE
, PRIVILEGE , OBJECT_TYPE, GRANT_REVOKE_GBN,GRANT_REVOKE_SQL)
VALUES ( V_ROW.OWNER ,V_ROW.TABLE_NAME ,V_ROW.GRANTEE ,V_ROW.GRANTOR ,V_ROW.GRANTABLE
,V_ROW.PRIVILEGE ,V_ROW.OBJECT_TYPE, 'R',V_G_SQL);
COMMIT;
END IF;
ELSE
dbms_output.enable(100);
dbms_output.put_line('>>> '||V_G_SQL);
END IF;
-- COMMIT;
END LOOP;
-- []------------------------------------------------------------------------------------
ELSIF p_option = 'RA' THEN -- GRANTOR가 준 전체 권한
FOR V_ROW IN ADD_OBJECT
LOOP
V_G_SQL := 'REVOKE '||V_ROW.PRIVILEGE||' ON '||V_ROW.GRANTOR||'.'|| V_ROW.TABLE_NAME ||' FROM '||V_ROW.GRANTEE;
IF p_exec <> 0 THEN
EXECUTE IMMEDIATE V_G_SQL;
-- 권한 테이블에서 제거 하기
IF p_delmgr = 'Y' THEN
DELETE FROM TB_MGR_GRANT@DL_scott_DEV_scott_DBA
WHERE OWNER = V_ROW.OWNER
AND TABLE_NAME = V_ROW.TABLE_NAME
AND GRANTEE = V_ROW.GRANTEE
AND GRANTOR = V_ROW.GRANTOR
AND PRIVILEGE = V_ROW.PRIVILEGE
;
COMMIT;
END IF;
-- LOG 기록
INSERT INTO TB_MGR_GRANT_LOG
( OWNER , TABLE_NAME , GRANTEE , GRANTOR , GRANTABLE
, PRIVILEGE , OBJECT_TYPE, GRANT_REVOKE_GBN,GRANT_REVOKE_SQL)
VALUES ( V_ROW.OWNER ,V_ROW.TABLE_NAME ,V_ROW.GRANTEE ,V_ROW.GRANTOR ,V_ROW.GRANTABLE
,V_ROW.PRIVILEGE ,V_ROW.OBJECT_TYPE, 'R',V_G_SQL);
COMMIT;
ELSE
dbms_output.enable(100);
dbms_output.put_line('>>> '||V_G_SQL);
END IF;
-- COMMIT;
END LOOP;
END IF;
exception
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' ||V_G_SQL||'-'|| SQLERRM);
end;
/