Microsoft Word - 기술노트[22회]_AUDIT_ver1.3

Transcription

1 Goodus 기술노트 [22 회 ] AUDIT Author 고형덕, 노재구 Creation Date Last Updated Version 1.1 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 노재구 문서최초작성 2 3

2 Contents 1. Audit Audit 란? Database Audit Overview 예제 1 (AUDIT_TRAIL=DB) 예제 2 (AUDIT_TRAIL=DB_EXTENDED, Oracle Database 10g New Feature) Controlling the Growth and Size of the Standard Audit Trigger 를이용한 Audit FGA(Fine-Grained Auditing) Fine-Grained Auditing 이란? Fine-Grained Auditing 특징 Oracle 9i Database FGA 예제 Oracle Database 10g FGA 예제 1 STATEMENT_TYPE, AUDIT_TRAIL Oracle Database 10g FGA 예제 2 AUDIT_COLUMN_OPTS Database Auditing과 FGA의비교 부록 Trigger를이용한 DML Auditing AUDIT_UTIL PACKAGE SQL Tips for Developer Oracle 소식 Pro-Active Tuning Service

3 Audit 1.1. Audit 란? Auditing 기능은사용자의행동을감시하거나데이터베이스에관한통계자료를얻는목적으로사용된다. Auditing 기능을사용함으로써누가어떠한테이블을언제사용하고, 언제어떤작업을하는지를기록할수있다. 데이터베이스사용자는일정한권한을부여받아데이터를조작 (Insert, Update, Delete) 하거나조회 (Select) 할수있다. 권한을받은사용자는주어진권한을이용하여원래의목적에맞지않는, 접근해서는안되는중요한데이터를조회하거나변경할수도있다. 이러한일들을막기위해서사용자가데이터를조회, 조작할때마다이에대한정보를기록하여, 누가언제, 무엇을했는지확인하는방법이필요하다. 이것을 감사 (Audit) 라고한다 Database Audit Overview 데이터베이스에영향을끼치는작업을감시하거나특정데이터베이스작업에대한데이터를모니터하고수집한다. 이벤트에대한정보는 Audit Trail 에저장된다. - Database Aduting 을통해데이터베이스의모든연결에대해감사가가능하다. 승인되지않은사용자가테이블에서데이터를삭제하고있는경우, DBA 는데이터베이스에있는테이블에서의행에대한성공적인삭제여부를감시하는데사용된다. - 특정데이터베이스작업을모니터링하고, 그에대한데이터를모으는데사용될수있다. 예를들어, DBA 는갱신중인테이블, 수행한논리적 I/O 횟수및시스템이바쁜시간에연결한동시 User 수에대한통계를수집할수있다. - DBA 에의해활성화또는비활성화할수있다. - 컬럼의값에대한기록은불가능하다. 그림 1 Database Audit 1. Enable/Disable Database Auditing (Setting Audit Parameter) - 데이터베이스감사 (Database Auditing) 설정은 DBA 에의해가능하다. Init 파라메터중 AUDIT_TRAIL 을이용하여데이터베이스감사기능을활성화또는비활성화시킬수있다. AUDIT_TRAIL Value TRUE / DB DB_EXTENDED Description Audit 를활성화함. Audit 결과는 SYS.AUD$ 에저장 Audit Trail 정보에 SQLBIND, SQLTEXT 추가됨. (Oracle Database 10g New Feature) OS OS 에서허용하는경우활성화, Audit 결과는파일로저장됨. FALSE / NONE Audit 를비활성화함. 표 1 - AUDIT_TRAIL Parameter Values - 3 -

4 2. Specify Audit Options - Audit 명령을사용하여 Audit 할명령, User, Object, Privilege 를지정한다. Audit Record 가발생할때마다생성할지, Session 당한번생성할지를결정할수있다. 즉, 다음과같은 3 가지종류가있다. Statement Auditing SQL 명령문의유형에따른 Auditing 설정 ex) AUDIT TABLE BY SCOTT BY ACCESS WHENEVER SUCCESSFUL; : scott 유저가테이블에관련된명령 (create table, drop table 등 ) 이성공한경우기록된다. Privilege Auditing 사용되는 PRIVILEGE 에따른 Auditing 설정 ex) AUDIT CREATE TABLE BY SCOTT BY SESSION; : scott 유저가 create table 권한이필요한명령을수행시기록 Object Auditing 특정스키마의개체의명령문에대한 Auditing 설정 ex) AUDIT ALL ON SCOTT.EMP; : scott.emp 테이블에대한모든명령 (select, insert, update, delete, drop 등 ) 에관한사항이기록된다. 설정된 AUDIT 기능은 NOAUDIT 명령으로제거할수있다. ex) NOAUDIT ALL ON SCOTT.EMP; 3. Execute Command - 사용자가 SQL 또는 PL/SQL 문을실행할때 Server Process 는 Audit 옵션을검색하여실행중인 Statement 가감사대상에포함되는지여부를먼저결정한다. 4. Generate Audit Trail - audit_trail 파라메터에정의된값에따라 OS 의파일또는데이터베이스내에 SYS.AUD$ 에 Audit Trail 레코드를생성한다. 이작업은사용자의 Transaction 과무관하므로 Transaction Rollback 이수행될지라도 Audit Trail 레코드는그대로유지된다. 단, Audit Trail 레코드는구문의 execute 단계에서생성되므로 Parsing 단계에서오류가발생하면생성되지않는다. 5. Review Audit Information - Audit 를통해생성된정보는아래의 Audit Trail Data Dictionary 뷰를통해확인할수있다. OS 에생성된 Audit Trail 인경우 OS Utility 를사용하여확인한다. 이정보를토대로의심이가는작업을확인하거나데이터베이스작업에대한모니터링할수있다. View Description STMT_AUDIT_OPTION_MAP Audit 옵션타입코드에대한정보를보여준다. AUDIT_ACTIONS Audit Trail Action 타입코드에대한설명을포함한다. ALL_DEF_AUDIT_OPTS Object 가생성될대적용될수있는기본적인 Object-auditing 옵션을보여준다. DBA_STMT_AUDIT_OPTS 시스템과사용자에걸쳐현재시스템의 Audit 옵셥을보여준다. DBA_PRIV_AUDIT_OPTS 시스템과사용자에걸쳐 Audit 되고있는현재시스템권한을보여준다. DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS DBA_AUDIT_TRAIL USER_AUDIT_TRAIL DBA_AUDIT_OBJECT USER_AUDIT_OBJECT 모든 Object 에대한 Audit 옵션을보여준다. USER 뷰는현재사용자가소유한모든 Object 의 Audit Option 을보여준다. 모든 Audit Trail 엔트리를리스트한다. USER 뷰는현재사용자에관련된 Audit Trail 엔트리를보여준다. 시스템의모든 Object 에대한 Audit Trail 레코드를포함한다. USER 뷰는현재사용자가접근할수있는 Object 와관련된구문에대한 Audit Trail 레코드를리스트한다

5 DBA_AUDIT_SESSION USER_AUDIT_SESSION DBA_AUDIT_STATEMENT USER_AUDIT_STATEMENT CONNECT 와 DISCONNECT 에관련된모든 Audit Trail 레코드를리스트한다. USER 뷰는현재사용자에대한연결설정과해제에관련된모든 Audit Trail 레코드를리스트한다. 데이터베이스전반적으로 GRANT, REVOKE, AUDIT, NOADUIT, ALTER SYSTEM 구문과관계되는 Audit Trail 레코드를리스트한다. USER 뷰는현재사용자와관련된내용만을보여준다. DBA_AUDIT_EXISTS AUDIT EXISTS 와 AUDIT NOT EXISTS 와관련된 Audit Trail 엔트리를리스트한다. 표 2 - Audit Trail Data Dictionary Views 1.3. 예제 1 (AUDIT_TRAIL=DB) SQL*Plus: Release Production on Thu Aug 2 15:25: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect /as sysdba SQL> show parameter audit_trail NAME TYPE VALUE audit_trail string DB -- Object Audit 설정 SQL> select owner, object_name, object_type, sel from dba_obj_audit_opts 2 where object_name = 'EMP' and owner = 'SCOTT'; OWNER OBJECT_NAME OBJECT_TYPE SEL SCOTT EMP TABLE -/- SQL> audit select on scott.emp by session whenever successful; Audit succeeded. SQL> select owner, object_name, object_type, sel from dba_obj_audit_opts 2 where object_name = 'EMP' and owner = 'SCOTT'; OWNER OBJECT_NAME OBJECT_TYPE SEL SCOTT EMP TABLE S/S SQL> select * from dba_obj_audit_opts 2 where object_name = 'EMP' and owner = 'SCOTT'; OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- * dba_obj_audit_opts 는 object에설정할수있는각 audit option을 column으로하고있으며, 내용은 [A/S]/[A/S] 의형식을갖는다. / 의앞부분은 successful일경우, 뒷부분은 not successful일경우를표시한다. * A는 by access, S는 by session을의미한다. by access는해당명령이내려질때마다정보를기록하고, by sessoin은접속된세션에대하여하나의레코드만생성한다

6 SQL> select count(*) from sys.aud$; COUNT(*) SQL> connect scott/tiger SQL> select * from emp where sal > 3000; 인사급여테이블에 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO KING PRESIDENT 17-NOV SQL> connect / as sysdba SQL> select count(*) from sys.aud$; COUNT(*) SQL> select os_username, username, timestamp, owner, obj_name, action_name, ses_actions, returncode 2 from dba_audit_object; OS_USERNAME USERNAME TIMESTAMP OWNER OBJ_NAME ACTION_NAME SES_ACTIONS RETURNCODE oracle SCOTT 02-AUG-07 SCOTT EMP SESSION REC S * returncode 가 0 인경우는 SUCCESS 임을의미한다. * by session 로설정한경우 audit 를설정한경우 action_name 은 SESSION REC 으로표시된다. * ses_actions columns 은총 13 가지의 action(alter, audit, comment, delete, grant, index, insert, lock, rename, select, update, reference, execute) 에대한 Audit 정보를포함한다. 각 Action 에대해모두성공하였다면 S 를실패하였다면 F 로표현되며성공과실패가모두있었다면 B 로표현된다. * by access 로설정한경우 action_name 컬럼에해당 action( 예 : delete, insert) 등이바로기술된다. -- Private Audit 설정 SQL> select * from dba_priv_audit_opts; USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE CREATE ANY RULE BY ACCESS NOT SET ALTER ANY RULE BY ACCESS NOT SET EXECUTE ANY RULE BY ACCESS NOT SET SQL> audit create session by scott; Audit succeeded. SQL> select * from dba_priv_audit_opts; USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE SCOTT CREATE SESSION BY ACCESS BY ACCESS CREATE ANY RULE BY ACCESS NOT SET ALTER ANY RULE BY ACCESS NOT SET - 6 -

7 EXECUTE ANY RULE BY ACCESS NOT SET -- connect session SQL> connect scott/tiger -- disconnect session SQL> disconnect Disconnected from Oracle9i Enterprise Edition Release bit Production With the Partitioning option JServer Release Production SQL>! date Thu Aug 2 18:09:24 KORDT 2007 SQL> connect /as sysdba SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> select os_username, username, timestamp, action_name, logoff_time, logoff_lread, logoff_pread 2 from dba_audit_session 3 where username = 'SCOTT'; OS_USERNAME USERNAME TIMESTAMP ACTION_NAME LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD oracle SCOTT :08:56 LOGOFF :08: 다른쪽터미널에서 Scott 유저로접속하고다시조회하면, SQL> select os_username, username, timestamp, action_name, logoff_time, logoff_lread, logoff_pread 2 from dba_audit_session 3 where username = 'SCOTT'; OS_USERNAME USERNAME TIMESTAMP ACTION_NAME LOGOFF_TIME LOGOFF_LREAD LOGOFF_PREAD oracle SCOTT :08:56 LOGOFF :08: oracle SCOTT :55:59 LOGON -- Statement Aduit 설정 -- 존재하지않는오브젝트에대한구문을실행시에 Audit Trail 작성 SQL> audit not exists by test; SQL> select * from dba_stmt_audit_opts ; USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE SCOTT NOT EXISTS BY ACCESS BY ACCESS SQL> conn scott/tiger SQL> grant select on not_exist_table to tester; grant select on not_exist_table to tester * ERROR at line 1: ORA-00942: table or view does not exist SQL> select os_username,username,timestamp,obj_name,action_name, 2 obj_privilege, grantee - 7 -

8 3 from dba_audit_statement; OS_USERNAME USERNAME TIMESTAMP OBJ_NAME ACTION_NAME OBJ_PRIVILEGE GRANTEE oracle SCOTT 04-AUG-07 NOT_EXIST_TABLE GRANT OBJECT Y TESTER * 3 가지타입의 Audit Trail 이생성되는예제를보았듯이누가언제무슨작업을하겨고했는지알수있다. 하지만, 위의결과에서는어떤구문을실행했을때 Auditing 되었는지정확히알수가없고, Object Audit 에서볼수있듯이어떤데이터를조회했는지알수가없다 예제 2 (AUDIT_TRAIL=DB_EXTENDED, Oracle Database 10g New Feature) SQL> show parameter audit_trail NAME TYPE VALUE audit_trail string DB_EXTENDED SQL> connect /as sysdba SQL> audit insert on scott.emp by access; Audit succeeded. SQL> select owner, object_type, object_name, ins from dba_obj_audit_opts 2 where owner = 'SCOTT' and object_name = 'EMP'; OWNER OBJECT_TYPE OBJECT_NAME INS SCOTT TABLE EMP A/A SQL> desc dba_audit_object Name Null? Type OS_USERNAME VARCHAR2(255) USERNAME VARCHAR2(30) USERHOST VARCHAR2(128) TERMINAL VARCHAR2(255) client pc 명 : 사용자를짐작할수있음 TIMESTAMP DATE OWNER VARCHAR2(30) OBJ_NAME VARCHAR2(128) ACTION_NAME VARCHAR2(28) NEW_OWNER VARCHAR2(30) NEW_NAME VARCHAR2(128) SES_ACTIONS VARCHAR2(19) COMMENT_TEXT VARCHAR2(4000) SESSIONID NOT NULL NUMBER ENTRYID NOT NULL NUMBER STATEMENTID NOT NULL NUMBER RETURNCODE NOT NULL NUMBER PRIV_USED VARCHAR2(40) CLIENT_ID VARCHAR2(64) - 8 -

9 ECONTEXT_ID SESSION_CPU EXTENDED_TIMESTAMP PROXY_SESSIONID GLOBAL_UID INSTANCE_NUMBER OS_PROCESS TRANSACTIONID SCN SQL_BIND SQL_TEXT VARCHAR2(64) NUMBER TIMESTAMP(6) WITH TIME ZONE NUMBER VARCHAR2(32) NUMBER VARCHAR2(16) RAW(8) NUMBER NVARCHAR2(2000) NVARCHAR2(2000) * ORACLE Database 10g 에서 AUD$ 테이블에는위 DBA 뷰에서보듯이 SQL_BIND 와 SQL_TEXT 두컬럼이추가되었다. SQL> select username, owner, obj_name, action_name, ses_actions, returncode, timestamp, 2 sql_bind, sql_text 3 from dba_audit_object; no rows selected SQL> conn scott/tiger SQL> variable empno number; SQL> variable ename varchar2(8); SQL> begin 2 :empno := 9999; 3 :ename := 'TESTER'; 4 end; 5 / PL/SQL procedure successfully completed. SQL> insert into emp values (:empno, :ename, 'MANAGER', 7499, SYSDATE, 3000, '', 20); 1 row created. SQL> commit; Commit complete. SQL> connect / as sysdba SQL> select username, owner, obj_name, action_name, ses_actions, returncode, timestamp, 2 sql_bind, sql_text 3 from dba_audit_object; USERNAME OWNER OBJ_NAME ACTION_NAME SES_ACTIONS RETURNCODE TIMESTAMP SQL_BIND SQL_TEXT SCOTT SCOTT EMP INSERT 0 05-AUG-07 #1(4):9999 #2(6):TESTER insert into emp values (:empno, :ename, 'MANAGER', 7499, SYSDATE, 3000, '', 20) - 9 -

10 1.5. Controlling the Growth and Size of the Standard Audit Audit Trail 이쌓이는 SYS.AUD$ 테이블은 SYSTEM 테이블스페이스생성된다. 그러므로 SYS.AUD$ 테이블에데이터가쌓임으로써 SYSTEM 테이블에대하여경합등부담을줄수있다. 예를들어, CREATE SESSION 에대한 AUDITING 을수행할때세션의사용량에따라엄청난데이터가축척될수있다. 그러므로불필요한 AUDIT 의사용은데이터베이스의성능을저하시킬수있다. 그러므로불필요한 Audit 기능은제한하고, Audit 하는기간동안 SYS.AUD$ 테이블의크기를적절히유지하도록해야한다. Purging Audit Record from the Audit Trail Archiving Audit Trail Information Reducing the Size of the Audit Trail 제어방법에대한예시는다음과같다. -- Purging Audit Record from the Audit Trail DELETE FROM SYS.AUD$; DELETE FROM SYS.AUD$ WHERE obj$name = EMP ; TRUNCATE TABLE SYS.AUD$; -- Archiving Audit Trail Information INSERT INTO table SELECT FROM SYS.AUD$ exp '"sys/sys as sysdba" ' file=aud.dmp tables=aud$; -- Reducing the Size of the Audit Trail : Archiving 과 Purging 을이용한관리 1. SYS.AUD$ 에대한백업을수행한다. - Export 등 2. sys 유저로접속한다. 관리자에의한수행 3. TRUNCATE TABLE SYS.AUD$; (HWM 의이동으로 extent 할당을해제하여테이블내공간을줄임.) 4. Audit Trail Record 가많이생성되면 1 번부터재수행한다 Trigger 를이용한 Audit 앞서말했듯이 10g 이전의 Audit 의기능에는바운드변수 (SQL_BIND) 와구문 (SQL_TEXT) 를알수있는방법이없었다. 그리고 Oracle 9i Database 에서 FGA(Find-Grained Auditing) 을사용하면좀더다양하고세부적인 Auditing 이가능하다. 우선, FGA 를사용하지않고구현할수있는방법을알아보겠다. 그리고 FGA 는뒤에서설명하기로하겠다. 낮은버전의오라클 (8i 이하 ) 이나오라클이제공하지않는 AUDITING 을구현하기위해서 Trigger 를이용할수있다. 이는 Trigger 를사용하여데이터의액세스나조작이일어나기전에그내용을관리자가관리하는테이블로저장하는방식이다. 단순한 AUDITING 또는 AUDIT 로인한부담을줄이기위해데이터베이스사용자에의해개발된 AUDIT 기능이라고보면될것이다. 트리거를이용하여사용자에의한트랜잭션에대한로그를남길수있다. 하지만자칫 Trigger 의내용으로인해실제구문의수행시간및리소스사용에영향을최소한으로줄여야한다. 다음은트리거를이용하여특정테이블의변경된내용을 Auditing 하는예제이다

11 1. AUDIT 테이블생성. connect / as sysdba CREATE TABLE emp_audit ( old_empno NUMBER(4), old_ename VARCHAR2(10), old_job VARCHAR2(9), old_mgr NUMBER(4), old_hiredate DATE, old_sal NUMBER(7,2), old_comm NUMBER(7,2), old_deptno NUMBER(2), new_empno NUMBER(4), new_ename VARCHAR2(10), new_job VARCHAR2(9), new_mgr NUMBER(4), new_hiredate DATE, new_sal NUMBER(7,2), new_comm NUMBER(7,2), new_deptno NUMBER(2), changed_by VARCHAR2(8), change_type CHAR(1), timestamp DATE ); Table created. 2. TRIGGER 생성. CREATE OR REPLACE TRIGGER LogEmpChanges BEFORE INSERT OR DELETE OR UPDATE ON scott.emp FOR EACH ROW DECLARE v_changetype CHAR(1); BEGIN /* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */ IF INSERTING THEN v_changetype := 'I'; ELSIF UPDATING THEN v_changetype := 'U'; ELSE v_changetype := 'D'; END IF; INSERT INTO emp_audit ( change_type, changed_by, timestamp, old_empno, old_ename, old_job, old_mgr, old_hiredate, old_sal, old_comm, old_deptno, new_empno, new_ename, new_job, new_mgr, new_hiredate, new_sal, new_comm, new_deptno) VALUES ( v_changetype, USER, SYSDATE, :old.empno, :old.ename, :old.job, :old.mgr, :old.hiredate, :old.sal, :old.comm, :old.deptno, :new.empno, :new.ename, :new.job, :new.mgr, :new.hiredate, :new.sal, :new.comm, :new.deptno); END LogEmpChanges; / PL/SQL procedure successfully completed

12 connect scott/tiger INSERT INTO emp VALUES ( 9999, 'TESTER', 'CLERK', 7782, SYSDATE, 1000, 0, 10); COMMIT; UPDATE emp SET comm = 300 WHERE empno = 9999; COMMIT; DELETE FROM emp WHERE empno = 9999; COMMIT; connect /as sysdba SELECT OLD_EMPNO, OLD_ENAME, OLD_COMM, NEW_EMPNO, NEW_ENAME, NEW_COMM, CHANGED_BY, CHANGE_TYPE, TIMESTAMP FROM EMP_AUDIT; OLD_EMPNO OLD_ENAME OLD_COMM NEW_EMPNO NEW_ENAME NEW_COMM CHANGED_BY CH TIMESTAMP TESTER 0 SCOTT I 01-AUG TESTER TESTER 300 SCOTT U 01-AUG TESTER 300 SCOTT D 01-AUG-07 * Before Trigger 로인하여사용자가 DML 작업을수행할때해당테이블의변경전후의 column 값및변경된시간등을 emp_audit 테이블에기록한다. * 부록 1. 에는위와같은소스를자동생성해주는샘플 Package 를작성해보았다. 해당테이블의정보를추출하여 audit trail record 를담을테이블과이벤트를처리할트리거를자동생성하고제거할수있는패키지이다. 다시말해, 편의상구문을자동생성하고, 실행시키는루틴을포함시켜놓았다. 오라클의제공하는 Audit 기능을대체할수있도록응용해보기바란다

13 2. FGA(Fine-Grained Auditing) 2.1. Fine-Grained Auditing 이란? 데이터베이스감사 (Database Auditing) 기능을사용하여특정스키마내의테이블에 SELECT 에관한 Auditing 을설정하였을경우해당테이블의조회관련된모든수행기록이 Audit Trail Record 의형태로저장될것이다. 이런경우실제로원하지않는결과까지도모두기록되어 Audit Trail 의양은상당히많아질것이다. 그리고이들데이터에서특정컬럼에대한조회내역직접적으로사용된 SELECT 문을찾기는어려울것이다. FGA(Fine-Grained Auditing) 은이러한문제에대한해결책을제시했다. Oracle 9i Database 에등장한 FGA 는특정데이터를조회하는경우에만감사가가능하도록설정할수있다. Oracle Database 10g 에서는 Oracle 9i Database 에서 SELECT 만가능했던 FGA 의영역을 DML 까지확장하며완성되었다 Fine-Grained Auditing 특징 더상세한레벨의감사기능을제공한다. 선택적인감사를위한조건으로 SQL 의 WHERE 절을기반으로한다. 값에의한감사뿐만아니라특정컬럼의참조또는액세스여부에대해서도 Auditing 이가능하다. DBMS_FGA 패키지를이용하여활성화 / 비활성화한다. CBO(Cost Based Optimizer) 인경우에정상적으로작동한다. ( 인스턴스레벨의 CBO 설정및테이블에대한분석 (Analyze) 이되어있어야하며 SQL 에힌트가없어야한다.) Oracle Database 9i 에서는 SELECT 문에서만가능하며, Oracle Database 10g 에서는 Insert, Update, Delete, Merge 문에대해서도가능하다. Oracle Database 10g 에서는컬럼에대한옵션 (audit_column_opts) 지정으로 DBMS_FGA.ALL_COLUMNS / DBMS_FGA.ANY_COLUMNS 를사용할수있다. FGA 에서 Audit Trail Record 는 SYS.FGA_LOG$ 에저장된다. Parameter Name Data Types Description object_schema VARCHAR2 설정하고자하는스키마명 object_name VARCHAR2 설정하고자하는오브젝트명 ( 테이블, 뷰명 ) policy_name VARCHAR2 Policy 명 (Unique) audit_condition VARCHAR2 상세감사조건, 스키마오브젝트에대한논리적인데이터그룹에상응하는 WHERE절지칭 SQL문의조건절에해당하는부분이명시적묵시적으로이조건에만족할경우 Audit Trail을생성함. 조회결과의일부가조건에해당되는경우도 Audit Trail을생성함 audit_column VARCHAR2 감사대상이되는컬럼을지정함. Oracle 9i Database에서는한개의컬럼만지정이가능했으나, Oracle Database 10g부터는하나이상의컬럼의지정이가능. handler_schema VARCHAR2 FGA에의해 Audit Trail이생성될때실행시킬프로시저의소유자 handler_module VARCHAR2 FGA에의해 Audit Trail이생성될때실행시킬프로시저생성패턴 : PROCEDURE <fname>{ obect_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2} AS enable BOOLEAN FGA 활성화여부 TRUE로지정해야활성화됨. statement_type(10g) VARCHAR2 FGA가작동하는쿼리타입지정 (INSERT/DELETE/UPDATE, SELELCT) audit_trail(10g) AUDIT_TRAIL=> DBMS_FGA.DB_EXTENDED 로설정되는경우 BINARY_INTEGER SYS.FGA_log$ 의 LSQLTEXT, LSQLBIND 컬럼에 Audit Trail이 IN DEFAULT 생성된다. audit_column_opts(10g) BINARY_INTEGER IN DEFAULT AUDIT_COLUMN 에나열된컬럼중에서나열된컬럼중모두엑세스될때 Audit Trail 을생성하도록하는 DBMS_FGA.ALL_COLUMNS 옵션과 AUDIT_COLUMN 에나열된컬럼중에서하나라도엑세스될때 AUDIT TRAIL 이생성되도록지정하는 DBMS_FGA.ANY_COLUMNS 옵션이있다. 표 3 DBMS_FGA.ADD_POLICY 프로시져

