시노님 권한 관리 프로시져
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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;
/