Penerapan Trigger untuk Logging Akses Basis Data pada Microsoft SQL Server

Dalam beberapa kasus, akses ke dalam basis data perlu dicatat untuk memantau siapa saja yang mengakses basis data, jumlah akses yang dilakukan, dan kapan akses dilakukan. Informasi tersebut dapat diperoleh dengan melakukan pencatatan saat akses ke basis data dilakukan. Proses pencatatan tersebut sering disebut dengan istilah logging.

Trigger merupakan salah satu objek basis data yang dimiliki oleh Microsoft SQL Server. Trigger dapat digunakan untuk mencatat akses yang terjadi ke basis data. Pada SQL Server, selain menggunakan trigger, logging juga dapat dilakukan dengan menggunakan SQL Server Error Log, yaitu log yang mencatat setiap error yang terjadi dalam basis data. Dengan melakukan sedikit pengaturan, maka log tersebut dapat digunakan untuk mencatat informasi akses basis data.

Jadi, mengapa logging dengan trigger diperlukan jika logging bisa dilakukan dengan SQL Server Error Log?

Pada SQL Server, error log (SQL Server Log) hanya mencatat informasi tertentu saja, yaitu tanggal, sumber informasi, pesan error, jenis log, dan nama log. Pada pesan error terdapat login name yang digunakan untuk mengakses basis data, jenis login, dan alamat IP pengguna. Informasi tersebut disimpan dalam satu kalimat (yang bertipe string).

Permasalahan muncul saat kita ingin menggunakan informasi akses dalam sebuah aplikasi untuk memperoleh nama pengguna, jenis login, dan lainnya. Jika kita menggunakan informasi dari error log tersebut, maka kita akan kesulitan jika hanya ingin menggunakan informasi tertentu saja. Contohnya jika kita ingin memperoleh login name dari pesan error tersebut, mungkin kita harus menerapkan Natural Languange Processing.

Oleh karena itu, ada sebuah alternatif lain untuk dapat mencatat informasi akses, yaitu dengan menggunakan trigger. Trigger pada Microsoft SQL Server pada umumnya dibagi menjadi tiga jenis, yakni:

• DML Trigger
• DDL Trigger
• Logon Trigger

Jenis Trigger yang dapat digunakan untuk mencatat akses adalah dengan menggunakan Logon Trigger. Trigger tersebut dapat dibuat dengan cara berikut ini.

CREATE TRIGGER [Nama_Trigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN
/*SQL Statement untuk pencatatan informasi*/
END

SQL Statement dapat diisi dengan perintah INSERT untuk menuliskan hasil pencatatan ke dalam tabel.  Trigger tersebut dapat ditemukan pada Object Explorer yaitu pada Database Instance > Server Objects > Triggers. Informasi yang dicatat dapat berupa login name, event time, client host, host name, dan application name. Salah satu contoh adalah sebagai berikut.

CREATE TRIGGER [DatabaseAccessTrigger] /* Creates trigger for logons */
ON ALL SERVER 
FOR LOGON
AS
 
BEGIN
DECLARE @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@ClientHost varchar(50),
@LoginType varchar(50),
@HostName varchar(50),
@AppName varchar(500)
 
SET @LogonTriggerData = eventdata()
 
SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
SET @HostName = HOST_NAME()
SET @AppName = APP_NAME()
 
INSERT INTO NorthwindAudit.[dbo].DatabaseAccessLog
(
 LoginName,
 ProgramName,
 AccessTime
)
SELECT
@LoginName,
@AppName,
@EventTime

END

Trigger tersebut akan menuliskan hasil ke dalam tabel sebagai berikut.

Dengan demikian, informasi dapat diambil langsung hanya dengan menggunakan perintah SELECT biasa dari tabel.

Sumber:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017