Membuat Trigger untuk audit DDL yang dilakukan User (Oracle Database)


Trigger ini berguna untuk mengetahui DDL yang dilakukan oleh user.

Berikut list DDL yang akan tercatat ketika user eksekusi DDL :
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER CREATE
BEFORE / AFTER DDL
BEFORE / AFTER DISASSOCIATE STATISTICS
BEFORE / AFTER DROP
BEFORE / AFTER GRANT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER RENAME
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
AFTER SUSPEND 

details :

1. create tabel untuk menyimpan log hasil audit 
Create Table ddl_log
CREATE TABLE ddl_log (
operation   VARCHAR2(30),
obj_owner   VARCHAR2(30),
object_name VARCHAR2(30),
sql_text    VARCHAR2(64),
attempt_by  VARCHAR2(30),
attempt_dt  DATE); 

2. Create Trigger DDL
CREATE OR REPLACE TRIGGER EKOES.ddl_trigger
AFTER DDL
ON SCHEMA
DECLARE

 sql_text ora_name_list_t;
 i        PLS_INTEGER;
BEGIN
  i := sql_txt(sql_text);
    INSERT INTO ddl_log
    SELECT ora_sysevent, ora_dict_obj_owner,
    ora_dict_obj_name, sql_text(1), USER, SYSDATE
    FROM DUAL;
END ddl_trigger;
/

3. testing trigger
create table test1(col1 varchar2(30));
truncate table ddl_log;
alter table test1 add col2 number;
grant select on test1 to user1;
grant update on test1 to user2;
drop table test1;

hasil audit :
select * from ddl_log

OPERATION  OBJ_OW OBJECT_NAM SQL_TEXT                                 ATTEMP ATTEMPT_D
---------- ------ ---------- ---------------------------------------- ------ ---------
TRUNCATE   EKOES  DDL_LOG    truncate table ddl_log                   EKOES  03-MAY-12
ALTER      EKOES  TEST1      alter table test1 add col2 number        EKOES  03-MAY-12
GRANT      EKOES  TEST1      grant select on test1 to user1           EKOES  03-MAY-12
GRANT      EKOES  TEST1      grant update on test1 to user2           EKOES  03-MAY-12
DROP       EKOES  TEST1      drop table test1                         EKOES  03-MAY-12

Done

Dengan kaitkata , , , , , , , ,

One thought on “Membuat Trigger untuk audit DDL yang dilakukan User (Oracle Database)

  1. TARYO mengatakan:

    Mas, saya sudah mencoba untuk insert data ke sql text, dan berhasil. tapi ko isi didalamnya di batasi yah.. mohon bimbingan.

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: