[Oracle] Audit Trail 기능 사용하기
앞 글에서 스키마 삭제 시 DBA_DEPENDENCIES 뷰를 사용해서 의존성까지 점검했음에도 불구하고 여전히 불안감이 있을 수 있습니다. 저도 불안해서 Oracle의 Audit Trail 기능을 사용해서 의심스러운 스키마를 삭제하기 전에 하나 더 점검하려고 합니다. 참고로 Invalid Object는 Audit Trail 기능으로 감사 로그를 기록할 수 없기 때문에 별도 Trigger를 생성해서 Invalid Object의 접근/사용 이력을 기록하고 있습니다.
- Audit Trail 기능 설명
- Audit Trail 환경 설정(기능 활성화, 초기화/보존기간/자동삭제 등)
- Audit Trail 감사 대상 설정
- Audit Trail 로그 모니터링
- Audit Trail 해제
- Audit Trail 로그 수동 삭제
1. Audit Trail 기능 설명
Audit Trail은 데이터베이스 내에서 발생하는 사용자의 행위(접속, 쿼리 실행, 객체 수정 등)를 기록하고 보관하는 기능입니다.
- 목적: 보안 침해 사고 분석(포렌식), 내부 통제 준수 여부 확인, 데이터 변경 이력 추적.
- 기록 내용: ‘누가(User)’, ‘언제(Time)’, ‘어떤 객체에(Object)’, ‘어떤 작업(Action: SELECT, DELETE 등)’을 했는지, 그리고 그 결과가 ‘성공했는지 실패했는지’를 기록합니다.
- 저장소: AUDIT_TRAIL 파라미터의 값이 DB인 경우는 SYS.AUD$ 테이블에 저장되며, DBA_AUDIT_TRAIL 뷰를 통해 조회합니다. AUDIT_TRAIL 파라미터의 값이 xml인 경우는 AUDIT_FILE_DEST 파라미터에 설정된 OS 디렉토리에 xml 파일 형식으로 저장되며, V$XML_AUDIT_TRAIL 뷰를 통해 조회합니다. 참고로 FGA(Fine-Grained Auditing, 세밀한 감사) 기능에 의해 생성된 감사 로그는 SYS.FAG_LOG$ 테이블에 저장되며, DBA_FGA_AUDIT_TRAIL 뷰를 통해 조회합니다. DBA_COMMON_AUDIT_TRAIL 뷰는 일반 감사 로그(SYS.AUD$)와 FGA 로그(SYS.FGA_LOG$)를 합쳐서 한꺼번에 보여주는 뷰입니다.
- 종류: 시스템 권한 감사(DBA_PRIV_AUDIT_OPTS 뷰), DDL 등 문장 감사(DBA_STMT_AUDIT_OPTS 뷰), DML 등 객체 감사(DBA_OBJ_AUDIT_OPTS 뷰)
2. Audit Trail 환경 설정(기능 활성화, 초기화/보존기간/자동삭제 등)
별도로 설정을 하지 않으면 감사 로그(SYS.AUD$)는 SYSTEM 테이블스페이스가 꽉 찰 때까지 무한정 쌓입니다. 이를 관리하기 위해 오라클은 DBMS_AUDIT_MGMT 패키지를 통해 보존 기간(정확히는 삭제 기준점)을 설정하고 관리하는 방법을 제공합니다.
- Audit Trail 기능이 활성화되어 있는지 시스템 파라미터를 확인해 봅니다. SYS 관리자의 작업도 기록되고 있는지 확인합니다.
SQL> SHOW PARAMETER AUDIT_TRAIL; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_trail string DB SQL> SHOW PARAMETER AUDIT_SYS_OPERATIONS; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_sys_operations boolean TRUE SQL> SHOW PARAMETER AUDIT_FILE_DEST; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/CMAIN/adump
- Audit Trail 기능이 활성화되지 않았다면, 먼저 AUDIT_TRAIL 파라미터를 설정한 후, DB를 재기동해야 적용됩니다. AUDIT_TRAIL 파라미터를 DB로 설정하더라도 AUDIT_SYS_OPERATIONS 파라미터가 TRUE로 설정되어 있다면, AUDIT_FILE_DEST 파라미터에 설정된 디렉토리에 SYS 관리자 계정의 로그가 .aud 파일 형식으로 저장됩니다. 그 이유는 오라클은 보안상의 이유로 “최고 관리자(SYS)는 자기 자신의 범죄 증거(로그)를 스스로 지울 수 없어야 한다”는 원칙을 가지고 있기 때문입니다.
ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE; -- DB로 저장 SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB SCOPE=SPFILE; -- XML로 저장(권장) SQL> ALTER SYSTEM SET AUDIT_TRAIL=XML SCOPE=SPFILE;
- 관리 인프라 초기화 (최초 1회): 감사 로그를 자동으로 관리할 수 있도록 인프라를 먼저 구성
BEGIN DBMS_AUDIT_MGMT.INIT_CLEANUP( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, default_cleanup_interval => 24 -- 24시간마다 체크 ); END; / 또는 EXEC DBMS_AUDIT_MGMT.INIT_CLEANUP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, 24); -- 설정 확인 SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE PARAMETER_NAME = 'DEFAULT CLEAN UP INTERVAL'; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------- --------------- ----------- DEFAULT CLEAN UP INTERVAL 24 STANDARD AUDIT TRAIL - 보존 기준일 설정: 30일 이전 데이터 삭제 대상으로 지정
BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, -- 표준 감사($AUD) last_archive_time => SYSDATE - 30 ); END; / 또는 EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, SYSDATE - 30); -- 설정 확인 SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS WHERE AUDIT_TRAIL = 'STANDARD AUDIT TRAIL'; AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS -------------------- ------------ ----------------------------- STANDARD AUDIT TRAIL 0 2025-12-09 08:15:24.000 +0000 - 자동 삭제(Purge) 예약 작업 생성: 24시간마다 실행하는 스케줄러 등록
BEGIN DBMS_AUDIT_MGMT.CREATE_PURGE_JOB( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, audit_trail_purge_interval => 24, audit_trail_purge_name => 'DAILY_AUDIT_PURGE_JOB' ); END; / 또는 EXEC DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, 24, 'DAILY_AUDIT_PURGE_JOB') -- 설정 확인 SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS WHERE JOB_NAME = 'DAILY_AUDIT_PURGE_JOB'; JOB_NAME JOB_STATUS AUDIT_TRAIL JOB_FREQUENCY --------------------- ---------- ---------------- ----------------------- DAILY_AUDIT_PURGE_JOB ENABLED ALL AUDIT TRAILS FREQ=HOURLY;INTERVAL=24 - 패키지 설정이 복잡하다면, 많은 DBA들은 그냥 간단한 PL/SQL 스크립트를 작성하여 매일 밤 실행합니다.
DELETE FROM SYS.AUD$ WHERE NTIMESTAMP# < SYSDATE - 30; COMMIT;
3. Audit Trail 감사 대상 설정
감사를 시작하려면 구체적인 감사 대상을 지정해야 합니다. 감사 대상에는 시스템 권한 감사, 문장 감사(DDL 등), 객체 감사(DML 등)가 있습니다.
- 시스템 권한 감사 설정 사례
AUDIT ALTER SYSTEM;: 데이터베이스 파라미터 변경이나 로그 스위치 강제 발생 등 시스템 설정을 건드리는 행위 감시.AUDIT SELECT ANY TABLE;: '본인 소유가 아닌' DB 내의 모든 테이블을 조회할 수 있는 권한을 사용하는지 추적.AUDIT CREATE ANY PROCEDURE;: '타인의 프로시저를 생성할 수 있는' 특정 시스템 권한 사용을 감시AUDIT CREATE USER, ALTER USER, DROP USER;: 보안에 직결되는 계정 생성 및 삭제 행위 모니터링.AUDIT CREATE EXTERNAL JOB;: OS 레벨의 스크립트를 실행할 수 있는 위험 권한 사용 감시.AUDIT EXPORT FULL DATABASE;: DB 전체 데이터를 파일로 추출(Dump)하려는 시도 추적. -- 시스템 권한 감사 설정 확인 SELECT * FROM DBA_PRIV_AUDIT_OPTS; - DDL 등 문장 감사 설정 사례
AUDIT SESSION;: 모든 사용자의 로그인 및 로그아웃 기록을 남겨 접속 이력을 관리.AUDIT SESSION BY 스키마;: 특정 스키마의 로그인 및 로그아웃 기록을 남겨 접속 이력을 관리.AUDIT TABLE;: 어떤 유저가 어떤 테이블을 CREATE, DROP, TRUNCATE 했는지 DDL 이력 추적.AUDIT PROCEDURE;: 프로시저, 함수, 패키지의 생성 및 변경 사항 감시.AUDIT PUBLIC SYNONYM;: 전체 유저에게 영향을 주는 공용 시노님을 생성하거나 삭제하는 행위 확인.AUDIT DATABASE LINK;: 외부 DB와의 연결 통로를 생성하거나 삭제하여 데이터 유출 경로를 만드는지 감시. -- 문장 감사 설정 확인 SELECT * FROM DBA_STMT_AUDIT_OPTS; -- 특정 시키마의 로그인 및 로그아웃 기록 확인 SELECT * FROM DBA_AUDIT_SESSION WHERE USERNAME = '스키마'; - DML 등 객체 감사 설정 사례
AUDIT SELECT ON 스키마.객체;: 특정 객체(뷰, 테이블 또는 시노님)을 누가 조회하는지 확인.AUDIT INSERT, UPDATE, DELETE ON 스키마.테이블명;: 급여 테이블처럼 민감한 정보의 변경(DML) 행위를 집중 감시.AUDIT EXECUTE ON 스키마.프로시저;: 특정 스키마의 프로시저를 누가 호출하여 사용하는지 추적.AUDIT ALL ON 스키마.테이블 WHENEVER NOT SUCCESSFUL;: 특정 테이블에 접근하려다 권한 부족으로 실패한 '수상한 시도'만 기록.AUDIT GRANT ON 스키마.테이블;: 특정 테이블에 대한 권한을 다른 사람에게 함부로 부여(GRANT)하는 행위 감시. -- 객체 감사 설정 확인(A/A는 성공/실패 매번 기록, S/S는 세션 동안 성공/실패 한 개만 기록, -/-는 감시 안함) SELECT * FROM DBA_OBJ_AUDIT_OPTS; - 옵션 조합하기: 각 사례 뒤에 아래 옵션을 붙여 더 정교하게 설정할 수 있습니다.
BY ACCESS: 실행될 때마다 매번 기록 (상세한 증거가 필요할 때)BY SESSION: 한 세션에서 여러 번 수행해도 1번만 기록 (로그 용량 절약)WHENEVER SUCCESSFUL: 정상적으로 수행된 작업만 기록WHENEVER NOT SUCCESSFUL: 오류가 난(침입 시도 등) 작업만 기록 - 주의사항 및 특기사항
- Invalid 객체에 대한 Audit Trail 기록은 발생하지 않습니다
- Invalid View에 대한 Audit Trail 설정은 가능하지만 설정 해제(NOAUDIT) 시 ORA-04063 오류가 발생합니다.
4. Audit Trail 로그 모니터링
- 설정된 감사가 실제로 어떻게 기록되고 있는지 딕셔너리 뷰를 통해 상시 모니터링합니다.
< AUDIT_TRAIL=DB인 경우 > SELECT EXTENDED_TIMESTAMP, -- 상세 발생 시간 USERNAME, -- DB 접속 계정 OS_USERNAME, -- OS 사용자 계정 USERHOST, -- 접속한 장비(호스트) 명 ACTION_NAME, -- 수행한 작업 (SELECT, INSERT, UPDATE, DELETE, LOGON 등) OWNER, -- 대상 객체 소유자 OBJ_NAME, -- 대상 객체 이름 RETURNCODE -- 0이면 성공, 그 외는 에러 번호 FROM DBA_AUDIT_TRAIL WHERE OBJ_NAME = '객체명' ORDER BY TIMESTAMP DESC; < AUDIT_TRAIL=XML인 경우 > SELECT EXTENDED_TIMESTAMP, -- 상세 발생 시간 DB_USER, -- 접속 계정 OS_USER, -- OS 사용자 계정 OS_HOST, -- 접속한 장비(호스트) 명 ACTION, -- 수행한 작업 (SELECT, INSERT, LOGON 등) OBJECT_SCHEMA, -- 대상 객체 소유자 OBJECT_NAME, -- 대상 객체 이름 SQL_TEXT, -- 실행된 실제 SQL 문장 RETURNCODE -- 0이면 성공, 그 외는 에러 번호 FROM V$XML_AUDIT_TRAIL WHERE OBJECT_NAME = '객체명' ORDER BY EXTENDED_TIMESTAMP DESC;
5. Audit Trail 해제
- 더 이상 감사가 필요하지 않을 때는
NOAUDIT명령어를 사용합니다. 설정할 때와 동일한 키워드를 사용해야 합니다.-- 시스템 권한 감사 NOAUDIT SESSION; -- 문장 감사 NOAUDIT SESSION; -- 객체 감사 NOAUDIT SELECT ON 스키마.객체;
6. Audit Trail 로그 백업 및 수동 삭제
감사 로그는 자동으로 지워지지 않으므로 SYSAUX나 SYSTEM 테이블스페이스가 꽉 차지 않도록 주기적으로 관리해야 합니다.
- AUDIT_TRAIL 파라미터를 XML로 설정한 경우에는, AUDIT_FILE_DEST 경로의 XML 파일들을 백업합니다.
- AUDIT_SYS_OPERATIONS = TRUE라면, AUDIT_TRAIL 파라미터 설정과 상관없이 SYS 및 관리자 로그는 AUDIT_FILE_DEST 경로에 .aud 텍스트 파일로 저장되므로 함께 백업합니다.
- 표준 삭제 방법 (추천): DB 및 XML 모두 적용됨
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, USE_LAST_ARCH_LOG_SET => FALSE ); END; / - AUDIT_TRAIL 파라미터를 DB로 설정한 경우, 응급 상황 시 전체 삭제: SYS 계정으로 실행
TRUNCATE TABLE SYS.AUD$;
