Audit Perubahan DDL pada Oracle


Audit perubahan dalam database menjadi lebih dan lebih penting, seperti halnya audit perubahan data. 
Hal ini memungkinkan DBA untuk tidak hanya mengetahui perubahan apa yang telah dibuat, tetapi juga apa yang tidak. 
Dengan melihat perubahan apa yang telah dibuat ke database dapat membuat debug lebih cepat. 
Solusi ini terdiri dari dua tabel, satu sequence dan satu trigger. 


TABLE DAN SEQUENCE

CREATE TABLE DDL_EVENTS
( EVENTID          NUMBER(10,0),
  EVENTDATE        DATE,
  ORALOGINUSER     VARCHAR2(30),
  ORADICTOBJNAME   VARCHAR2(30),
  ORADICTOBJOWNER  VARCHAR2(30),
  ORADICTOBJTYPE   VARCHAR2(30),
  ORASYSEVENT      VARCHAR2(30),
  MACHINE          VARCHAR2(64),
  PROGRAM          VARCHAR2(64),
  OSUSER           VARCHAR2(30) );
 
CREATE TABLE DDL_EVENTS_SQL
( EVENTID          NUMBER(10,0),
  SQLLINE          NUMBER(10,0),
  SQLTEXT          VARCHAR2(4000) );
 
CREATE SEQUENCE DSQ_DDLEVENTS START WITH 1000; 

Tabel DDL_EVENTS menyimpan data tentang peristiwa DDL 
• EVENTID        : Primary key yang dihasilkan oleh sequence. 
• EVENTDATE        : diisi dengan SYSDATE. 
• ORALOGINUSER    : login username diambil dari fungsi ORA_LOGIN_USER. 
• ORADICTOBJNAME    : Nama objek event diambil dari fungsi ORA_DICT_OBJ_NAME. 
• ORADICTOBJOWNER    : Pemilik dari objek event yang diambil dari fungsi ORA_DICT_OBJ_OWNER. 
• ORADICTOBJTYPE    : Jenis objek event diambil dari fungsi ORA_DICT_OBJ_TYPE. 
• ORASYSEVENT    : Jenis aktivitas, seperti Create, Alter, Drop. Diambil dari fungsi ORA_SYS_EVENT. 
• MACHINE         : Nama mesin peristiwa dikeluarkan dari. Diambil dari V_ $ sesi. 
• PROGRAM        : Nama program gunakan untuk mengeluarkan perintah. Diambil dari V_ $ sesi. 
• OSUSER        : Sistem operasi nama pengguna. Diambil dari V_ $ sesi. 

Tabel DDL_EVENTS_SQL menyimpan teks dari perintah SQL. Ini adalah tabel anak dengan EVENTID DDL_EVENTS menjadi kunci asing.
SQLLINE dan EVENTID dapat digunakan sebagai unik identifikasi sebuah record. 
• EVENTID    : Link ke ddl_events 
• SQLLINE    : Jalur jumlah perintah yang dijalankan 
• SQLTEXT    : Sebuah baris teks perintah sql. Diambil dari fungsi ORA_SQL_TXT. 

TRIGGER 

CREATE OR REPLACE TRIGGER DTR_DDLEVENTS
AFTER DDL ON DATABASE
DECLARE
  L_EVENTID    NUMBER(10,0);
  L_SQLTEXT    ORA_NAME_LIST_T;
BEGIN
IF (ORA_LOGIN_USER <> ‘SYS’ AND ORA_LOGIN_USER IS NOT NULL) THEN
BEGIN
  SELECT DSQ_DDLEVENTS.NEXTVAL INTO L_EVENTID FROM SYS.DUAL;
  INSERT INTO DDL_EVENTS
  ( SELECT L_EVENTID,
           SYSDATE,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           MACHINE,
           PROGRAM,
           OSUSER
      FROM SYS.DUAL,
           SYS.V_$SESSION
     WHERE SYS_CONTEXT(‘USERENV’,’SESSIONID’ ) = AUDSID(+) );
  FOR L IN 1..ORA_SQL_TXT(L_SQLTEXT) LOOP
    INSERT INTO DDL_EVENTS_SQL
    ( EVENTID, SQLLINE, SQLTEXT )
    VALUES
    ( L_EVENTID, L, L_SQLTEXT(L) );
  END LOOP;
END;
END IF;
END;
/
 

Audit perubahan DDL dimudahkan oleh Trigger DDL. Solusi ini menggunakan klausa AFTER DDL ON DATABASE klausa. 

Ini akan mengaudit semua perubahan yang dibuat pada database. 

Jika Anda hanya ingin mengaudit skema tertentu maka gunakan klausa AFTER DDL ON SCHEMA sebagai gantinya. 

Trigger memasukkan data ke dalam ke dalam tabel DDL_EVENTS dengan data dari built-in functions and the v_$session view. 

Outer join pada the v_$session view memungkinkan bulti-in functions untuk tetap mengisi untuk background processes.


Tabel DDL_EVENTS_SQL kemudian diisi oleh fungsi ORA_SQL_TXT. loop di tabel DDL_EVENTS_SQL untuk setiap baris pernyataan. 

VARIASI 

Jika audit database terlalu banyak, satu skema dapat diaudit dengan menggunakan AFTER DDL ON SCHEMA di tempat AFTER DDL ON DATABASE. 
Jika audit teks sql memberi terlalu banyak data, kode ini dapat dengan mudah dihapus, atau dimodifikasi sehingga 4.000 karakter pertama dimasukkan ke dalam tabel DDL_EVENTS. 
Teks sql diatas sudah termasuk audit untuk perubahan password. Sebagai password sensitif Anda mungkin ingin menyembunyikan itu dari log. Anda dapat melakukan ini dengan memodifikasi teks sql loop seperti di bawah ini. 


   FOR L IN 1..ORA_SQL_TXT(L_SQLTEXT) LOOP
    IF  ORA_DICT_OBJ_TYPE = ‘USER’
    AND INSTR(UPPER(L_SQLTEXT(L)),’IDENTIFIED BY’) != 0
    THEN
      L_SQLTEXT(L) := SUBSTR(L_SQLTEXT(L),1,INSTR(UPPER(L_SQLTEXT(L)),’IDENTIFIED BY’)+13)||’*’;
    END IF;
    INSERT INTO DDL_EVENTS_SQL
    ( EVENTID, SQLLINE, SQLTEXT )
    VALUES
    ( L_EVENTID, L, L_SQLTEXT(L) );
  END LOOP;

Pernyataan tambahan jika pengguna memeriksa pernyataan-pernyataan yang berisi klausa IDENTIFIED BY, teks di sebelah kanan klausa kemudian digantikan dengan tanda bintang. 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s