오라클 데이터베이스 보안 감사 Audit 기능 - SQL Query, DML, DDL 로깅

감사(Audit) 기능이란 Database 내에서 실행되는 SQL Query 에 대한 기록을 남기는 행위입니다.

나중에 문제가 생겨서 어떤놈이, 언제, 어떤 SQL 을 실행했는지 찾고자 할 때 꼭 필요한 보안 기능 중 하나입니다.

 

Oracle Database 는 Audit 기능을 기본으로 지원하고, 이때 DML, DDL SQL 까지도 Audit 이 가능합니다.

Audit 기능을 이용하기 위해서는 "audit_trail" DB파라메타값이 none 이 아닌 값으로 세팅되어 있어야 합니다.

audit_trail 파라메타의 디폴트값은 none 으로 되어있습니다.

 

 

audit_trail 파라메타값을 아래와 같이 변경해줍니다.

 

alter system set audit_trail=db, extended scope=spfile sid='*';
    -- 파라메타값 변경하고, DB 재기동 해줘야 합니다.

 

audit_trail 값에는 다음과 같이 여러가지 모드가 있습니다.

none : audit 로그를 남기지 않습니다. (default)
os :  audit_file_dest 파라메타에 지정된 디렉토리에 *.aud 파일로 audit 로그를 남깁니다.
db : DB내의 sys.aud$ 테이블에 audit 로그를 남깁니다.
db, extended : 위 db 와 같은데, 추가로 sql문과 bind 변수값 정보도 남깁니다.
xml : OS 파일 시스템에 xml 형태로 audit 로그를 남깁니다.
xml, extended : 위 xml 과 같은데, 추가로 sql문과 bind 변수값 정보도 남깁니다.

 

실제로 os 모드로 해서 보면, 그닥 아름답게 나오지는 않습니다.

"db, extended" 모드로 세팅했을때, SQL문도 제대로 나오고, 가장 그럴듯하게 볼 수 있습니다.

 

 

DB 파라메타를 세팅해 줬으면, 그 다음으로는 Audit 하려는 DB유저에 audit 을 걸어줍니다.

 

SQL> audit select table, insert table, update table, delete table by SCOTT;
        --> scott 유저의 모든 테이블에 대해 SQL Query, DML 문에 대해 Audit 설정

SQL> audit table, alter table by SCOTT;
        --> scott 유저에서 실행되는 Table 관련 DDL 문에 대해 Audit 설정

 

위 예는 아주 심플하게 scott 유저에 대해 걸어줬는데, 실제로 audit 걸어주는 방식은 아주 다양합니다.

특정 테이블에 대해서만 걸어줄 수도 있습니다.

자세한 것은 매뉴얼을 참조 하세요  => https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4007.htm#SQLRF01107

 

 

아래는 실행예입니다.

 

위는 scott 유저에서 TEST999 테이블을 생성하고, 데이터를 Insert, Update 하고, 테이블을 변경하는 것까지 실행한 예입니다. Query, DML, DDL 을 모두 사용했습니다.

 

이런 활동들이 모두 audit 됩니다.

sys.aud$ 테이블에 정보가 기록되는데, 이 테이블을 직접 조회하는 것보다는 dba_audit_trail 뷰를 조회하는 것이 좀더 편합니다.

 

위에서 실행한 SQL문장을 dba_audit_trail 뷰에서 정확히 확인할 수 있습니다.

 

 

위에서 사용한 스크립트는 아래와 같습니다.

 

set linesize 200
col userhost for a15
col os_username for a15
col username for a15
col owner for a15
col obj_name for a20
col sql_text for a50
col sql_bind for a10

select timestamp, userhost, os_username, username, owner, obj_name,
       sql_text, sql_bind
from dba_audit_trail
where owner in ('SCOTT')
order by timestamp;

 

 

만약에 IP Address 정보까지 Auditing 하고자 하는 경우에는,

아래와 같이 트리거(Trigger)를 걸어줘야 합니다.

 

-- sys 유저에서 실행
create or replace trigger IN_clientid after logon on database
   declare
     v_ipinfo varchar2(50);
   begin
     select nvl(sys_context('userenv','IP_ADDRESS'),'127.0.0.1') into v_ipinfo from dual;
     dbms_session.set_identifier(v_ipinfo);
   end;
/

 

위와 같이 after logon on database 트리거를 sys 유저에서 한번만 걸어주면 됩니다.

그러면, 아래와 같이 ip address 도 함께 조회할 수 있습니다.

 

로컬 DB서버에서 직접 접속해서 실행한 경우는 127.0.0.1 로 나옵니다.

다른 서버에서 SQL*Net 으로 접속해서 실행한 경우에는 해당 Client 의 IP가 제대로 찍힙니다.