14 Parameter Name Data Types Description object_schema VARCHAR2 해제하고자하는스키마명 object_name VARCHAR2 해제하고자하는오브젝트명 ( 테이블, 뷰명 ) policy_name VARCHAR2 Policy 명 표 4 DBMS_FGA.DROP_POLICY 2.3. Oracle 9i Database FGA 예제 CONNECT system/manager CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; User created. GRANT CONNECT, RESOURCE TO scott; Grant succeeded. CREATE USER alice IDENTIFIED BY alice DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; User created. GRANT CONNECT, RESOURCE TO alice; Grant succeeded. CONNECT SQLPLUS scott/tiger ANALYZE TABLE emp COMPUTE STATISTICS; Table analyzed. ANALYZE TABLE dept COMPUTE STATISTICS; Table analyzed. GRANT SELECT ON emp TO alice; Grant succeeded. GRANT SELECT ON dept TO alice; Grant succeeded

15 CONNECT system/manager -- FGA 설정 EXECUTE DBMS_FGA.ADD_POLICY( object_schema => 'SCOTT', - object_name => 'emp', - policy_name => 'aud_emp_sal_mgr', - audit_condition => 'deptno in (10, 20) ', - audit_column => 'sal, mgr', - handler_schema => 'system', - handler_module => 'INS_EMP_TRAIL', - enable => TRUE ); PL/SQL procedure successfully completed. CREATE TABLE aud_emp_trail ( object_schema VARCHAR2(80), object_name VARCHAR2(80), policy_name VARCHAR2(80)); Table created. CREATE OR REPLACE PROCEDURE system.ins_emp_trail ( p_object_schema VARCHAR2, p_object_name VARCHAR2, p_policy_name VARCHAR2) AS BEGIN INSERT INTO system.aud_emp_trail VALUES ( p_object_schema, p_object_name, p_policy_name ); END; / Procedure created. CONNECT ALICE/ALICE SELECT * FROM scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SMITH CLERK DEC ALLEN SALESMAN FEB WARD SALESMAN FEB JONES MANAGER APR MARTIN SALESMAN SEP BLAKE MANAGER MAY CLARK MANAGER JUN SCOTT ANALYST DEC KING PRESIDENT 17-NOV TURNER SALESMAN SEP ADAMS CLERK JAN JAMES CLERK DEC FORD ANALYST DEC MILLER CLERK JAN rows selected. -- 모든데이터가조회되므로 audit_condition 을만족한다

16 SELECT empno, ename, sal FROM scott.emp; EMPNO ENAME SAL SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER rows selected. -- 역시 WHERE 절이존재하지않고, 조회하는컬럼중에 sal 이있으므로 Audit Trail 생성된다. CONNECT system/manager SELECT ename FROM scott.emp WHERE deptno = 20; ENAME SMITH JONES SCOTT ADAMS FORD -- Audit Column 에도존재하지않고, Audit Condition 에만족하지않으므로 Audit Trail 이생성되지않는다. SELECT ename, sal, mgr FROM scott.emp WHERE deptno = 30; ENAME SAL MGR ALLEN WARD MARTIN BLAKE TURNER JAMES rows selected. -- Audit Column 에는존재하는 WHERE 절에만족하는데이터가 Audit Condition 에만족하는데이터를포함하지않으므로 Audit Trail 이생성되지않는다. COL db_user FORMAT A10 COL sql_bind FORMAT A40 COL sql_text FORMAT A60 col policy_name for a20 SET LINESIZE

17 SELECT TO_CHAR(timestamp, 'YYMMDDHH24MI') AS timestamp, db_user, policy_name, sql_text,userhost FROM dba_fga_audit_trail; TIMESTAMP DB_USER POLICY_NAME SQL_TEXT USERHOST ALICE AUD_EMP_SAL_MGR SELECT * FROM scott.emp PC ALICE AUD_EMP_SAL_MGR SELECT empno, ename, sal FROM scott.emp PC1 COLUMN object_schema FORMAT A15 COLUMN object_name FORMAT A15 COLUMN POLICY_NAME FORMAT A15 SELECT * FROM aud_emp_trail; OBJECT_SCHEMA OBJECT_NAME POLICY_NAME SCOTT EMP AUD_EMP_SAL_MGR SCOTT EMP AUD_EMP_SAL_MGR 2.4. Oracle Database 10g FGA 예제 1 STATEMENT_TYPE, AUDIT_TRAIL SQL> show parameter audit_trail NAME TYPE VALUE audit_trail string NONE SQL> BEGIN 2 DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT', 3 object_name => 'EMP', 4 policy_name => 'POL_SCOTT_EMP', 5 audit_condition => 'sal > 3000', 6 enable => TRUE, 7 statement_types => 'SELECT, INSERT', 8 audit_trail => DBMS_FGA.DB_EXTENDED); 9 END; 10 / PL/SQL procedure successfully completed. SQL> SELECT object_schema, object_name, policy_name, policy_text, policy_column, enabled 2 FROM dba_audit_policies; OBJECT_SCHEM OBJECT_NAME POLICY_NAME POLICY_TEXT POLICY_COLUMN ENA SCOTT EMP POL_SCOTT_EMP sal > 3000 YES SQL> SELECT timestamp, object_name, scn, sql_text, sql_bind 2 FROM dba_fga_audit_trail; no rows selected SQL> insert into emp values (:empno, :ename, 'MANAGER', 7499, SYSDATE, 3100, '', 20); 1 row created

18 SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO TESTER MANAGER AUG ALLEN SALESMAN FEB WARD SALESMAN FEB JONES MANAGER APR MARTIN SALESMAN SEP BLAKE MANAGER MAY CLARK MANAGER JUN SCOTT ANALYST DEC KING PRESIDENT 17-NOV JONES MANAGER APR MARTIN SALESMAN SEP BLAKE MANAGER MAY CLARK MANAGER JUN SCOTT ANALYST DEC KING PRESIDENT 17-NOV rows selected. SQL> insert into emp values (8888, 'TESTER2', 'SALESMAN', 7499, SYSDATE, 3200, 10, 30); 1 row created. SQL> delete from emp where empno = 9999; 1 row deleted. SQL> select timestamp, object_name, scn, sql_bind, sql_text, 2 from dba_fga_audit_trail; TIMESTAMP OBJECT_NAME SCN SQL_BIND SQL_TEXT :36:21 EMP 0 #1(4):9999 #2(6):TESTER insert into emp values (:empno, :ename, 'MANAGER', 7499, SYSDATE, 3100, '', 20) :36:26 EMP E+12 select * from emp; :36:34 EMP 0 insert into emp values (8888, 'TESTER2', 'SALESMAN', 7499, SYSDATE, 3200, 10, 30) * Oracle Database 10g 부터 statement_types 과 audit_trail 이추가되었다. Oracle 9i Database 에서 SELECT 문만 auditing 이가능했지만 Oracle Database 10g 에서는 INSERT, DELETE, UPDATE, MERGE 에대해서도가능하다. 또한 audit_trail 파라메터에 DBMS_FGA.DB_EXTENDED 로설정하므로 init parameter 의 audit_trail 파라메터와무관하게 FGA 가설정만으로수행된구문과바인드정보를볼수있다. * 위예제에서보았듯이 statement_types 가 SELECT, INSERT 이므로두가지의구문형식에대해서만 Auditing 되며, DELETE 는 Auditing 되지않았다. 그리고 audit_trail 이 DB_EXTENEDED 로설정되어 sql_bind, 와 sql_text 에대해서도 audit trail 이작성되었다

