쉽게 설명한 감사 기능의 확장

알게 쉬운 감사 기능의 확장

 

Oracle Database 10g 의 개선된 감사(Audit Trail) 기능을 이용해 매우 상세한 수준의 사용자 접근 기록을 관리하고, 수작업에 의존한 트리거 기반의 감사 작업을 최소화할 수 있습니다.

 

사용자 Joe가 아래와 같이 테이블 로우에 대한 업데이트를 수행한 경우를 가정해 봅시다.

 

update SCOTT.EMP set salary = 12000 where empno = 123456;

 

이 작업이 데이타베이스에는 어떠한 형태의 기록으로 남게 될까요? Oracle9i Database와 그 이전 버전에서는 “누가” 접근했는지에 대한 기록은 남았지만, “어떤” 작업을 했는지에 대해서는 아무런 기록도 남지 않았습니다. 예를 들어, 사용자 Joe가 SCOTT가 소유한 EMP 테이블을 업데이트했다는 사실은 알 수 있지만, 그가 사번 123456을 가진 직원의 salary 컬럼을 업데이트했다는 사실은 알 수 없습니다. 또 salary 컬럼의 변경 전 값 역시 기록되지 않습니다. 이처럼 상세한 변경 내역을 추적하려면, 트리거를 적용하거나 Log Miner를 이용해 아카이브 로그를 뒤지는 수 밖에 없었습니다.

 

두 가지 방법 모두 변경된 컬럼과 값을 확인할 수는 있지만 이에 수반되는 비용이 만만치 않다는 문제가 따릅니다. 감사 목적으로 적용되는 트리거는 성능적으로 심각한 부담을 줍니다. 이러한 이유 때문에 사용자 정의 트리거의 적용을 아예 금지하는 경우도 많습니다. Log Miner는 성능 자체에는 아무런 영향을 주지 않으나 변경 내역을 추적하기 위해 아카이브 로그에 의존해야 한다는 문제가 있습니다.

 

Oracle9i에 추가된 Fine-Grained Auditing(FGA) 기능은 로우(row) 단위 변경 내역을 SCN 넘버와 함께 저장함으로써 이전의 데이타를 재구성할 수 있게 하지만, select 구문에 대해서만 적용 가능하고 update, insert, delete 등의 DML 구문에는 적용할 수 없습니다. 이러한 이유로, Oracle Database 10g 이전 버전에서 로우 단위의 데이타 변경 내역을 추적하려면 트리거를 적용하는 것 외에 다른 방법이 없었습니다.

 

Oracle Database 10g에서는 감사 작업과 관련하여 두 가지 중요한 기능 개선이 이루어졌습니다. 이 문서에서는 오라클이 제공하는 두 종류의 감사 관련 기능 ? (모든 버전에서 제공되는) 표준 감사(standard audit) 기능과 (Oracle9i 이후 버전에서 제공되는) fine-grained audit 기능 ? 의 관점에서 개선된 사항을 설명하고자 합니다.

 

새로운 기능들

 

첫 번째로, FGA는 select 이외의 다른 DML 구문을 지원합니다. 변경 내역은 이전과 마찬가지로 FGA_LOG$에 기록되며, DBA_FGA_AUDIT_TRAIL 뷰를 통해 확인할 수 있습니다. 또 특정 컬럼이 액세스되는 경우, 또는 정의된 일련의 컬럼이 액세스되는 경우에만 트리거가 동작하도록 설정하는 것이 가능합니다

 

“AUDIT” SQL 명령으로 구현되는 표준 감사 기능은 특정 오브젝트의 감사를 위한 환경을 쉽고 빠르게 설정하려는 경우 유용합니다. 예를 들어, Scott가 소유자인 EMP 테이블에 대한 모든 업데이트를 추적하려면 아래와 같은 명령을 사용합니다:

 

audit UPDATE on SCOTT.EMP by access;

 

위 명령은 SCOTT.EMP 테이블에 업데이트가 발생할 때마다 그 내역을 AUD$ 테이블에 기록합니다. 감사 기록은 DBA_AUDIT_TRAIL 뷰를 통해 조회할 수 있습니다.

 

이 기능은 10g 이전의 버전에서도 제공되어 오던 것입니다. 하지만 이전 릴리즈에서는 제한적인 정보(구문을 실행한 사용자, 시간, 터미널 ID, 등)만을 추적할 수 있었으며, 바인딩 되는 변수 값과 같은 중요한 정보의 추적이 불가능했습니다. 10g에서는 이전 버전에서 제공되던 것에 추가하여 여러 가지 중요한 정보를 수집할 수 있도록 개선되었습니다. 감사 정보를 저장하는 AUD$ 테이블에도 몇 가지 새로운 컬럼이 추가되었으며, 이전과 마찬가지로 DBA_AUDIT_TRAIL을 통해 확인할 수 있습니다. AUD$ 테이블에 추가된 컬럼에 대해 좀 더 자세히 살펴보기로 합시다.

 

