태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'DBMS_FGA'에 해당되는 글 1건

  1. 2010.10.18 FGA(Fined Grained Auditing) 활용 예제 (1)

FGA(Fined Grained Auditing) 활용 예제

오라클/관리 2010.10.18 16:08
며칠 전 지인으로부터 다급한(?) 문의가 왔습니다.
  • 특정 테이블들에 대해 SELECT, INSERT, UPDATE, DELETE 문장을 어떤 프로그램(가령 SQL*Plus? 토드? 웹?)이 언제 수행했는지 기록할 수 있는 방법이 있을까요?
위와 같은 요구 사항을 만족시키는 가장 좋은 방법은 오라클에 제공하는 감사 기능, 즉 AUDIT를 사용하는 것이겠지요? 그런데 프로그램 종류를 알아야 한다는 요구 사항에서 문제가 생겼습니다.

오라클이 제공하는 AUDIT 데이터에는 프로그램 정보는 제공되지 않습니다. 한가지 방법은 AUDIT가 제공하는 데이터와 AWR이 제공하는 ASH데 대한 히스토리 데이터인 DBA_HIST_ACTIVE_SESS_HISTORY 뷰를 이용해서 프로그램을 알아내는 것인데, 번거롭고 100% 확실한 정보를 제공할 수 없다는 점이 제약이 됩니다.

이런 경우에 이용할 수 있는 것이 FGA(Fined Grained Auditing)입니다. 오라클의 최신 버전에서는 모든 종류의 SQL 문장에 대해 FGA를 수행할 수 있습니다. FGA가 수행되면 그 결과가 SYS.FGA_LOG$ 테이블에 저장되고, DBA_FGA_AUDIT_TRAIL 뷰를 통해 볼 수 있습니다. 단, FGA를 이용한 Audit 데이터에도 프로그램 정보는 제공되지 않습니다. 대신 FGA가 제공하는 핸들러(Handler) 기능을 이용할 수 있습니다.

Audit 이벤트가 발생할 때 사용자가 직접 정의한 핸들러 프로시저를 호출할 수 있는 기능이 제공되는데, 흔히 DBA에게 Alert를 수행할 목적 등으로 활용하도록 설계된 것입니다. 이 기능을 이용해서 프로그램 정보를 포함한 2차 Auditing을 수행하도록 할 수 있습니다.

아래에 간단한 예제가 있습니다.

1. 테스트 버전은 11.2.0.1입니다.

SQL> -- version
SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2. AUDIT 대상이 되는 사용자는 TPACK이고, 대상 테이블은 T1입니다.
SQL> -- audited user is tpack
SQL> conn tpack/tpack@ukja1120
Connected.

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1 select level from dual connect by level <= 1000;

1000 rows created.
3. AUDIT를 수행할 유저는 UKJA이고, 2차 AUDIT 결과를 T_AUD_LOG 테이블에 저장하기로 합니다. 다음과 같이 핸들러 프로시저를 가지는 FGA 정책(Policy)를 만듭니다.
SQL> -- auditing user = ukja
SQL> conn ukja/ukja@ukja1120
Connected.

SQL> create table t_aud_log
  2  as
  3  select
  4  	sid,
  5  	username,
  6  	program,
  7  	machine,
  8  	rpad('x',30) as table_name,
  9  	rpad('x',10) as stmt_type,
 10  	sysdate as log_date
 11  from v$session
 12  where 1 = 0
 13  ;

Table created.

SQL> -- create "select" event handler
SQL> -- proc_audit_select, proc_audit_update, proc_audit_insert, proc_audit_delete 이렇게 만들어서 사용하시면 모든 종류의 접근을 로깅할 수 있습니다.
SQL> create or replace procedure proc_audit_select(
  2  	object_schema varchar2,
  3  	object_name varchar2,
  4  	policy_name varchar2)
  5  as
  6  	pragma autonomous_transaction;
  7  begin
  8  
  9  	insert into t_aud_log(
 10  		sid,
 11  		username,
 12  		program,
 13  		machine,
 14  		table_name,
 15  		stmt_type,
 16  		log_date)
 17  	select
 18  		sid,
 19  		username,
 20  		program,
 21  		machine,
 22  		object_name,
 23  		'SELECT',
 24  		sysdate
 25  	from
 26  		v$session s
 27  	where
 28  		s.sid = userenv('sid')
 29  	;
 30  
 31  	commit;
 32  
 33  end;
 34  /

Procedure created.