19 2.5. Oracle Database 10g FGA 예제 2 AUDIT_COLUMN_OPTS SQL> conn / as sysdba SQL> truncate table fga_log$; Table truncated. SQL> BEGIN 2 DBMS_FGA.DROP_POLICY(object_schema => 'SCOTT', 3 object_name => 'EMP', 4 policy_name => 'POL_SCOTT_EMP'); 5 END; 6 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT', 3 object_name => 'EMP', 4 policy_name => 'POL_SCOTT_EMP1', 5 audit_column => 'empno, sal, comm', 6 enable => TRUE, 7 audit_column_opts => DBMS_FGA.ANY_COLUMNS); 8 END; 9 / PL/SQL procedure successfully completed. SQL> select timestamp, object_name, scn, sql_bind, sql_text 2 from dba_fga_audit_trail; no rows selected SQL> connect scott/tiger SQL> select empno, ename from emp where deptno = 20; EMPNO ENAME JONES 7788 SCOTT 7566 JONES 7788 SCOTT SQL> select empno, sal from emp where sal > 3000; EMPNO SAL SQL> select ename, job from emp where job > 'MANAGER'; ENAME JOB ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN KING PRESIDENT MARTIN SALESMAN KING PRESIDENT

20 SQL> select empno, sal, comm from emp where deptno = 30; EMPNO SAL COMM SQL> select timestamp, object_name, scn, sql_bind, sql_text 2 from dba_fga_audit_trail; TIMESTAMP OBJECT_NAME SCN SQL_BIND SQL_TEXT :57:18 EMP E+12 select empno, ename from emp where deptno = :57:32 EMP E+12 select empno, sal from emp where sal > :58:07 EMP E+12 select empno, sal, comm from emp where deptno = 30 * audit_column_opts 값이 DBMS_FGA.ANY_COLUMNS 으로설정했을경우 audit_column 에나열된 Column 중에 1 개라도포함이되어있다면 Audit Trail 이기록된다. 나열된컬럼들중에없는경우는기록되지않는다. SQL> truncate table fga_log$; Table truncated. SQL> BEGIN 2 DBMS_FGA.DROP_POLICY(object_schema => 'SCOTT', 3 object_name => 'EMP', 4 policy_name => 'POL_SCOTT_EMP1'); 5 END; 6 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_FGA.ADD_POLICY(object_schema => 'SCOTT', 3 object_name => 'EMP', 4 policy_name => 'POL_SCOTT_EMP2', 5 audit_column => 'empno, sal, comm', 6 enable => TRUE, 7 audit_column_opts => DBMS_FGA.ALL_COLUMNS); 8 END; 9 / PL/SQL procedure successfully completed. SQL> connect scott/tiger

21 SQL> select empno, ename from emp where deptno = 20; EMPNO ENAME JONES 7788 SCOTT 7566 JONES 7788 SCOTT SQL> select empno, sal from emp where sal > 3000; EMPNO SAL SQL> select ename, job from emp where job > 'MANAGER'; ENAME JOB ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN KING PRESIDENT MARTIN SALESMAN KING PRESIDENT SQL> select empno, sal, comm from emp where deptno = 30; EMPNO SAL COMM SQL> select timestamp, object_name, scn, sql_bind, sql_text 2 from dba_fga_audit_trail; TIMESTAMP OBJECT_NAME SCN SQL_BIND SQL_TEXT :01:07 EMP E+12 select empno, sal, comm from emp where deptno = 30 * 위와같이 audit_column_opts 를 DBMS_FGA.ALL_COLUMN 으로설정한경우, audit_column 에나열한컬럼이모두조회되는구문에서만 Audit Trail 이기록된다 Database Auditing 과 FGA 의비교 이상살펴보았듯이 Database Auditing, Trigger 를이용한 Auditing, FGA(Fine-Grained Auditing) 을사용하는 Auditing 기법에대해알아보았다. 기존 Database Auditing 에서는그기능에제약이있었지만, Oracle 9i Database 에서제공되는 FGA 를사용하면보다용이한 Auditing 기능을제공한다. 적은 Audit Trail 을생성하면서수행되는 SQL 구문에대한정보를얻을수있고, Oracle Database 10g 부터제공되는 FGA 를사용하면 Bind 정보및실제수행된 SQL 문의유용한정보등를얻을수있다. 끝으로변경된데이터의이력을찾아야하는업무에서사전에적절하게 FGA 를응용한다면예상치못한데이터유실같은문제에대한원인규명및대처를할수있을것이라고기대한다

22 3. 부록 3.1. Trigger 를이용한 DML Auditing AUDIT_UTIL PACKAGE --Trigger 를이용한 DML Auditing - Package Name : AUDIT_UTIL CREATE OR REPLACE PACKAGE AUDIT_UTIL IS -- AUDIT 테이블생성 PROCEDURE create_audit_table( dest_table_owner IN VARCHAR2, dest_table_name IN VARCHAR2, audit_table_name IN VARCHAR2 DEFAULT '', tablespace_name IN VARCHAR2 DEFAULT 'SYSTEM'); -- TRIGGER 생성 PROCEDURE create_audit_trigger( audit_table_name IN VARCHAR2, dest_table_owner IN VARCHAR2, dest_table_name IN VARCHAR2); -- DML AUDIT 를위한테이블및트리거자동생성 PROCEDURE create_dml_audit( dest_table_owner IN VARCHAR2, dest_table_name IN VARCHAR2, audit_table_name IN VARCHAR2 DEFAULT '', tablespace_name IN VARCHAR2 DEFAULT 'SYSTEM'); -- DML AUDIT 관련테이블및트리거제거 PROCEDURE remove_dml_audit( audit_table_name IN VARCHAR2, audit_trigger_name IN VARCHAR2 DEFAULT ''); -- ADUIT TABLE 내데이터삭제 ( 날짜별이전데이터삭제 ) PROCEDURE delete_auditing_values( audit_table_name IN VARCHAR2, start_date IN DATE, end_date IN DATE DEFAULT NULL); -- ADUIT TABLE 내데이터삭제 ( 지정일수이전데이터삭제 ) PROCEDURE delete_auditing_values( audit_table_name IN VARCHAR2, days IN NUMBER); END AUDIT_UTIL; / CREATE OR REPLACE PACKAGE BODY AUDIT_UTIL IS DDL 실행프로시져 (PRIVATE) PROCEDURE exec_ddl(statement IN VARCHAR2) IS v_statment VARCHAR2(4000); v_cid INTEGER; v_ret INTEGER; BEGIN v_statment := REPLACE(statement, CHR(10), ''); v_cid := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(C => v_cid, STATEMENT => v_statment, LANGUAGE_FLAG => DBMS_SQL.NATIVE);

23 --v_ret := DBMS_SQL.EXECUTE(v_cid); DBMS_SQL.CLOSE_CURSOR(v_cid); END exec_ddl; DML 실행프로시져 (PRIVATE) PROCEDURE exec_dml(statement IN VARCHAR2) IS v_statment VARCHAR2(4000); v_cid INTEGER; v_ret INTEGER; BEGIN v_statment := REPLACE(statement, CHR(10), ''); v_cid := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(C => v_cid, STATEMENT => v_statment, LANGUAGE_FLAG => DBMS_SQL.NATIVE); v_ret := DBMS_SQL.EXECUTE(v_cid); DBMS_SQL.CLOSE_CURSOR(v_cid); END exec_dml; AUDIT 테이블생성 PROCEDURE : CREATE_AUDIT_TABLE (only SYS OR SYSTEM USER) PROCEDURE create_audit_table ( dest_table_owner in VARCHAR2, dest_table_name in VARCHAR2, audit_table_name in VARCHAR2 DEFAULT '', tablespace_name in VARCHAR2 DEFAULT 'SYSTEM') IS CURSOR c_table_columns IS SELECT column_name, data_type, data_length, data_precision, data_scale FROM dba_tab_columns WHERE owner = dest_table_owner AND table_name = dest_table_name; v_ddl VARCHAR2(4000); v_audit_table_name VARCHAR2(100); BEGIN IF (dest_table_owner IS NULL ) OR (dest_table_name IS NULL) THEN DBMS_OUTPUT.PUT_LINE('No Values in Owner or Table_name'); END IF; IF (audit_table_name IS NULL) THEN v_audit_table_name := dest_table_name '_AUDIT'; ELSE v_audit_table_name := audit_table_name; END IF; -- Table DDL 문 v_ddl := 'CREATE TABLE ' v_audit_table_name ' (' chr(10); FOR v_col_rec IN c_table_columns LOOP IF (c_table_columns%notfound) THEN DBMS_OUTPUT.PUT_LINE('TABLE NOT FOUND'); END IF; CASE WHEN v_col_rec.data_type IN ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR') THEN v_ddl := v_ddl ' old_' v_col_rec.column_name ' ' v_col_rec.data_type '(' v_col_rec.data_length ')'; WHEN v_col_rec.data_type = 'NUMBER' THEN

