Membuat Audit delete record log dengan trigger MS SQL 2005
Posted on December 23, 2007
Tulisan ini sebenarnya merupakan pemenuhan janji saya kepada salah satu peserta training yang ingin mengetahui tentang konsep Auditing Record Log di sebuah database MS SQL Server 2005 (any edition). Saya mohon maaf kepada Pak Indra yang sudah menanti cukup lama untuk tulisan ini 
.
Kita mulai dari cara yang sederhana yaitu dengan membuat sebuah database / menggunakan sebuah database yang sudah ada. Beberapa prasyarat awal untuk mengetahui lebih detail konsep ini bisa dimulai pemahaman tentang Trigger. Kenaapa menggunakan trigger ? Sebagai sebuah object database yang sebenarnya ‘berdiri sendiri’, trigger mempunyai kemampuan utk meng-antisipasi sebuah aksi yang dilakukan ke database / table dengan merespon balik berupa reaksi terhadap table itu sendiri (tempat trigger berada) / kepada object (table,view dll) yang juga terdapat di dalam database itu. Fungsi trigger sebenarnya mirip Check constraint tetapi trigger mempunya fungsionalitas yang lebih luas dibandingkan dengan Check Constraint.
Mari kita mulai pembahasan topik ini dengan membuat sebuah skenario berikut : Sebuah aplikasi berbasis windows based client dan database MS SQL 2005 sebagai backend-nya sering mendapatkan sebuah kasus dimana database-nya / tabel / record-nya tiba-tiba data-nya menghilang, sebagai seorang DBA tentunya kita harus mempunyai cara untuk mengetahui siapa, kapan, dan lewat mana data tersebut ‘terhapus’ entah itu sengaja atau tidak sengaja. Beberapa prasyarat untuk membuatnya adalah sebagai berikut :
- Tentukan terlebih dahulu mana tabel yang akan di audit, karena semakin banyak trigger yang harus di aktifkan di sebuah database dapat menurunkan performance dari database tersebut.
- Buat database dan table kosong yang baru di SQL 2005 dengan menggunakan SQL Management Studio nama-nya terserah anda : “testingAudit” dengan menggunakan konfigurasi standard :
create database testingAudit
go
create table testDelete
(
id_record varchar(5),
Deskripsi varchar(20)
)
go
‘insert data dummy ke table testDelete, jalankan script :insert into testDelete values (’ABC’,'testing 123′)
insert into testDelete values (’BCD’,'testing 124′)
insert into testDelete values (’CDE’,'testing 125′)
insert into testDelete values (’EFG’,'testing 126′)
insert into testDelete values (’FGH’,'testing 126′)
insert into testDelete values (’GHI’,'testing 127′)
insert into testDelete values (’HIJ’,'testing 128′)
insert into testDelete values (’IJK’,'testing 129′)
insert into testDelete values (’JKL’,'testing 130′)
insert into testDelete values (’KLM’,'testing 131′)
insert into testDelete values (’LMN’,'testing 132′)
insert into testDelete values (’MNO’,'testing 133′) - Setelah kita tentukan tabel mana yang akan di audit yaitu testdelete table, buatlah sebuah tabel yang dimanfaatkan sebagai audit log table yang di dalamnya terdapat beberapa informasi seperti : deleted_date_hour_minute,who_deleted,id_record_deleted,application. Berikut contoh script untuk membuat tabel-nya :
use testingAudit
go
create table log_record
(
dateDeleted datetime,
personWho varchar(30),
Id_record varchar(10)
) - Setelah table log dibuat, tahap berikut-nya adalah membuat trigger on delete di dalam table testDelete
Setiap perintah transaction (insert,update,delete) statement di table MS SQL Server pasti selalu muncul beberapa table sementara yang hanya muncul disaat eksekusi trigger. Table-table itu diantaranya adalah sebagai berikut :
- inserted (muncul pada saat terjadi proses transaksi perintah insert terhadap sebuah tabel)
- deleted (muncul pada saat terjadi proses transaksi perintah delete terhadap sebuah tabel)
- Sedangkan untuk transaksi Update , proses yang terjadi adalah deleted yang pertama kali di kerjakan baru setelah itu Inserted.Gambaran script untuk pembuatan trigger-nya adalah sebagai berikut :
create trigger tr_test on testdelete
for delete
as
declare @date1 datetime,@person varchar(30) , @id varchar(10)
set @date1 = getdate()
set @person = suser_sname()
select @id = id_record from deleted
insert into log_record values (@date1,@person,@id) - Pastikan trigger tr_test sudah terdapat dalam tabel testDelete, dengan menggunaan SSMS (SQL Server Management Studio).
- Coba jalankan perintah delete terhadap 1 record di tabel testDelete kemudian cek apakah log_record table telah terisi siapa, kapan dan record mana yang terhapus.
- Tolong dipastikan juga bahwa user yang authorized delete record di testDelete bisa melakukan perintah insert di table log_record.
- Selamat mencoba.
Feel free your comment,
Sony
» Filed Under Database
Comments
8 Responses to “Membuat Audit delete record log dengan trigger MS SQL 2005”
Leave a Reply
Halo Pak, tulisan anda menarik, dan sangat membantu bagi kami yang baru di bidang ini, terima kasih.
Mau nanya pak, kalo audit trailnya pake trigger, brarti g bisa mendeteksi kegiatan yang dilakukan spesifik (terutama delete) oleh user dari aplikasi dunk? (pembatasan user dari aplikasi) Kalo ia, pemecahan terbaiknya bagaimana pak?
Apakah setiap user pada aplikasi harus menggunakan koneksi dengan user (user database) yang berbeda juga? (repot ngatur usernya)
Atau auditnya manual coding dari aplikasi saja? (repot pengembangannya)
Ataukan ada solusi lain yang lebih efektif?
Oke pak, mohon pencerahannya, terima kasih.
Itu tergantung dari type security connection db yang digunakan di aplikasi apakah windows auth. atau sql auth. Kalo yang dipakai sql auth. memang agak susah tapi kalo wind auth bisa ketahuan user-nya. Kalo pingin gampang bisa menggunakan konsep roles (kelompok) utk assign orang-orang-nya.
Nimbrung dikit..
untuk mendapatkan user dari aplikasi, langkah yang paling mudah adalah dengan menambahkan Field UpdateBy di semua table misal:
create table testDelete
(
Id_Record VARCHAR(5),
Deskripsi VARCHAR(20),
UpdateBy VARCHAR(15)
)
, Kemudian pada trigger diubah menjadi
select @person = UpdateBy from deleted
select @id = id_record from deleted
Saran saya, pembuatan trigger sebaiknya dengan menggunakan cursor untuk me-looping semua isi table deleted, pada contoh diatas, jika ada perintah yang menghapus lebih dari 1 row, maka yang akah masuk ke log hanya record pertama saja.
Semoga membantu
numpang tanya nih mas, bagai mana yg mas menghapus log yg lama (ldf), dan membuat log baru (ldf )yg baru pada sql server 2005, saya pernah coba di sql 2000 bisa tapi di sql 2005 ngak bisa
makasih sebelumnya mas.
hormat saya
tolong balas ke email saya
ferdian9281@yahoo.com
sori saya baru liat comment-nya. udah solve belum masalah-nya ?
Mas, thx atas tutorial nya
saya dah testing tutorial triger ini. tapi adahal yg menjadi pertanyaan saya.
1. ketika saya hapus satu record isi dari tabel, terlihat ada log di tabel log nya. tpi ketika hapus smua record , yg tercatat hanya satu log saja ( record yg paling atas saja yg tercata log nya). bagaimana utk mengetahui log setiap record yg di delete ??
2. kalau utk triger update table gmana ??
maaf pak…..saya mau tanya, bukannya kalo didalam triger itu untuk mengubah ditabel yang dituju itu harus menggunakan new.nama_kolom, saya masih g negrti soalnya saya baru mendapat mata kuliah ini dna saya rasa dnegan adanya tulisan ini sangat membantu saya untuk mengembangkan kemampuan dibindnag database.
thx..
cici : saya juga kurang mengerti pertanyaan anda berikut-nya. Silakan mendetailkan pertanyaan anda.