SQL> show errors
No errors.

SQL> -- add policy
SQL> begin
  2  	dbms_fga.add_policy(
  3  		object_schema => 'TPACK',
  4  		object_name => 'T1',
  5  		policy_name => 'T1_AUDIT_SELECT',
  6  		statement_types => 'SELECT',
  7  		handler_schema => 'UKJA',
  8  		handler_module => 'PROC_AUDIT_SELECT'
  9  	);
 10  end;
 11  /

PL/SQL procedure successfully completed.
위와 같이 수행하면 TPACK.T1 테이블에 대해 SELECT가 발생할 때마다 AUDIT가 이루어지고 핸들러 프로시저인 PROC_AUDIT_SELECT가 수행될 것입니다.

4. TPACK.T1 테이블에 대해 SELECT를 수행하고 성공적으로 AUDIT가 이루어졌는지 확인해봅니다.

SQL> -- now select table
SQL> conn tpack/tpack@ukja1120
Connected.

SQL> select count(*) from t1;

  COUNT(*)
----------
      1000

SQL> -- check wheter t_aud_log is being populated
SQL> conn ukja/ukja@ukja1120
Connected.

SQL> set serveroutput on
SQL> exec print_table('select * from t_aud_log');
SID                           : 139
USERNAME                      : TPACK
PROGRAM                       : sqlplus.exe    -- 이 정보때문에 이 고생을!!!
MACHINE                       : POWER_GROUP\UKJAX
TABLE_NAME                    : T1
STMT_TYPE                     : SELECT
LOG_DATE                      : 2010/10/18 15:41:49
-----------------

PL/SQL procedure successfully completed.

SQL> -- check dba_fga_audit_trail and fga_log$
SQL> conn sys/oracle@ukja1120 as sysdba
Connected.

SYS@ukja1120> set serveroutput on
SYS@ukja1120> exec ukja.print_table('select * from dba_fga_audit_trail');
SESSION_ID                    : 8860593
TIMESTAMP                     : 2010/10/18 15:41:49
DB_USER                       : TPACK
OS_USER                       : UKJAX\Administrator
USERHOST                      : POWER_GROUP\UKJAX
CLIENT_ID                     :
ECONTEXT_ID                   :
EXT_NAME                      : UKJAX\Administrator
OBJECT_SCHEMA                 : TPACK
OBJECT_NAME                   : T1
POLICY_NAME                   : T1_AUDIT_SELECT
SCN                           : 46448666
SQL_TEXT                      : select count(*) from t1
SQL_BIND                      :
COMMENT$TEXT                  :
STATEMENT_TYPE                : SELECT
EXTENDED_TIMESTAMP            : 18-OCT-10 03.41.49.968000 PM +09:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 3188:5036
TRANSACTIONID                 :
STATEMENTID                   : 8
ENTRYID                       : 1
OBJ_EDITION_NAME              :
DBID                          : 864753741
-----------------

PL/SQL procedure successfully completed.
물론 테스트가 끝나면 불필요한 데이터는 삭제!
SYS@ukja1120> delete from fga_log$;

1 row deleted.

SYS@ukja1120> -- drop policy
SYS@ukja1120> conn ukja/ukja@ukja1120

SQL> begin
  2  	dbms_fga.drop_policy(
  3  		object_schema => 'TPACK',
  4  		object_name => 'T1',
  5  		policy_name => 'T1_AUDIT_SELECT'
  6  	);
  7  end;
  8  /

PL/SQL procedure successfully completed.
조금 번거롭지만, 목적한 바를 성공적으로 달성할 수 있었습니다.

오라클이 제공하는 FGA 기능은 매우 유연하고 강력해서 AUDIT와 관련된 어지간한 요구 사항을 대부분 만족시킬 수 있습니다. 단, 오라클 데이터베이스 내에서 추가적인 작업이 발생하기 때문에 오버헤드가 발생할 우려가 있습니다. 이런 이유 때문에 써드 파티가 제공하는 AUDIT 툴을 구매하는 경우도 많긴 하지만요.

복잡한 AUDIT 요구 사항이 발생할 때 위의 방법을 활용해보시는 것도 좋은 경험이 되겠습니다.

저작자 표시
신고

'오라클 > 관리' 카테고리의 다른 글

FGA(Fined Grained Auditing) 활용 예제  (1) 2010.10.18
tags : , ,
Trackback 0 : Comment 1
  1. 연두곰 2014.01.27 17:46 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment

티스토리 툴바