24 v_ddl := v_ddl ' old_' v_col_rec.column_name ' ' v_col_rec.data_type; IF (v_col_rec.data_precision IS NOT NULL) THEN v_ddl := v_ddl '(' v_col_rec.data_precision; IF (v_col_rec.data_scale IS NOT NULL) THEN v_ddl := v_ddl ',' v_col_rec.data_scale; END IF; v_ddl := v_ddl ')'; END IF; ELSE v_ddl := v_ddl ' old_' v_col_rec.column_name ' ' v_col_rec.data_type; END CASE; v_ddl := v_ddl ',' CHR(10); END LOOP; FOR v_col_rec IN c_table_columns LOOP IF (c_table_columns%notfound) THEN DBMS_OUTPUT.PUT_LINE('TABLE NOT FOUND'); END IF; CASE WHEN v_col_rec.data_type IN ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR') THEN v_ddl := v_ddl ' new_' v_col_rec.column_name ' ' v_col_rec.data_type '(' v_col_rec.data_length ')'; WHEN v_col_rec.data_type = 'NUMBER' THEN v_ddl := v_ddl ' new_' v_col_rec.column_name ' ' v_col_rec.data_type; IF (v_col_rec.data_precision IS NOT NULL) THEN v_ddl := v_ddl '(' v_col_rec.data_precision; IF (v_col_rec.data_scale IS NOT NULL) THEN v_ddl := v_ddl ',' v_col_rec.data_scale; END IF; v_ddl := v_ddl ')'; END IF; ELSE v_ddl := v_ddl ' new_' v_col_rec.column_name ' ' v_col_rec.data_type; END CASE; v_ddl := v_ddl ',' CHR(10); END LOOP; v_ddl := v_ddl ' v_ddl := v_ddl ' v_ddl := v_ddl ' changed_by VARCHAR2(8),' CHR(10); change_type CHAR(1),' CHR(10); timestamp DATE' CHR(10) ') TABLESPACE ' tablespace_name; exec_ddl(v_ddl); END create_audit_table; TRIGGER 생성 PROCEDURE : CREATE_AUDIT_TRIGGER PROCEDURE create_audit_trigger( audit_table_name IN VARCHAR2, dest_table_owner IN VARCHAR2, dest_table_name IN VARCHAR2) IS CURSOR c_table_columns IS SELECT rownum, column_name FROM dba_tab_columns WHERE owner = USER AND table_name = audit_table_name; v_audit_table_name VARCHAR2(100); v_st VARCHAR2(4000); BEGIN

25 v_audit_table_name := audit_table_name; v_st := 'CREATE OR REPLACE TRIGGER TR_' v_audit_table_name CHR(10) ' BEFORE INSERT OR DELETE OR UPDATE ON ' dest_table_owner '.' dest_table_name CHR(10) ' FOR EACH ROW ' CHR(10) 'DECLARE' CHR(10) ' v_changetype CHAR(1); ' CHR(10) 'BEGIN ' CHR(10) ' IF INSERTING THEN v_changetype := ''I''; ' CHR(10) ' ELSIF UPDATING THEN v_changetype := ''U''; ' CHR(10) ' ELSE v_changetype := ''D''; ' CHR(10) ' END IF; ' CHR(10) ' INSERT INTO ' v_audit_table_name '('; FOR v_col_rec IN c_table_columns LOOP IF (v_col_rec.rownum > 1) THEN v_st := v_st ', '; END IF; v_st := v_st v_col_rec.column_name; END LOOP; v_st := v_st ') ' CHR(10) ' VALUES ('; FOR v_col_rec IN c_table_columns LOOP IF (v_col_rec.rownum > 1) THEN v_st := v_st ', '; END IF; CASE SUBSTR(v_col_rec.column_name, 1, 3) WHEN 'OLD' THEN v_st := v_st REPLACE(v_col_rec.column_name, 'OLD_', ':OLD.'); WHEN 'NEW' THEN v_st := v_st REPLACE(v_col_rec.column_name, 'NEW_', ':NEW.'); ELSE IF (v_col_rec.column_name = 'CHANGED_BY') THEN v_st := v_st 'USER'; ELSIF (v_col_rec.column_name = 'CHANGE_TYPE') THEN v_st := v_st 'v_changetype'; ELSIF (v_col_rec.column_name = 'TIMESTAMP') THEN v_st := v_st 'SYSDATE'; END IF; END CASE; END LOOP; v_st := v_st '); ' CHR(10) 'END TR_' v_audit_table_name ';'; exec_ddl(v_st); END create_audit_trigger; DML AUDIT 를위한테이블및트리거자동생성 PROCEDURE : CREATE_DML_AUDIT PROCEDURE create_dml_audit( dest_table_owner in VARCHAR2, dest_table_name in VARCHAR2, audit_table_name in VARCHAR2 DEFAULT '', tablespace_name in VARCHAR2 DEFAULT 'SYSTEM') IS v_audit_table_name VARCHAR2(100); v_dest_table_owner VARCHAR2(100); V_dest_table_name VARCHAR2(100);

