행위

권한 관리 프로시져

DB CAFE

thumb_up 추천메뉴 바로가기


권한 관리 프로시져(스케줄링으로 자동화처리)[편집]

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;
/