[Oracle] FGA 구현하기

<< 프로시저 작성 문법 

CREATE OR REPLACE PROCEDURE 이름 
  [(Parameter....)] 
IS 
pl/sql_block; 

<<실행방법 

SQL*Plus에서 명령프롬프트에서 argument값과 함께 프로시저 이름을  
입력하면 됩니다.  
SQL> EXECUTE raising_salary(20000); 

 << 예 >> 

CREATE OR REPLACE PROCEDURE GBN_INSERT 

IS 

V_LCODE      ETC_AGENCY.LCODE%TYPE; 
V_MCODE      ETC_AGENCY.MCODE%TYPE; 
V_SCODE      ETC_AGENCY.SCODE%TYPE; 
V_GBN      ETC_AGENCY.GBN%TYPE; 


data_count        INTEGER; 

CURSOR SELECT_GBN_INSERT IS 

          SELECT LCODE, MCODE, SCODE, GBN    
          FROM CD_SCODE;  

BEGIN 
OPEN SELECT_GBN_INSERT; 

SELECT COUNT(*) INTO data_count FROM CD_SCODE; 

FOR i IN 1..data_count LOOP 

    
    FETCH SELECT_GBN_INSERT INTO V_LCODE, V_MCODE, V_SCODE, V_GBN; 

    UPDATE ETC_AGENCY 
          SET GBN = V_GBN 
          WHERE LCODE = V_LCODE  
                AND MCODE = V_MCODE 
                AND SCODE = V_SCODE; 
            COMMIT;              

END LOOP; 

COMMIT; 
CLOSE SELECT_GBN_INSERT; 

  
END GBN_INSERT; 

위의 예는 데이터를 여러행에 걸쳐 업데이트 하는데 update문에 조건절이 계속  
바뀔 경우에 매번 빠꿔 주기 싫은 경우에 간단히 제작하여 쓰면 편합니다  

<< 파라메터 명 선언 문법 

Parameter명 [ IN | OUT | IN OUT ] data type 
 [ { := | DEFAULT } 식 ] 

IN    실행환경에서 서브 프로그램으로 값을 전달. 
OUT 프로시저에서 실행환경으로 값을 전달. 
IN OUT 실행환경에서 프로시저로 값을 전달하고 프로시저에서 실행환경으로 변경된 값을 전달할 수 있다. 

<< 주요 특징 

PL/SQL블럭들로 구성된다. 
어떠한 파라메터도 사용 가능합니다. 
함수, 프로시져등을 호출할 수 있다. 
함수에서는 리턴값을 필요로 하지만 프로시저에서는 가질 수 없습니다. 
Query문에서 독립적으로 사용될 수 없다. 
local 변수는 Declare section( IS 와 BEGIN 사이) 에 합니다. 
SELECT, UPDATE, INSERT 등 프로시저 내의 PL/SQL블럭은 어떤 DML문이든 포함이 될 수 있으나 DDL문은 사용할 수 없다. 

<< 프로시져(Procedure)의 구성 

 프로시져의 주요부는 선언, 실행, 예외 섹션을 가진 PL/SQL블럭이다. 

 CREATE OR REPLACE PROCEDURE procedurename AS 
 Declarative section 
 Executable section 
 EXCEPTION 
 Exception section 
 END [procedurename]; 

----------------------------------------------------------- 


아래와 같이 change_salary procedure를 생성하고 실행해 보시오.

 
SQL> conn testxx/testxx 
SQL> EDIT p1 
CREATE OR REPLACE  PROCEDURE change_salary 
          (p_id  IN  NUMBER,  p_new_sal  IN  NUMBER) 
IS 
BEGIN　　 
            UPDATE  s_emp 
            SET  salary  =  p_new_sal 
            WHERE id  =  p_id; 
            COMMIT; 
END change_salary ; 
/ 
SQL> @p1 
(* Error 발생 시 SHOW ERRORS 명령으로 확인하여 수정한 후 다시 생성) 

SQL> SELECT id, salary FROM s_emp WHERE id = 1; 
(* 현재의 salary 확인) 
SQL> EXEC change_salary(1, 5000); 
SQL> SELECT id, salary FROM s_emp WHERE id = 1; 
(* 변경된 salary 확인) 

 