26 BEGIN v_dest_table_owner := UPPER(dest_table_owner); v_dest_table_name := UPPER(dest_table_name); IF (audit_table_name IS NULL) THEN v_audit_table_name := UPPER(dest_table_name) '_AUDIT'; ELSE v_audit_table_name := UPPER(audit_table_name); END IF; CREATE_AUDIT_TABLE(v_dest_table_owner, v_dest_table_name, v_audit_table_name, tablespace_name); CREATE_AUDIT_TRIGGER(v_audit_table_name, v_dest_table_owner, v_dest_table_name); END create_dml_audit; DML AUDIT 관련테이블및트리거제거 PROCEDURE : MAKE_DML_AUDIT PROCEDURE remove_dml_audit( audit_table_name IN VARCHAR2, audit_trigger_name IN VARCHAR2 DEFAULT '') IS v_c INTEGER; v_st VARCHAR2(4000); v_tr VARCHAR2(100); BEGIN v_st := 'DROP TABLE ' audit_table_name; exec_ddl(v_st); IF (audit_trigger_name IS NULL) THEN v_tr := 'TR_' audit_table_name; ELSE v_tr := audit_trigger_name; END IF; v_st := 'DROP TRIGGER ' v_tr; exec_ddl(v_st); END remove_dml_audit; ADUIT TABLE 내데이터삭제 PROCEDURE : DELETE_AUDITING_VALUES PROCEDURE delete_auditing_values( audit_table_name IN VARCHAR2, start_date IN DATE, end_date IN DATE DEFAULT NULL) IS v_st VARCHAR2(4000); BEGIN v_st := 'DELETE FROM' audit_table_name; IF (end_date IS NULL) THEN v_st := v_st ' WHERE TIMESTAMP < TO_DATE(''' TO_CHAR(start_date, 'YYYY-MM-DD HH24:MI:SS') ''', ''fmyyyy-mm-dd HH24:MI:SS'')'; ELSE v_st := v_st ' WHERE TIMESTAMP BETWEEN TO_DATE(''' TO_CHAR(start_date, 'YYYY-MM-DD HH24:MI:SS') ''', ''fmyyyy-mm-dd HH24:MI:SS'') AND TO_DATE(''' TO_CHAR(end_date, 'fmyyyy-mm-dd HH24:MI:SS') ''', ''fmyyyy-mm-dd HH24:MI:SS'')'; END IF; exec_dml(v_st); COMMIT; END delete_auditing_values; PROCEDURE delete_auditing_values( audit_table_name IN VARCHAR2,

27 days IN NUMBER) IS v_st VARCHAR2(4000); v_last_date DATE; BEGIN v_last_date := SYSDATE - days; v_st := 'DELETE FROM' audit_table_name ' WHERE TIMESTAMP < TO_DATE(''' TO_CHAR(v_last_date, 'YYYY-MM-DD HH24:MI:SS') ''', ''fmyyyy-mm-dd HH24:MI:SS'')'; exec_dml(v_st); COMMIT; END delete_auditing_values; END AUDIT_UTIL; / 실행방법 ( AUDIT 용 TABLE 및 TRIGGER 생성 ) exec make_dml_audit (dest_table_owner, dest_table_name, audit_table_name(default ''), tablespace_name(default 'SYSTEM'); EXAMPLE 1 - exec make_dml_audit('scott', 'EMP'); EXAMPLE 2 - exec make_dml_audit('scott', 'EMP', 'EMP_AUDIT', 'USERS'); 확인 : DML 작업이후 audit_table_name 조회 EXAMPLE - SELECT * FROM EMP_AUDIT; 2. AUDIT TABLE 내데이터삭제 PROCEDURE delete EXAMPLE 1 - exec delete_auditing_values('emp_audit', TO_DATE(' :00:00', 'fmyyyy-mm-dd HH24:MI:SS'); EXAMPLE 2 - exec delete_auditing_values('emp_audit', TO_DATE(' :00:00', 'fmyyyy-mm-dd HH24:MI:SS'), TO_DATE(' :00:00', 'fmyyyy-mm-dd HH24:MI:SS')); EXAMPLE 3 - exec delete_auditing_values('emp_audit', 1); -- 1 일이전데이터삭제 3. 제거방법 ( AUDIT 용 TABLE 및 TRIGGER 제거 ) exec remove_dml_audit (audit_table_name, audit_trigger_name(default '')); EXAMPLE - exec remove_dml_audit('emp_audit'); -- Test : SCOTT.EMP 에대한 DML Auditing exec make_dml_audit('scott', 'EMP', 'EMP_AUDIT', 'USERS'); select table_name from dba_tables where trigger_name like '%_AUDIT' select trigger_name from dba_triggers where trigger_name like 'TR_%' connect scott/tiger INSERT INTO emp VALUES ( 9999, 'TESTER', 'CLERK', 7782, SYSDATE, 1000, 0, 10); COMMIT; UPDATE emp SET comm = 300 WHERE empno = 9999; COMMIT; DELETE FROM emp WHERE empno = 9999; COMMIT; connect /as sysdba SELECT OLD_EMPNO, OLD_ENAME, OLD_COMM, NEW_EMPNO, NEW_ENAME, NEW_COMM, CHANGED_BY, CHANGE_TYPE,

28 TIMESTAMP FROM EMP_AUDIT; OLD_EMPNO OLD_ENAME OLD_COMM NEW_EMPNO NEW_ENAME NEW_COMM CHANGED_BY CH TIMESTAMP TESTER 0 SCOTT I 01-AUG TESTER TESTER 300 SCOTT U 01-AUG TESTER 300 SCOTT D 01-AUG 일정기간에대한 Audit Trail Reocords 삭제 alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; exec delete_auditing_values('emp_audit', TO_DATE(' :00:00', 'fmyyyy-mm-dd HH24:MI:SS')); exec delete_auditing_values('emp_audit', TO_DATE(' :00:00', 'fmyyyy-mm-dd HH24:MI:SS'), TO_DATE(' :00:00', 'fmyyyy-mm-dd HH24:MI:SS')); exec delete_auditing_values('emp_audit', 0); -- 현재이전삭제 ( 전체삭제 ) -- dbms_job 을이용하여자동삭제스케줄링 VARIABLE job_no NUMBER; VARIABLE inst_no NUMBER; begin select instance_number into :inst_no from v$instance; dbms_job.submit(:job_no, 'AUDIT_UTIL(''EMP_AUDIT'', 0);', trunc(sysdate+1,'hh'), 'trunc(sysdate+1,''hh'')', TRUE, :inst_no); commit; end; / print :job_no; exec dbms_job.broken(:job_no, TRUE); exec dbms_job.remove(:job_no); -- Audit Trail Table 제거 exec remove_dml_audit('emp_audit');

29 3.2. SQL Tips for Developer 이번호부터개발자를위한 SQL Tip 을연재합니다. 자주사용하는 Oracle Function 이나, 새로운오라클버전에서소개된 SQL New Feature 를통하여프로그램생산성및성능을높일수있는내용들을소개하겠습니다. 이번호에서는개발자분들이자주사용하는 NVL 함수관련내용을모아보았습니다. 흔히개발자분들이 NULL 값의처리에많은고민을하게되는데, 기존에자주사용하시는 NVL 함수뿐아니라, NVL2, NULLIF, COALESCE, LNNVL 함수를추가로소개합니다. 이함수들은기존에프로그램에서로직으로처리해야하는많은코딩을함수하나를제대로사용함으로써대체할수있는기능을가지고있습니다. 프로그램개발생산성을올릴수있는오라클함수들입니다. 1. NVL2 (expr1, expr2, expr3) NVL2 함수는첫번째표현식을검사합니다. expr1 이널이아닌경우, NVL2 함수는 expr2 를반환하며, expr1 이널인경우 expr3 을반환합니다. SELECT last_name, salary, commission_pct, NVL2(commission_pct, 2 'SAL+COMM', 'SAL') income FROM employees WHERE department_id IN (50, 80); 1 예제에서는 COMMISSION_PCT 열을검사하여이값이 NULLL 이아니면두번째표현식인 SAL+COMM 이반환됩니다. COMMISSION_PCT 열에널값이있으면세번째표현식인 SAL 이반환됩니다. 인수 expr1 에는모든데이터유형을사용할수있습니다. 인수 expr2 및 expr3 에는 LONG 을제외한모든데이터유형을사용할수있습니다. expr2 및 expr3 의데이터유형이서로다를경우, Oracle server 는 expr3 이널상수가아니라면두표현식을비교하기전에 expr3 을 expr2 의데이터유형으로변환합니다. expr3 이널상수인경우에는데이터유형변환이필요없습니다. 반환값의데이터유형은항상 expr2 의데이터유형과동일하며 expr2 가문자데이터인경우에는반환값의데이터유형이 VARCHAR2 입니다. 2. NULLIF (expr1, expr2) NULLIF 함수는두표현식을비교하여동일한경우널을반환하고동일하지않은경우첫번째표현식을반환합니다. 첫번째표현식에널리터럴을지정할수없습니다. SELECT first_name, LENGTH(first_name) "expr1", last_name, LENGTH(last_name) "expr2", NULLIF(LENGTH(first_name), LENGTH(last_name)) result FROM employees;

30 예제에서는 EMPLOYEES 테이블의 First_name 과 Last_name 의길이를비교하여같으면 NULL 을리턴하고다르면 First_name 의길이를리턴합니다. 참고 : NULLIF 함수는다음의 CASE 표현식과논리적으로동일합니다. CASE 표현식은다음페이지에서설명합니다. [ CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END ] 3. COALESCE (expr1, expr2,..., exprn) COALESCE 함수는여러개의평가식에 NULL 값이있는지여부를동시에수행할수있습니다. expr1 을 먼저평가하여 Null 이아니면 expr1 의값을리턴하고 NULL 이면 expr2 를평가합니다. expr2 가 NULL 이 아니면 expr2 의값을리턴하고 NULL 이면 expr3 을평가합니다. 이런방식을 exprn 까지모두평가할 수있습니다. SELECT last_name, COALESCE(commission_pct, salary, 10) comm FROM employees ORDER BY commission_pct; 예제에서, COMMISSION_PCT 값이널이아닌경우, 이값이표시됩니다. COMMISSION_PCT 값이널인경우, SALARY 가평가됩니다. COMMISSION_PCT 와 SALARY 값이모두널인경우, 상수값 10 이표시됩니다. 4. LNNVL (expr1) LNNVL 함수는조건식에주어지는항의한쪽혹은양쪽에 NULL 값이나타날경우에, 조건식을평가하는간결한방법을제공한다. LNNVL 함수는 where 절에만사용할수있습니다. 1. emp 테이블에서 commission 이 500 보다작은사람들의수를계산한다. SQL> SELECT COUNT (*) FROM emp WHERE comm < 500 COUNT(*) 하지만, commission 을못받는사람을포함해서 (comm is null), commissin 이 500 보다작은사람들의수를계산하기위해서는아래와같이 LNNVL 함수를쓴다. SQL> SELECT COUNT (*) FROM emp WHERE LNNVL(comm >= 500) COUNT(*) LNNVL 함수는조건식을파라미터로가지며, 조건식이 FALSE 나 UNKNOWN 일경우에 TRUE 를반환하고, 조건식이 TRUE 일경우에 FALSE 를반환합니다. LNNVL 함수는잠재적으로 NULL 값이나올수있는상황에서 NULL 값을처리하기위해사용할수있습니다

31 3.3. Oracle 소식 Oracle 11g 가출시되었습니다. 데이터베이스 (DB) 업계최강자인오라클이 7 월 12 일차세대데이터베이스관리시스템 (DBMS) 인 ' 오라클데이터베이스 11g' 를공식출시하였습니다. 이전버전인 ' 오라클데이터베이스 10g' 가출시된지 3 년여만에선보인 11g 는리얼애플리케이션테스팅 재해복구 정보순환주기관리등 400 가지이상의기능을갖췄으며 1500 만시간의테스팅과 3 만 6000 개월에걸친작업으로개발되었습니다. 특히자가관리와자동화성능으로기업이서비스레벨협약 (SLA) 를실현하도록지원하는리얼애플리케이션테스트기능은이제품이내세우는가장큰장점입니다. 고가용성, 확장성, IT 비용절감강조 오라클은향상된보안기능과그리드컴퓨팅기반고가용성, 확장성을내세워 ' 오라클데이터베이스 11g' 를사용하는기업이시스템관리비용과데이터스토리지비용을절감할수있다는것을강조하고있습니다. 서버테크놀러지부문앤드류멘델슨수석부사장은컨퍼런스콜을통해 " 오라클데이터베이스 11g 는그리드기술이확장돼기업의 IT 비용절감에도움을준다 " 고말하고있습니다. ' 오라클데이터베이스 11g' 가기업의비용절감을돕는다는점을강조하고있으며앞으로다양한운영체제 (OS) 에대해동일한수준의지원을펼칠계획입니다. 또오라클은최근비즈니스인텔리전스 (BI) 에대한기업수요가늘어난다는것을고려, 신제품의 BI 기능도강화하고있습니다. 국내에서의반응은? ' 오라클데이터베이스 11g' 는국내시장에서는오는 8 월에출시될예정입니다. 그러나, 현재대부분의기업이 ' 오라클데이터베이스 10g' 보다이전버전을사용하고있고, 국내기업들이중요한데이터집합소인 DB 를업그레이드하거나교체하는데민감한반응을보이고있기때문에신제품확산빠르게적용되지는않을것으로예측됩니다. 한국오라클세일즈컨설팅양수환전무는 " 자체조사결과오라클고객가운데 40% 가 ' 오라클데이터베이스 10g' 버전을사용하고있다 " 며 " 한국기업들이새로운제품을받아들이는속도가늦긴하지만새로운기능에대한요구가있어이제품이확산되는데큰장애가없다고본다 " 고발표했습니다. ' 오라클데이터베이스 11g' 가이전버전인 ' 오라클데이터베이스 10g' 와기능면에서크게다르지않다는것도기업들이새로운제품을선택하는데걸림돌이될것으로보입니다. 실제로오라클은 ' 오라클데이터베이스 11g' 에새로운기능을추가시켰다기보다기능을확장하고여러옵션들을새롭게내놓는것에중점을두었으며, 이신제품이기업의 IT 비용절감에도움이된다고하지만이특징이기업들이업그레이드를강행할충분한요소가될수있을지는미지수입니다. 한국오라클은이에 "10g 사용자가오라클데이터베이스 11g 로업그레이드할경우비용이거의들지않기때문에업그레이드를채택하는기업이늘어날것 " 이라고합니다. 11g 관련기술세미나개최 한국오라클교육센터에서는 8 월 22 일부터 24 일까지 3 일간에걸쳐서 Oracle RDBMS 11g 의신기술소개와 RAC(Real Application Cluster), 대용량데이터 Migration 기법에대한세미나를실시합니다 Innovative Technology Seminar Track 을통해, 오라클신기술에대한핵심적인내용을신기술에대한접근방법설명과데모를통하여소개해드립니다. 오라클교육사업본부의전문성을가장극대화하여보여줄수있는분야인, Technology 부분의주요 3 가지주제에대해세미나를진행합니다. 'Oracle Database 11g New Features', 'Real Application Cluster 10g' 그리고 'Down-Time 최소화를위한다양한 Data Migration 방법론 ' 3 가지주제와일반 Overview 세미나에서는접하기힘든고급실무사례도다룰예정입니다

32 4. Pro-Active Tuning Service 4.1. 실제사용자 (End-User) 관점의응답시간튜닝 Pro-active tuning service 는사용자관점의모니터링및분석을통하여실제 End-User 가느끼는응답시간 (Response Time) 을튜닝합니다. APM(Application Performance Management) 툴을이용하여 End-User 의 Request 결과를반환받기까지의모든구간 (Client PC, Internet 구간, FireWall, DNS, Web Server, WAS, DBMS) 을분석하여가장 Delay Time 이많이소요된구간을찾아냅니다. ISP Backb one ISP Client Time Internet Time Firewall Time DNS Time Backend Systems Time 4.2. 최상의성능상태로비즈니스고가용성을유지 Pro-Active Tuning Service 매업무단위프로젝트마다참여하여업무적용 (Open) 前문제요소를분석하여튜닝. 단위업무적용 (Open) 후매 3 개월 ( 데이터량갱신주기 ) 마다튜닝포인트를설정, 성능둔화요소를해결. 전사적으로새롭게추가되는업무단위프로젝트의모든 SQL 쿼리를검토및튜닝. 다양한대용량데이터베이스관리 / 튜닝기법을도입하여최적의 DB 상태를 1 년내내상시유지. 전략적튜닝 Factor 를분석, 투자대비효율이높은 Targeting 기법적용. ( 비중도높은 SQL 을튜닝함 )

33 4.3. Knowledge Transfer Pro-Active Tuning Service 는고객의 Business Process 를이해하고시스템을분석한후튜닝하는것으로완료되지않습니다. 실제로고객사환경에서튜닝한내용을그대로실무자들에게전수하여내부임직원의역량을제고시킵니다. 또한, Oracle RDBMS 신버젼의 New Features 를교육함으로써, 이용자 ( 관리자및개발자 ) 가스스로개발업무의효율및생산성을향상시킬수있도록지원합니다. 이외에도 DBMS 관리자를위한관리노하우 ( 고급 Trouble-Shooting, 대용량 DB 처리, 병렬처리등 ) 를전수함으로써, 최상의시스템을최고의기술로유지할수있도록지원합니다. UAS (User Adapted Seminar) 진행사례및내용 (Contents) 개발자를위한 SQL 튜닝실무사례세미나 G 쇼핑몰업체튜닝후실제고객사의튜닝사례를개발자들에게전수하여개발자들이성능을고려한 SQL 을작성할수있도록내부역량을제고시킴. Oracle 10g New Features 세미나 S Global 전자기업 : Oracle 10g 버전으로업그레이드하기전, 신버전의새로운기능과주의사항을전파함으로써, 업그레이드후발생할수있는문제점의사전제거와개발자들이새로운기능을이용함으로써, 개발생산성을향상시킴. K 국가기관 DBMS 관리노하우세미나내부관리자 (DBA,SE) 들을대상으로 DBMS 관리자들이흔히겪을수있는상황에대한 Administration Know-How 와고급 Trouble-Shooting 사례를소개함으로써, 관리기술력을향상시킴 Tuning 범위확대 Pro-active tuning service 의제공범위는제한된 Database, 제한된 Server 에국한되지않으며, 고객사전체의 Server+DB 를대상으로그범위를확대함으로써고객사전체 Performance 향상에기여합니다

34 4.5. 기대효과 재무적관점 기존 Tuning Service 는주로 System Performance 향상에따른업무트레픽감소에초점이맞춰져있었습니다. Pro-actvice 서비스는 Tuning 작업을통한업무처리시간단축뿐만아니라, 업무처리시간단축으로가져올수있는재무적성과를가능하게합니다 서비스관점 단기적성능향상에맞추어진기존 Tuning 서비스는계약된 system 및 Database 를서비스대상으로하기때문에전사적인차원의성능향상을기대하기어려웠습니다. Proactive tuning service 는계약기간동안주요비즈니스 Factor 별로 SLA 를정하여 Tuning consulting 을수행함으로써서비스자체의안정성을제고할수있습니다

35 사용자관점 Proactive tuning service 는계약종료시점작업한 Tuning 산출물을통한기술전수세미나를진행함으로서고객사의사용자가실무에서바로적용가능한기술을전수함과동시에, 계약기간종료후에도 Proactive tuning service 를유지할수있는방향을제시합니다 혁신적관점

36 - 36 -