Audit operasi di suatu table

Misalkan kita ingin tahu user-user mana saja yang melakukan perubahan (INSERT, UPDATE, DELETE) pada suatu tabel. Caranya, aktifkan parameter AUDIT_TRAIL, lakukan perintah audit, dan lihat hasil auditnya.

  1. Aktifkan parameter AUDIT_TRAIL. Ada tiga pilihan value, yaitu DB,OS, dan NONE. By default nilainya adalah NONE. Pilih OS kalau ingin hasil audit disimpan di suatu file, dan pilih DB kalau ingin hasil audit disimpan di tabel (database). Dalam contoh ini saya memilih option DB. Alasannya: lebih mudah melihat (me-manage) hasil auditnya.
    SQL> alter system set AUDIT_TRAIL=DB scope=spfile;
    SQL> shutdown immediate
    SQL> startup
  2. Lakukan perintah audit. Misalkan kita ingin mengaudit operasi INSERT, UPDATE, DELETE pada tabel pegawai (di schema test)
    SQL> AUDIT INSERT, UPDATE, DELETE ON test.pegawai
    BY ACCESS WHENEVER SUCCESSFUL;
  3. Lihat hasilnya
    SQL> select * FROM SYS.AUD$;
  4. Untuk meng-cancel audit
    SQL> NOAUDIT INSERT, UPDATE, DELETE ON test.pegawai;

Pertanyaan
Saya mau nanya nih soal audit trail, kira2 apa pro dan kon kalau tabel aud$ dipindah ke tablepsace yang lain? Dari artikel, sebagian DBA menyarankan agar dipindah, tetapi dari note Oracle sendiri, kalau dipindah dan suatu saat ada masalah, oracle tidak mau mensupport. Jadi gimana baiknya ya?

Jawaban
By default, tabel aud$ disimpan di tablespace SYSTEM. Dengan pertimbangan maintenance, kita bisa memindahkannya ke tablespace lain. Walaupun not supported, bahkan Oracle sendiri ngasih tahu (bikin step-step) cara untuk memindahkan tabel aud$ ke tablespace lain.

Adapun maksud dari “not supported” adalah Oracle tidak mensupport kalau-kalau nanti ada masalah di database yang disebabkan oleh pemindahan tabel aud$ ini.

Lantas, gimana baiknya? Salah satu pertimbangan utama memindahkan tabel aud$ ke tablespace lain adalah untuk mencegah agar tablespace SYSTEM tidak tumbuh (growth) cepat/besar hanya gara-gara tabel aud$ ini. Namun kita bisa mensiasatinya kok, kita bisa melakukan cleanup/purge terhadap tabel aud$ tersebut:

  1. Content dari tabel aud$ dicopy ke table lain, misal aud_history (yang ditaruh di tablespace lain)
  2. Setelah dicopy, record di tabel aud$ bisa di-delete

Catatan
Berikut ini Quote dari Document Oracle Metalink Note:72460.1

Important Note:
===============

It is strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package instead of using the method described in this document, see
Note 731908.1 “New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information”
for more information and references. Also please understand the movement of the
audit tables SYS.AUD$ (or SYSTEM.AUD$ in case of OLS) and FGA_LOG$ tables is supported
by the DBMS_AUDIT_MGMT package. But, this gives no support on adding triggers to AUD$ table.

Moreover many additional audit features such as Alerts are possible with Audit Vault and should not
need to be implemented on the source database with custom triggers but by using a supported product feature.

The procedure described in this note is not officially supported, because the Oracle code makes implicit assumptions about the data dictionary tables such as SYS.AUD$, which could cause problems with upgrades and backup / recovery scenarios. However, in most cases the procedure works as described. If you encounter problems using a trigger on the relocated AUD$ table Oracle Support may suggest to delete it.

In case you decide to move AUD$ to a different tablespace, and the tablespace or datafile where you relocated is unavailable, you will not be able to use audit anymore, therefore your applications might get errors such as ORA-9925 or ORA-9817 In such cases, please correct the problem before calling support.

For more information on this issue, please read Oracle 8i Administration Guide, Chapter 24 – “Auditing Database Use”, under topic “Controlling the growth and size of the audit trail”:

The maximum size of the database audit trail (SYS.AUD$ table) is predetermined during database creation. By default, up to 99 extents, each 10K in size, can be allocated for this table. You should not move SYS.AUD$ to another tablespace as a means of controlling the growth and size of the audit trail. However, you can modify the default storage parameters in SYS.AUD$.

Lebih banyak lagi tentang feature-feature audit, lihat di referensi berikut:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm

This entry was posted in Administration and tagged , , , , . Bookmark the permalink.

5 Responses to Audit operasi di suatu table

  1. andes says:

    pak rohmad,

    misal saya audit table emp pada user scott, kemudian saya kasih perintah update, delete dan insert pada table tersebut menggunakan user sys. ketika saya query table sys.aud$, ternyata user sys tidak tertangkap/terlihat, knp ya ?
    bisa ngga supaya user sys ini bisa tertangkap jg di table audit ? jadi semua user teraudit.

    terima kasih

  2. rohmadne says:

    Apakah edit pakai user lain (selain sys) ter-record di sys.aud$?

  3. andrea says:

    iya pak pakai user lain jg ga terecord di sys.aud$ kenapa ya pak

  4. Pingback: Petunjuk Memahami Database Oracle | Fariz Syahputra

  5. andri says:

    pak mau tanya, audit ini mencatat query yang bermasalah atau hanya menampilkan error code oracle nya saja ya ?
    terima kasih

Leave a Reply

Your email address will not be published. Required fields are marked *