출처:http://www.oraclejava.co.kr/oraclejavanew/oraclejava/index.php

---

FGA 예제

begin

DBMS_FGA.ADD_POLICY (

   object_schema      =>  'scott', 

   object_name        =>  'emp', 

   policy_name        =>  'mypolicy1', 

   audit_condition    =>  'sal < 100', 

   audit_column       =>  'comm,sal', 

   handler_schema     =>   NULL, 

   handler_module     =>   NULL, 

   enable             =>   TRUE, 

   statement_types    =>  'INSERT, UPDATE', 

   audit_trail        =>   DBMS_FGA.XML + DBMS_FGA.EXTENDED, 

   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS); 

end;

/

<DROP_POLICY>

DBMS_FGA.DROP_POLICY(

   object_schema  VARCHAR2, 

   object_name    VARCHAR2, 

   policy_name    VARCHAR2 );

==================================================

**  상급admin이  delete  할때 마다 audit 남김  **

begin

DBMS_FGA.ADD_POLICY (

   object_schema      =>  'wed', 

   object_name        =>  'customer', 

   policy_name        =>  'del_admin1', 

   handler_schema     =>   NULL, 

   handler_module     =>   NULL, 

   enable             =>   TRUE, 

   statement_types    =>  'DELETE', 

   audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED, 

   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS); 

end;

/

**  하급admin이 view select 할때 마다 audit 로 

test 테이블에 scn 100 동안 10회 이상 조회할 경우 insert 해서 남김(빈번한 sql)  **

begin

DBMS_FGA.ADD_POLICY (

   object_schema      =>  'subadmin', 

   object_name        =>  'cust_view', 

   policy_name        =>  'sel_subadmin', 

   handler_schema     =>   'sys', 

   handler_module     =>   'test', 

   enable             =>   TRUE, 

   statement_types    =>  'SELECT', 

   audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED, 

   audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS); 

end;

/

==> DBMS_FGA.EXTENDED 는 FGA가 바인드 변수를 상시 캡쳐하도록 할수 있다

begin

DBMS_FGA.DROP_POLICY (

   object_schema      =>  'wed', 

   object_name        =>  'customer', 

   policy_name        =>  'del_admin');

end;

/

begin

DBMS_FGA.DROP_POLICY (

   object_schema      =>  'subadmin', 

   object_name        =>  'cust_view', 

   policy_name        =>  'sel_subadmin');

end;

/

=====검색하기

**fgalog.sql**

select sessionid,timestamp#,sqltext

from sys.fga_log$

/

 

**policy.sql**

select object_schema,object_name,policy_name

from dba_audit_policies

/

**fga_trail.sql**

select to_char(timestamp, 'RRRR-MM-DD HH24:MI:SS') as timestamp, db_user, policy_name, sql_bind,sql_text

from dba_fga_audit_trail;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

** 빈번하게 발생하는 sql 확인 **

after insert on fga_log$

for each row

declare

begin

select sessionid,DBUID,count(*)

from sys.fga_log$

where scn between timestamp_to_scn(sysdate)-300 and timestamp_to_scn(sysdate)

and obj$name='CUSTOMER'

group by sessionid, DBUID

having count(*)>9;

** 프로시져 생성 **

create or replace procedure test

as

cursor c1 is 

select sessionid,DBUID 

from sys.fga_log$ 

where scn between timestamp_to_scn(sysdate)-10000 and timestamp_to_scn(sysdate) 

and obj$name='CUST_VIEW' 

group by sessionid, DBUID 

having count(*)>9;

sql_stmt    VARCHAR2(200);

begin 

dbms_output.enable('10000000');

dbms_output.put_line('-----------------------------');

dbms_output.put_line('-------------성공------------');

dbms_output.put_line('-----------------------------');

for rec in c1 loop 

dbms_output.put_line('----' || rec.sessionid || '---'|| rec.DBUID ||'--');

sql_stmt := 'insert into fre_trail values (:1,:2)';

execute immediate sql_stmt using rec.sessionid, rec.DBUID;

end loop; 

dbms_output.put_line('-----------------------------');

end;

** 임시테이블 생성 **

create table fre_trail

(sessionid number,

DBUID varchar2(30));