안녕하세요 Thinking Man 입니다.
이번 글에서는 Audit Trail 설정 예제를 통해서 감사 기록하는 방법에 대해서 알아보도록 하겠습니다.
이번 글을 읽게 되면 의심스러운 작업에 대해 Audit 설정을 수행하는 방법과 관련된 Audit Trail View를 확인하는 방법에 대해서 알 수 있습니다.
이 글은 Oracle 11gR2 공식 문서를 참고하여 작성하였습니다.
의심스러운 활동에 대한 감사를 위해 Audit 설정 예제
이번 챕터에서는 Audit Trail 을 확인하고 데이터를 해석하는 방법을 예제를 통해 알아보도록 하겠습니다. 아래의 의심되는 사례에 대해서 감사 데이터를 쌓을 수 있도록 감사 설정을 해야되는 상황이라고 가정하겠습니다.
- scott 스키마의 emp 테이블에서 행이 임의로 삭제됨.
- 허가 없이 일부 데이터베이스 사용자의 암호, 테이블스페이스 설정 및 할당량을 변경
- 많은 데드락이 발생하며, 이는 대부분 사용자가 전용 테이블 락을 획득하기 때문임.
thinker와 thinkingman 사용자가 위의 위반되는 작업을 수행했다고 의심하고 있는 상황.
조사를 위해 아래와 같이 AUDIT 설정을 실행합니다 (순서대로 수행)
AUDIT ALTER, INDEX, RENAME ON DEFAULT; -- alter, index, rename 감사 설정 CREATE VIEW scott.employee AS SELECT * FROM scott.emp; AUDIT SESSION BY thinker, thinkingman; -- 지정한 유저의 세션 감사 설정 AUDIT ALTER USER; -- alter user 명령어 감사 설정 AUDIT LOCK TABLE -- table lock 감사 설정 BY ACCESS WHENEVER SUCCESSFUL; AUDIT DELETE ON scott.emp -- scott.emp 테이블 delete 감사 설정 BY ACCESS WHENEVER SUCCESSFUL;
thinker 사용자가 아래처럼 다른 사용자의 TBS 할당량을 변경하고 특정 사용자를 삭제함.
ALTER USER tsmith QUOTA 0 ON users; -- tsmith의 USERS TBS 할당량을 0으로 변경 DROP USER TEST; -- TEST 유저 삭제
이후 thinkingman이 아래와 같이 table에 lock 을 걸고 데이터를 삭제하고 extent 할당을 변경하며 인덱스를 새롭게 만듬.
LOCK TABLE scott.emp IN EXCLUSIVE MODE; DELETE FROM scott.emp WHERE mgr = 7698; ALTER TABLE scott.emp ALLOCATE EXTENT (SIZE 100K); CREATE INDEX scott.ename_index ON scott.emp(ename); CREATE PROCEDURE scott.fire_employee (empid NUMBER) AS BEGIN DELETE FROM scott.emp WHERE empno = empid; END; / EXECUTE scott.fire_employee(7902);
아래부터는 위 예제에서 위반된 사항들을 AUDIT 관련된 View를 조회하여 위반 사항을 확인하는 예제입니다.
감사 활성화된 SQL문 확인
아래 VIEW는 설정된 모든 명령문에 대한 감사 옵션을 확인 할 수 있습니다.
SELECT * FROM DBA_STMT_AUDIT_OPTS;
아래와 같은 결과를 확인할 수 있습니다.
USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ------------------- ---------- ---------
THINKER CREATE SESSION BY ACCESS BY ACCESS -- AUDIT SESSION BY thinker, thinkingman 에 대한 결과
THINKINGMAN CREATE SESSION BY ACCESS BY ACCESS -- AUDIT SESSION BY thinker, thinkingman 에 대한 결과
ALTER USER BY ACCESS BY ACCESS -- AUDIT ALTER USER 에 대한 결과
LOCK TABLE BY ACCESS NOT SET -- AUDIT LOCK TABLE 에 대한 결과
활성화 된 감사 옵션 확인
아래 VIEW는 설정된 모든 권한 감사 옵션을 확인 할 수 있습니다.
SELECT * FROM DBA_PRIV_AUDIT_OPTS;
아래와 같은 결과를 확인 할 수 있습니다.
USER_NAME PRIVILEGE SUCCESS FAILURE
------------------- -------------------- --------- ----------
ALTER USER BY ACCESS BY ACCESS
THINKER CREATE SESSION BY ACCESS BY ACCESS
THINKINGMAN CREATE SESSION BY ACCESS BY ACCESS
특정 Object 에 활성화 된 감사 옵션 확인
아래 쿼리는 Owner가 SCOTT이면서 Object 이름이 ‘EMP’로 시작하는 모든 객체에 대한 감사 옵션을 확인할 수 있습니다.
SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'SCOTT' AND OBJECT_NAME LIKE 'EMP%';
아래와 같이 유사한 결과가 출력 됩니다.
OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ... ----- ----------- --------- --- --- --- --- --- --- --- --- ... SCOTT EMP TABLE S/S -/- -/- A/- -/- S/S -/- -/- ... SCOTT EMPLOYEE VIEW -/- -/- -/- A/- -/- S/S -/- -/- ...
위 쿼리는 지정된 Object에 대한 모든 감사 옵션에 대한 내용을 출력합니다. 결과의 내용은 아래처럼 해석하면 됩니다.
- 대시(-)는 감사 옵션이 설정되지 않았음을 나타냅니다.
- S 문자는 감사 옵션이 BY SESSION으로 설정되었음을 나타냅니다.
- A 문자는 감사 옵션이 BY ACCESS로 설정되었음을 나타냅니다.
- 각 감사 옵션에는 WHENEVER SUCCESSFUL 및 WHENEVER NOT SUCCESSFUL로 구분된 두 가지 가능한 설정이 있습니다. 예를 들어 scott.emp의 DELETE 감사 옵션은 성공한 DELETE 문에 대해 BY ACCESS로 설정되어 있고, 실패한 DELETE 문에 대해 설정되어 있지 않습니다.
기본 Object의 감사 옵션 확인
아래 View는 모든 기본 Object 에 대한 감사 옵션을 출력합니다.
SELECT * FROM ALL_DEF_AUDIT_OPTS;
아래와 같은 결과가 출력됩니다.
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /- -/-
이 View는 USER_OBJ_AUDIT_OPTS, DBA_OBJ_AUDIT_OPTS View와 유사한 정보를 반환합니다(이전 예제 참조).
모든 Object의 감사 된 내역 확인
아래 View는 DB의 모든 Object에 생성된 감사 데이터를 출력합니다.
SELECT * FROM DBA_AUDIT_OBJECT;
아래와 같은 결과가 출력됩니다.
USERNAME USERHOST TIMESTAMP OWNER OBJ_NAME ACTION_NAME SESSIONID ------------- ----------------- ------------ ----------- ------------- ----------------- ------------ THINKINGMAN DESKTOP-2ADLBTA 24/10/31 SCOTT EMP DELETE 30061 THINKINGMAN DESKTOP-2ADLBTA 24/10/31 SCOTT EMP LOCK 30061 THINKER DESKTOP-2ADLBTA 24/10/31 TSMITH ALTER USER 30058
THINKINGMAN 유저가 SCOTT 계정의 EMP 테이블을 DELETE 한 내역과 LOCK 수행 내역 확인 가능.
THINKER 유저가 수행한 TSMITH 계정의 TBS Quota를 ALTER 한 내역 확인 가능
AUDIT SESSION 옵션에 대한 감사 데이터 확인
아래의 쿼리는 AUDIT SESSION 감사에 해당하는 감사 데이터를 출력합니다.
SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK FROM DBA_AUDIT_SESSION;
아래와 같은 결과를 출력합니다.
USERNAME LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK ----------- ----------- ------------ ------------ ------------- ------------ THINKER 31-OCT-24 20981 756 154 0 THINKINGMAN 31-OCT-24 54860 935 3027 0
Audit Trail 관련 View 삭제
감사 작업을 더 이상 진행하지 않고 중단하고자 한다면 Audit Trail과 관련된 View들도 불필요하게 됩니다.
이런 경우 관련된 View를 삭제하는 방법은 SYS 계정으로 DB 접속 후에 CATNOAUD.SQL 파일을 실행하면 View들이 삭제됩니다.
해당 파일의 경로는 OS 마다 다르니 참고하세요.
같이 읽으면 좋은 글
> Audit Trail Purge 4편 (기타 Purge 작업 2)
> Audit Trail Purge 3편 (기타 Purge 작업)
> Audit Trail Purge 2편 (자동 Purge)
1 thought on “예제로 살펴보는 Audit 설정 및 Audit Trail View 확인”