EXTENDED_TIMESTAMP. 이 컬럼은 감사 기록의 타임스탬프 값을 TIMESTAMP(6) 포맷으로 저장합니다 (TIMESTAMP(6) 포맷은 Time Zone 정보와 함께 그리니치 평균 시간(Universal Coordinated Time으로 불리기도 합니다)을 소수점 이하 9자리 초 단위로 표현합니다). 이 포맷으로 저장된 시간 정보의 예가 아래와 같습니다:

 

2004-3-13 18.10.13.123456000 -5:0

 

위 데이타는 미국 동부 표준 시간을 기준으로 한 2004년 3월 13일 시간 정보를 표시하고 있습니다 (-5.0”은 UTC 보다 5시간 이전을 기준으로 함을 의미합니다).

 

이와 같은 확장형 포맷을 사용함으로써 매우 세밀한 시간 간격으로 감사 기록을 관리할 수 있고, 여러 시간대에 걸쳐 사용되는 데이타베이스 환경에서 한층 유용한 정보를 얻을 수 있습니다.

 

GLOBAL_UID and PROXY_SESSIONID. 사용자 인증을 위해 Oracle Internet Directory와 같은 아이덴티티 관리 솔루션을 사용하는 경우, 데이타베이스가 사용자를 표시하는 방법도 조금 달라지게 됩니다. 예를 들어, 인증된 사용자가 (데이타베이스 사용자가 아닌) 엔터프라이즈 사용자인 경우를 생각해 봅시다. 이 경우 DBA_AUDIT_TRAIL 뷰의 USERNAME 컬럼에 enterprise userid가 표시되며, 따라서 해당 컬럼의 정보에 일관성이 없게 됩니다. Oracle Database 10g에서는 enterprise userid를 위한 GLOBAL_UID 컬럼을 별도로 제공하며, 이 컬럼을 이용하여 디렉토리 서버에 엔터프라이즈 사용자에 대한 정보를 질의할 수 있습니다.

 

멀티-티어 애플리케이션 환경에서 엔터프라이즈 사용자가 proxy user를 통해 데이타베이스에 접속하는 경우도 있습니다. 사용자에게 프록시 인증을 제공하는 명령이 아래와 같습니다:

 

alter user scott grant connect to appuser;

 

위 명령은 사용자 SCOTT가 APPUSER라는 proxy user를 통해 데이타베이스에 접속할 수 있게 합니다. 이 경우, Oracle 9i는 COMMENT_TEXT에 “PROXY”라는 값을 저장하지만 proxy user의 session id는 저장하지 않습니다. 10g에서는 PROXY_SESSIONID에 proxy session id를 저장합니다.

 

INSTANCE_NUMBER. Oracle Real Application Clusters (RAC) 환경에서 사용자가 실제로 어느 인스턴스에 연결되어 있는지 확인할 수 있다면 여러모로 도움이 될 것입니다. 10g에서는 INSTANCE_NUMBER 컬럼에 초기화 매개변수 파일에 지정된 해당 인스턴스의 인스턴스 번호를 저장합니다.

 

OS_PROCESS. Oracle9i와 그 이전 버전에서는 SID 값만을 저장하고 운영체제의 process id는 저장하지 않았습니다. 하지만 서버 프로세스의 OS process id가 Trace파일과 상호참조를 위해 필요한 경우가 있을 수 있습니다. 10g는 이를 위해 OS_PROCESS 컬럼을 제공합니다.

 

TRANSACTIONID. 이번에는 정말 중요한 정보를 소개할 차례입니다. 사용자가 아래와 같은 쿼리를 실행하는 경우를 가정해 봅시다:

 

update CLASS set size = 10 where class_id = 123;

commit;

 

위 명령은 트랜잭션으로서의 조건을 만족하며, 따라서 명령에 대한 감사 기록이 생성됩니다. 그렇다면, 실제로 생성된 감사 기록을 어떻게 확인할 수 있을까요? TRANSACTIONID 컬럼에 저장된 transaction id와 FLASHBACK_TRANSACTION_QUERY 뷰를 조인하면 됩니다. FLASHBACK_TRANSACTION_QUERY 뷰를 조회하는 간단한 예가 다음과 같습니다:

 

select start_scn, start_timestamp,

commit_scn, commit_timestamp, undo_change#, row_id, undo_sql

from flashback_transaction_query

where xid = '';

 

10g에서는 트랜잭션에 관련한 일반적인 정보 이외에도 undo change#와 rowid 등을 추가로 저장합니다. 트랜잭션 변경의 undo를 위한 SQL 쿼리는 UNDO_SQL 컬럼에, 변경된 로우의 rowid 값은 ROW_ID 컬럼에 저장됩니다.

 

