oracle 10g audit_trail 사용

9i부터 오라클에 자체 감사 기능이 추가 되었다. 다음은
http://www.oracle-base.com/articles/10g/DatabaseSecurityEnhancements10g.php#uniform_audit_trail
에서 참고한 자료다.

쉬운 영문 이기 때문에 번역없이 원문을 그대로 남겨 놓는다.

Uniform Audit Trail

The DBA_COMMON_AUDIT_TRAIL view has been added to display the complete audit trail:

• DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
• DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
• DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.

Audit Trail Contents

Several fields have been added to both the standard and fine-grained audit trails:

• EXTENDED_TIMESTAMP - A more precise value than the exising TIMESTAMP column.
• PROXY_SESSIONID - Proxy session serial number when an enterprise user is logging in via the proxy method.
• GLOBAL_UID - Global Universal Identifier for an enterprise user.
• INSTANCE_NUMBER - The INSTANCE_NUMBER value from the actioning instance.
• OS_PROCESS - Operating system process id for the oracle process.
• TRANSACTIONID - Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
• SCN - System change number of the query. This column can be used in flashback queries.
• SQL_BIND - The values of any bind variables if any.
• SQL_TEXT - The SQL statement that initiated the audit action.

The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL=DB_EXTENDED initialization parameter is set:

-- Configure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;
AUDIT ALL BY scott BY ACCESS;

-- Perform an auditable action.
CONN scott/tiger
UPDATE emp SET ename = ename;

-- Check the audit trail.
SELECT sql_text FROM dba_common_audit_trail;

SQL_TEXT
----------------------------
UPDATE emp SET ename = ename

1 row selected.

-- Unconfigure extended auditing.
CONN sys/password AS SYSDBA
ALTER SYSTEM SET audit_trail=none SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
TRUNCATE TABLE aud$;
TRUNCATE TABLE fga_log$;

SELECT os_username, username, userhost, action, action_name, comment_text,
       SCN, sql_bind, sql_text
  FROM dba_audit_trail a

 

성능 테스트는 아직 못해 봤지만 개발 DB 사용시 유용한 기능인듯 싶다.