CREATE OR REPLACE PROCEDURE SP_ADD_GRANT_AUTO
/*
-- 2019/08/12
-- 권한자동 관리 프로그램
-- 권한관리테이블에 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:XXX_SCOTT,AO:XXX_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:XXX_SCOTT,AO:XXX_TIGER
)
*/
(
p_exec in number default 1 -- 0 :SQL만 출력 , 1:실행
)
IS
/* 1.권한 부여 프로그램 */
CURSOR INVALID_OBJECT IS
-- 2.TB_MGR_GRANT에서 관리하는 권한에 빠진 권한 부여
SELECT A.GRANTEE, A.OWNER, A.TABLE_NAME, A.GRANTOR, A.PRIVILEGE,A.OBJECT_TYPE,A.GRANTABLE
FROM TB_MGR_GRANT@DL_SCOTT_DEV_SCOTT_DBA A
INNER JOIN DBA_OBJECTS B -- OBJECT 확인
ON ( B.OBJECT_NAME = A.TABLE_NAME
AND B.OWNER = A.OWNER
AND B.OBJECT_TYPE = A.OBJECT_TYPE
AND B.OBJECT_TYPE IN ('TABLE','VIEW','FUNCTION','SEQUENCE','SYNONYM')
)
WHERE NOT EXISTS ( SELECT 1 FROM DBA_TAB_PRIVS X -- DBA TABLE 권한에서 존재하는건 제외
WHERE A.TABLE_NAME = X.TABLE_NAME
AND A.GRANTEE = X.GRANTEE
AND A.GRANTOR = X.GRANTOR
AND A.OWNER = X.OWNER
AND A.PRIVILEGE = X.PRIVILEGE
AND A.OBJECT_TYPE = X.TYPE
)
AND A.USE_YN ='Y'
AND A.GRANTEE NOT IN ('SCOTT_DEV','XXX_SCOTT_DEV','TIGER_DEV','XXX_TIGER_DEV','WEB_DEV')
;
-- V_SQL VARCHAR2(1000);
V_G_SQL VARCHAR2(1000);
-- V_WG_SQL VARCHAR2(2000);
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_GRANTABLE VARCHAR2(10);
V_GRANT_REVOKE_GBN VARCHAR2(100);
V_MSG long;
BEGIN
DBMS_OUTPUT.ENABLE(2000);
FOR V_ROW IN INVALID_OBJECT
LOOP
-- 블록 에러 발생시에도 계속 실행토록
BEGIN
V_GRANTEE:= V_ROW.GRANTEE;
V_OWNER:= V_ROW.OWNER;
V_TABLE_NAME:= V_ROW.TABLE_NAME;
V_GRANTOR:= V_ROW.GRANTOR;
V_PRIVILEGE:= V_ROW.PRIVILEGE;
V_OBJECT_TYPE:= V_ROW.OBJECT_TYPE;
V_GRANTABLE:= V_ROW.GRANTABLE;
V_GRANT_REVOKE_GBN := 'GRANT TO USER';
-- 1. SCOTT,TIGER 일경우 WITH GRANT OPTION
IF V_GRANTABLE = 'YES' THEN
-- WITH GRANT OPTION
V_G_SQL := 'GRANT '||V_ROW.PRIVILEGE||' ON '||V_ROW.GRANTOR||'.'||V_ROW.TABLE_NAME||' TO '||V_ROW.GRANTEE || ' WITH GRANT OPTION';
ELSE -- GRANTABLE = 'NO' 일때
-- SCOTT 관리 대상 시스템 ?
IF V_GRANTEE IN ('SCOTT_APP','SCOTT_DEV','XXX_SCOTT_APP','XXX_SCOTT_DEV','WEB_APP','WEB_DEV','TIGER_APP','TIGER_DEV','XXX_TIGER_APP','XXX_TIGER_DEV') THEN
V_G_SQL := 'BEGIN '||V_ROW.GRANTOR||'.SP_ADD_'||V_ROW.GRANTOR||'_DEVS_GRANT('''||V_ROW.TABLE_NAME||''','''||V_ROW.PRIVILEGE||'''); END;';
ELSE
V_G_SQL := 'GRANT '||V_ROW.PRIVILEGE||' ON '||V_ROW.GRANTOR||'.'||V_ROW.TABLE_NAME||' TO '||V_ROW.GRANTEE;
END IF;
END IF;
IF p_exec <> 0 THEN
-- dbms_output.enable(4000);
-- dbms_output.put_line('V_G_SQL : >>> '||V_G_SQL);
EXECUTE IMMEDIATE V_G_SQL;
-- 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;
dbms_output.enable(4000);
dbms_output.put_line('GRANT SUCCESS : >>> '||V_G_SQL);
ELSE
dbms_output.enable(4000);
dbms_output.put_line('EXECUTE : >>> '||V_G_SQL);
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;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END;
/