System Change Number. 변경 전의 값을 저장하기 위해서 어떤 방법을 사용하고 계십니까? Oracle9i의 FGA를 이용하는 경우, flashback 쿼리를 통해 변경 전의 값을 알 수 있습니다. 이 작업을 위해서는 변경 작업에 사용된 SCN을 알고 있어야 하며, 이 정보는 감사 기록의 System Change Number 컬럼에 저장됩니다. 아래와 같은 쿼리를 사용하면 변경 전의 값을 확인할 수 있습니다.

 

select size from class as of SCN 123456

where where class_id = 123;

 

This will show what the user saw or what the value was prior to the change.

 

DB 감사 기능의 확장

 

처음에 우리가 제기했던 문제가 무엇인지 다시 상기해 보겠습니다. 표준 감사(standard auditing)을 통해 캡처되지 않던 정보들인 사용자가 실행한 SQL 구문과 바인드 변수들이 문제였습니다. Oracle Database 10g에서는 초기화 매개변수의 간단한 변경 작업만으로 이러한 목적을 달성할 수 있습니다. 매개변수 파일에 아래 항목을 삽입하면 됩니다.

 

audit_trail = db_extended

 

이 매개변수는 SQL텍스트와 컬럼에 사용된 변수 값의 기록을 활성화합니다. 이 기능은 10g 이전 버전에서는 지원되지 않았습니다.

 

트리거가 필요한 경우

 

롤백된 결과의 취소. 감사 기록은 원본 트랜잭션으로부터 파생된 autonomous transaction을 통해 생성됩니다. 그렇기 때문에 원본 트랜잭션이 롤백 되는 경우에도 감사 기록은 커밋됩니다.

 

간단한 예를 들어 설명해 보겠습니다. CLASS 테이블의 UPDATE 작업에 대한 감사를 수행하는 경우를 가정해 봅시다. 사용자가 SIZE 컬럼의 값을 20을 10으로 변경하는 구문을 실행한 뒤 아래와 같이 롤백했습니다:

 

update class set size = 10 where class_id = 123;

rollback

 

결국 SIZE 컬럼은 (10이 아닌) 20의 값을 갖게 됩니다. 하지만 감사 기록에는 (롤백이 수행되었음에도) 변경 작업이 수행된 것처럼 기록이 됩니다. 사용자가 롤백을 수행하는 빈도가 높은 경우, 이러한 현상으로 인해 곤란한 문제가 생길 수 있습니다. 이러한 경우라면 커밋된 변경내역만을 캡처하기 위해 트리거를 사용할 수 밖에 없습니다. 즉 사용자 정의된 audit trail에 기록하도록 CLASS테이블에 대한 트리거가 있다면, CLASS 테이블에 대한 변경 작업이 롤백 되는 경우, 설정된 트리거 역시 롤백 됩니다.

 

변경 전 값의 캡처. 오라클이 제공하는 감사 기록은 변경 이전/이후의 값을 제공하지 않습니다. 예를 들어, 위에서 예로 든 변경 작업에 대한 감사기록은 실행된 구문과 SCN 넘버를 저장하고 있지만 변경 전 값(20)은 저장하지 않습니다. SCN 넘버를 이용한 flashback 쿼리를 통해 이전 값을 확인할 수 있지만, 이것도 해당 정보가 undo 세그먼트에 저장되어 있는 경우에만 가능합니다. 해당 정보가 undo_retention period 설정된 보관 기간을 초과한 경우, 이전 값을 조회할 수 있는 방법은 없습니다. 트리거를 이용하면 undo_retention period의 설정값에 관계없이 변경전 값을 영구적으로 저장할 수 있습니다.

 

통합 감사 기록

 

FGA와 표준 감사(standard auditing) 기능은 서로 유사한 형태의 정보를 제공하므로, 이 두 가지를 함께 사용함으로써 매우 다양한 정보를 얻을 수 있습니다. Oracle Database 10g는 두 종류의 정보를 통합한 DBA_COMMON_AUDIT_TRAIL 뷰를 제공합니다. 이 뷰는 DBA_AUDIT_TRAIL 뷰와 DBA_FGA_AUDIT_TRAIL 뷰의 UNION ALL로 구성됩니다. (하지만 두 감사 작업이 제공하는 정보의 성격에 분명한 차이가 있는 것도 사실입니다.)

 

결론

 

10g로 오면서, 감사 기능은 단순한 “액션 레코더(action recorder)” 또는 “패스트 리코딩 메커니즘(fast-recording mechanism)”에서 매우 상세한 수준의 사용자 액티비티를 저장하는 수단으로 변화되었으며, 이 기능을 활용하여 수작업으로 수행되던 트리거 기반 감사 작업을 최소화할 수 있습니다. 또 표준 감사(standard auditing)과 FGA의 감사 결과를 통합함으로써, 데이타베이스 액세스에 대한 추적이 한층 용이해졌습니다.