/*
<용도>
- 특정 유저가 자신이 가지고 있는 프로시저의 수행여부(사용여부)를 판단하기 위한 목적
<전제 조건>
- DB Parameter : AUDIT_TRAIL = DB 이상으로 설정되어 있어야 함
*/
-- 1.AUDIT의 추가 또는 중단 : 불필요한 로그가 많이 쌓일 수 있으므로 수시로 수행해야 함
BEGIN
FOR X IN (
SELECT A.*
,CASE
WHEN A.AUDIT_YN = 0 -- AUDIT이 없고
AND A.AUDIT_LOG_YN = 0 -- AUDIT LOG도 없으면
THEN 'AUDIT EXECUTE ON ' || A.OBJECT_NAME || ' BY ACCESS' --> AUDIT 추가
WHEN A.AUDIT_YN = 1 -- AUDIT이 있고
AND A.AUDIT_LOG_YN = 1 -- AUDIT LOG가 존재하면
THEN 'NOAUDIT EXECUTE ON ' || A.OBJECT_NAME --> AUDIT 중단
END RUN_STR
FROM (
SELECT A.OBJECT_NAME
,CASE WHEN B.OBJECT_NAME IS NOT NULL
THEN 1
ELSE 0
END AUDIT_YN
,(
SELECT NVL(MAX(1), 0)
FROM USER_AUDIT_TRAIL S -- 프로시저 수행기록
WHERE S.OWNER = USER
AND S.OBJ_NAME = A.OBJECT_NAME
AND S.ACTION_NAME IN ('EXECUTE PROCEDURE')
AND ROWNUM = 1
) AUDIT_LOG_YN
FROM USER_OBJECTS A -- 프로시저
,USER_OBJ_AUDIT_OPTS B -- 프로시저 AUDIT 설정
WHERE A.OBJECT_TYPE = 'PROCEDURE'
AND B.OBJECT_NAME(+) = A.OBJECT_NAME
AND B.OBJECT_TYPE(+) = A.OBJECT_TYPE
) A
ORDER BY A.OBJECT_NAME
)
LOOP
IF X.RUN_STR IS NOT NULL THEN
EXECUTE IMMEDIATE X.RUN_STR;
DBMS_OUTPUT.PUT_LINE(X.RUN_STR);
END IF;
END LOOP;
END;
/
-- 2.프로시저의 사용여부 판단
SELECT A.OBJECT_NAME OBJECT_NAME --> 프로시저명
,A.CREATED CREATE_TIME --> 생성시간
,A.LAST_DDL_TIME LAST_DDL_TIME --> 최종 DDL 수행시간
,CASE WHEN B.OBJECT_NAME IS NOT NULL
THEN 1
END RUN_YN --> 수행여부
,B.USERNAME RUN_USERNAME --> 수행계정
,B.LAST_RUN_TIME LAST_RUN_TIME --> 최종수행시간
,CASE WHEN C.OBJECT_NAME IS NOT NULL
THEN 1
END AUDIT_YN --> AUDIT 설정 여부
FROM USER_OBJECTS A -- 프로시저
,(
SELECT B.OBJ_NAME OBJECT_NAME
,MAX(B.USERNAME ) USERNAME
,MAX(B.TIMESTAMP) LAST_RUN_TIME
FROM USER_AUDIT_TRAIL B
WHERE B.OWNER = USER
AND B.ACTION_NAME = 'EXECUTE PROCEDURE'
GROUP BY B.OBJ_NAME
) B -- 프로시저 AUDIT 설정
,USER_OBJ_AUDIT_OPTS C
WHERE A.OBJECT_TYPE = 'PROCEDURE'
AND B.OBJECT_NAME(+) = A.OBJECT_NAME
AND C.OBJECT_NAME(+) = A.OBJECT_NAME
AND C.OBJECT_TYPE(+) = A.OBJECT_TYPE
ORDER BY A.OBJECT_NAME
;