Membuat Plugin MySQL Yang Menulis Hasil Audit Ke Windows Event Log

Kode program untuk artikel ini juga bisa dibaca di https://github.com/JockiHendry/mysql_windows_audit_plugin.

Pada artikel Memahami Authentication dan Auditing Di MySQL Server, saya menggunakan MariaDB Audit Plugin di MySQL Server untuk melakukan auditing dan menuliskan hasilnya dalam bentuk file. Bila saya memberikan perintah seperti SET GLOBAL server_audit_output_type = 'SYSLOG' maka MariaDB Audit Plugin akan menulis hasil audit ke dalam syslog. Sayangnya ini hanya berlaku khusus untuk sistem operasi yang mendukung syslog seperti Linux. Fitur ini tidak akan menulis ke Windows Event Log!

Menampilkan hasil audit di Event Viewer akan memberikan lebih banyak flekibilitas dibandingkan dengan melihat log dalam bentuk file. Selain itu, dari sisi keamanan, file bisa di-edit secara leluasa oleh penyerang (misalnya menghilangkan baris yang mencurigakan). Hal ini tidak terjadi di Event Viewer karena log bersifat read-only dan hanya bisa dihapus secara global (log yang tiba-tiba hilang semua pasti mencurigakan!).

Apakah mungkin membuat MySQL Server menulis hasil audit ke Event Viewer? Yup, ini bisa dilakukan karena MySQL Server dapat di-extend dengan mekanisme plugin. Saya hanya perlu membuat sebuah auditing plugin untuk MySQL Server. Saya sudah menuliskan contoh kode program yang memakai Windows Event Log di artikel Membuat Program Visual C++ Yang Menulis Ke Windows Event Log.

Saya akan mulai dengan membuka Visual C++ 2010 dan membuat sebuah Win32 Project baru yang saya beri nama sebagai mysql_windows_audit. Pada kotak dialog yang muncul, saya memilih jenis aplikasi DLL dan Empty Project seperti yang terlihat pada gambar berikut ini:

Membuat proyek baru.

Membuat proyek baru.

Saya perlu menambahkan sebuah file kode program C dengan men-klik kanan pada Source Files dan memilih menu Add, New Item…. Pada kotak dialog yang muncul, saya memilih C++ File (.cpp). Agar jelas bahwa saya ingin memakai bahasa pemograman C, saya mengisi nama file dengan windows_audit.c sebelum men-klik tombol Add.

Sebelum mulai membuat kode program, saya perlu melakukan beberapa pengaturan. Untuk itu, saya men-klik kanan nama proyek dan memilih Properties. Setelah itu, saya beralih ke konfigurasi Release. Pada bagian C/C++, General, saya perlu menyertakan header file dari MySQL. Secara default, lokasi ini adalah C:\Program Files\MySQL\MySQL Server 5.6\include:

Melakukan pengaturan proyek.

Melakukan pengaturan proyek.

Selanjutnya, pada bagian C/C++, Preprocessor, saya menambahkan MYSQL_DYNAMIC_PLUGIN di bagian Preprocessor Definitions. Saya juga memastikan bahwa saya pada C/C++, Precompiled Headers, nilai untuk Precompiled Header adalah Not Using Precompiled Headers. Setelah itu, saya men-klik tombol OK untuk menutup dialog.

Karena memakai Windows Event Log, saya perlu membuat instrumentation manifest, misalnya sebuah file XML dengan nama message.man yang isinya seperti berikut ini:

<?xml version="1.0" encoding="UTF-16"?>
<instrumentationManifest xsi:schemaLocation="http://schemas.microsoft.com/win/2004/08/events eventman.xsd" xmlns="http://schemas.microsoft.com/win/2004/08/events" xmlns:win="http://manifests.microsoft.com/win/2004/08/windows/events" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:trace="http://schemas.microsoft.com/win/2004/08/events/trace">
    <instrumentation>
        <events>
            <provider name="MySQLWindowsAuditProvider" guid="{566E77CB-DC32-4B2D-A0CA-01CDF9BEC031}" symbol="MYSQL_WINDOWS_AUDIT_PROVIDER" resourceFileName="C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin\mysql_windows_audit.dll" messageFileName="C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin\mysql_windows_audit.dll">
                <events>
                    <event symbol="Connect" value="1" version="1" channel="MySqlWindowsAudit" level="win:Informational" template="ConnectionTemplate" message="$(string.MySQLWindowsAuditProvider.event.1.message)">
                    </event>
                    <event symbol="Disconnect" value="2" version="1" channel="MySqlWindowsAudit" level="win:Informational" template="ResultTemplate" message="$(string.MySQLWindowsAuditProvider.event.2.message)">
                    </event>
                    <event symbol="Change" value="3" version="1" channel="MySqlWindowsAudit" level="win:Informational" template="ConnectionTemplate" message="$(string.MySQLWindowsAuditProvider.event.3.message)">
                    </event>
                    <event symbol="Error" value="4" version="1" channel="MySqlWindowsAudit" level="win:Error" template="ConnectionTemplate" message="$(string.MySQLWindowsAuditProvider.event.4.message)">
                    </event>
                    <event symbol="ActiveChanged" value="5" version="1" channel="MySqlWindowsAudit" level="win:Warning" template="BooleanUpdateTemplate" message="$(string.MySQLWindowsAuditProvider.event.5.message)">
                    </event>
                </events>
                <levels>
                </levels>
                <channels>
                    <channel name="MySqlWindowsAudit" chid="MySqlWindowsAudit" symbol="MYSQL_WINDOWS_AUDIT" type="Operational" enabled="true" message="$(string.MySQLWindowsAudit.channel.MYSQL_WINDOWS_AUDIT.message)">
                    </channel>
                </channels>
                <templates>
                    <template tid="ConnectionTemplate">
                        <data name="status" inType="win:Int32" outType="xs:int">
                        </data>
                        <data name="user" inType="win:AnsiString" outType="xs:string">
                        </data>
                        <data name="host" inType="win:AnsiString" outType="xs:string">
                        </data>
                        <data name="ip" inType="win:AnsiString" outType="xs:string">
                        </data>
                        <data name="database" inType="win:AnsiString" outType="xs:string">
                        </data>
                    </template>
                    <template tid="ResultTemplate">
                        <data name="status" inType="win:Int32" outType="xs:int">
                        </data>
                    </template>
                    <template tid="BooleanUpdateTemplate">
                        <data name="var" inType="win:AnsiString" outType="xs:string">
                        </data>
                        <data name="active" inType="win:Boolean" outType="xs:boolean">
                        </data>
                    </template>
                </templates>
            </provider>
        </events>
    </instrumentation>
    <localization>
        <resources culture="en-US">
            <stringTable>
                <string id="level.Warning" value="Warning">
                </string>
                <string id="level.Informational" value="Information">
                </string>
                <string id="level.Error" value="Error">
                </string>
                <string id="MySQLWindowsAuditProvider.event.5.message" value="%1 has been changed to %2.">
                </string>
                <string id="MySQLWindowsAuditProvider.event.4.message" value="Connection error for %2 (%4) at database %5 at %3.  Status: %1.">
                </string>
                <string id="MySQLWindowsAuditProvider.event.3.message" value="Change user for %2 (%4) at database %5 at %3.  Status: %1.">
                </string>
                <string id="MySQLWindowsAuditProvider.event.2.message" value="User disconnect with the following status: %1.">
                </string>
                <string id="MySQLWindowsAuditProvider.event.1.message" value="Connection from %2 (%4) to database %5 at %3.  Status: %1.">
                </string>
                <string id="MySQLWindowsAudit.channel.MYSQL_WINDOWS_AUDIT.message" value="MySQL Connection Audit">
                </string>
            </stringTable>
        </resources>
    </localization>
</instrumentationManifest>

Saya mengisi nilai resourceFileName dan messageFileName dengan "C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin\mysql_windows_audit.dll". Ini adalah file DLL yang akan dihasilkan oleh proyek yang sedang saya buat. Bila seandainya plugin di-install di tempat yang terpisah, saya perlu mengubah nilai tersebut.

File di atas mendefinisikan 5 jenis event yang bisa dihasilkan oleh program. Event Connect, Disconnect dan Change mewakili aktifitas koneksi, diskoneksi dan perubahan user. Ketiga event ini memiliki level Information. Event Error akan terjadi terdapat kesalahan yang berhubungan dengan koneksi (misalnya pengguna login dengan password yang salah atau mengakses database yang salah). Dan terakhir, event ActiveChanged yang memiliki level Warning akan terjadi bila pengguna mematikan atau mengaktifkan fasilitas logging dari plugin ini.

Setelah memberikan perintah MC.exe, saya segera menambahkan referensi ke file yang dihasilkan sehingga struktur proyek terlihat seperti pada gambar berikut ini:

Struktur proyek

Struktur proyek

Sekarang, saatnya untuk menulis kode program! Karena tutorial menulis plugin MySQL tidak begitu banyak, saya akan memakai kode program MariaDB Audit Plugin sebagai sumber referensi. Kode program MariaDB Audit Plugin bisa dilihat di https://github.com/MariaDB/server/blob/10.1/plugin/server_audit/server_audit.c. Selain itu, saya memakai proyek audit_null yang bisa dilihat di https://github.com/mysql/mysql-server/tree/5.7/plugin/audit_null sebagai skeleton awal. Tentu saja karena saya membuat fitur yang hanya jalan di Windows, saya bisa memakai API khusus Windows dan tidak perlu memikirkan kode program yang cross-platform.

Sebagai contoh, saya membuat file windows_audit.c yang isinya seperti berikut ini:

#include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include "message.h"
#include <mysql/plugin.h>
#include <mysql/plugin_audit.h>

static char active = 1;
static long connection_errors;
static int internal_stop_logging = 0;
static CRITICAL_SECTION cs;
static const char* nullString = "null";

static struct st_mysql_show_var audit_status[] = {  
    {"windows_audit_connection_errors", (char*) &connection_errors, SHOW_LONG},
    {0,0,0}
};

static void update_active(MYSQL_THD thd, struct st_mysql_sys_var *var, void *var_ptr, const void *save) {   
    char new_is_active = *(char*) save;
    if (new_is_active == active) return;
    EnterCriticalSection(&cs);
    internal_stop_logging = 1;
    active = new_is_active; 
    EventWriteActiveChanged("windows_audit_active", active);
    internal_stop_logging = 0;
    LeaveCriticalSection(&cs);
}

static MYSQL_SYSVAR_BOOL(active, active, PLUGIN_VAR_OPCMDARG, "Turn on/off the logging.", NULL, update_active, 1);

static struct st_mysql_sys_var* vars[] = {
    MYSQL_SYSVAR(active),
    NULL
};

static int windows_audit_plugin_init(void *arg)
{   
    if (EventRegisterMySQLWindowsAuditProvider() != ERROR_SUCCESS) {
        fwprintf(stderr, L"Can't register Windows log provider.n");
        return -1;
    }
    InitializeCriticalSection(&cs); 
    connection_errors = 0;
    fwprintf(stderr, L"Windows Audit Plugin STARTED.n");  
    return 0;
}

static int windows_audit_plugin_deinit(void *arg)
{   
    if (EventUnregisterMySQLWindowsAuditProvider() != ERROR_SUCCESS) {
        fwprintf(stderr, L"Can't unregister Windows log provider.n");
        return -1;
    }
    DeleteCriticalSection(&cs);
    fwprintf(stderr, L"Windows Audit Plugin STOPPED.n");
    return 0;
}

static const char* new_cstr(const char* str, ULONG size) {      
    char* result = (char*) malloc(sizeof(char) * ((size==0) ? sizeof(nullString) : size) + 1);
    memcpy(result, (size==0) ? nullString : str, ((size==0) ? sizeof(nullString) : size) + 1);  
    return result;
}

static void event_log(PCEVENT_DESCRIPTOR descriptor, EVENT_DATA_DESCRIPTOR* data, const struct mysql_event_connection* connEvent) {
    const char* user, *host, *ip, *database;
    EventDataDescCreate(&data[0], &(connEvent->status), sizeof(const signed int)  );

    user = new_cstr(connEvent->user, connEvent->user_length);                                             
    EventDataDescCreate(&data[1], user, strlen(user)+1);

    host = new_cstr(connEvent->host, connEvent->host_length);
    EventDataDescCreate(&data[2], host, strlen(host)+1);

    ip = new_cstr(connEvent->ip, connEvent->ip_length);
    EventDataDescCreate(&data[3], ip, strlen(ip)+1);

    database = new_cstr(connEvent->database, connEvent->database_length);                                                         
    EventDataDescCreate(&data[4], database, strlen(database)+1);

    EventWrite(MySQLWindowsAuditProviderHandle, descriptor, 5, data);

    free((void*) user);
    free((void*) host);
    free((void*) ip);
    free((void*) database);
}

static void windows_audit_notify(MYSQL_THD thd, unsigned int event_class, const void *e)
{ 
    const struct mysql_event_connection *connEvent; 
    EVENT_DATA_DESCRIPTOR eventData[5];

    if (internal_stop_logging || !active) return;   
    EnterCriticalSection(&cs);  

    if (event_class == MYSQL_AUDIT_CONNECTION_CLASS) {
        internal_stop_logging = 1;  
        connEvent = (const struct mysql_event_connection *) e;

        if (connEvent->status > 0) {
            event_log(&Error, eventData, connEvent);
            connection_errors++;
        } else {
            switch (connEvent->event_subclass) {
                case MYSQL_AUDIT_CONNECTION_CONNECT:
                    event_log(&Connect, eventData, connEvent);
                    break;
                case MYSQL_AUDIT_CONNECTION_DISCONNECT:      
                    EventWriteDisconnect(connEvent->status);
                    break;
                case MYSQL_AUDIT_CONNECTION_CHANGE_USER:     
                    event_log(&Change, eventData, connEvent);
                    break;
                default:                    
                    break;          
            }
        }
        internal_stop_logging = 0;
    }

    LeaveCriticalSection(&cs);
}


/*
  Plugin type-specific descriptor
*/

static struct st_mysql_audit windows_audit_descriptor=
{
  MYSQL_AUDIT_INTERFACE_VERSION,                       /* interface version    */
  NULL,                                                /* release_thd function */
  windows_audit_notify,                                /* notify function      */
  { (unsigned long) MYSQL_AUDIT_CONNECTION_CLASSMASK } /* class mask           */
};

/*
  Plugin library descriptor
*/

mysql_declare_plugin(windows_audit)
{
  MYSQL_AUDIT_PLUGIN,                       /* type                            */
  &windows_audit_descriptor,                /* descriptor                      */
  "WINDOWS_AUDIT",                            /* name                            */
  "Jocki Hendry",                         /* author                          */
  "Audit connections to Windows Log",     /* description                     */
  PLUGIN_LICENSE_GPL,
  windows_audit_plugin_init,                /* init function (when loaded)     */
  windows_audit_plugin_deinit,              /* deinit function (when unloaded) */
  0x0001,                                   /* version                         */
  audit_status,                             /* status variables                */
  vars,                                     /* system variables                */
  NULL,
  0,
}
mysql_declare_plugin_end;

Bagian yang diapit oleh macro mysql_declare_plugin(windows_audit) dan mysql_declare_plugin_end; berisi informasi mengenai plugin yang saya buat. Disini saya juga mendaftarkan function windows_audit_plugin_init() sebagai function yang akan dikerjakan saat plugin dimulai (misalnya saat database dinyalakan) dan function windows_audit_plugin_deinit() sebagai function yang akan dikerjakan saat plugin dimatikan. Selain itu, saya juga mendaftarkan audit_status sebagai status yang bisa dilihat melalui perintah SHOW STATUS. Saya juga mendaftarkan vars sebagai variabel yang bisa diatur melalui perintah SET GLOBAL.

MySQL mendukung beberapa jenis plugin. Khusus untuk audit plugin, saya perlu membuat descriptor dengan tipe st_mysql_audit. Sebagai contoh, pada kode program saya, ini diwakili oleh variabel windows_audit_descriptor. Pada descriptor ini, saya mendaftarkan function windows_audit_notify() sebagai function yang akan dipanggil setiap kali ada aktifitas audit. Saya hanya memakai MYSQL_AUDIT_CONNECTION_CLASSMASK sebagai class mask sehingga hanya informasi yang berkaitan dengan koneksi saja yang akan di-audit.

Bagian kode program yang mencatat ke Windows Event Log terdapat di windows_audit_notify(). Pada function ini, saya akan memperoleh informasi koneksi yang sedang di-audit dalam bentuk argumen bertipe mysql_event_connection. Variabel tersebut mengandung informasi seperti status kesalahan (atau sukses), nama user, ip user, database yang hendak diakses dan lokasi host database.

Seusai membuat kode program, saya perlu men-build proyek untuk menghasilkan file DLL. Setelah itu, saya perlu memanggil Wevtutil.exe dan men-copy file DLL ke lokasi plugin di instalasi MySQL Server. Karena langkah-langkah ini akan selalu saya kerjakan setiap kali melihat hasil perubahan program, maka saya membuat sebuah batch file bernama install.bat untuk mengotomatisasikannya:

@echo off

:shutdown_mysql
tasklist | find "mysqld.exe" > nul
if errorlevel 1 goto :uninstall_manifest
echo Shutting down mysqld.exe.
taskkill /f /im mysqld.exe

:uninstall_manifest
wevtutil gp MySQLWindowsAuditProvider 2> nul | find ":" > nul
if errorlevel 1 goto :install_manifest
echo Uninstall information manifest for MySQLWindowsAuditProvider
wevtutil um message.man

:install_manifest
echo Install manifest for MysQLWindowsAuditProvider
wevtutil im message.man

:copy dll file to plugin location
set mysql_plugin_dir="C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin"
echo Copy %1 to %mysql_plugin_dir%
copy %1 %mysql_plugin_dir% > nul

Setelah itu, saya men-klik kanan nama proyek dan memilih Properties. Pada dialog yang muncul, saya memilih Configuration Properties, Build Events, Post-Build Event. Setelah itu, saya mengisi bagian Command Line dengan nilai seperti berikut ini:

Mengatur build event di proyek Visual Studio

Mengatur build event di proyek Visual Studio

Sekarang, Visual Studio akan secara otomatis mengerjakan install.bat seusai menghasilkan file DLL. Untuk menghasilkan file DLL, saya segera memilih menu Build, Build Solution.

Untuk menguji plugin yang telah dibuat, saya segera menjalankan MySQL Server dan men-install plugin dengan memberikan perintah SQL seperti berikut ini (hanya perlu dilakukan bila belum pernah di-install sebelumnya!):

INSTALL PLUGIN WINDOWS_AUDIT SONAME 'mysql_windows_audit.dll';

Untuk memastikan plugin sudah aktif, saya dapat menggunakan perintah SHOW PLUGINS seperti berikut ini:

mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+-------------------------+---------+
| Name                       | Status   | Type               | Library                 | License |
+----------------------------+----------+--------------------+-------------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                    | GPL     |
| WINDOWS_AUDIT              | ACTIVE   | AUDIT              | mysql_windows_audit.dll | GPL     |
+----------------------------+----------+--------------------+-------------------------+---------+

Bila saya membuka Windows Event Viewer, saya akan menjumpai channel baru bernama MySQLWindowsAuditProvider. Semua hasil audit akan tersimpan di channel ini, seperti yang terlihat pada gambar berikut ini:

Contoh log yang dihasilkan di Event Viewer

Contoh log yang dihasilkan di Event Viewer

Selain itu, saya juga bisa melihat jumlah koneksi yang gagal dilakukan sejak database dinyalakan dengan memberikan perintah SQL seperti berikut ini:

mysql> SHOW STATUS LIKE 'windows_audit_%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| windows_audit_connection_errors | 1     |
+---------------------------------+-------+

Saya juga bisa mematikan proses audit yang dilakukan oleh plugin ini dengan memberikan perintah SQL seperti berikut ini:

mysql> SET GLOBAL windows_audit_active = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'windows_audit_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| windows_audit_active | OFF   |
+----------------------+-------+

Perubahan pada variabel ini juga akan menimbulkan sebuah log baru di Event Viewer, seperti yang terlihat pada gambar berikut ini:

Log yang muncul jika status audit berubah.

Log yang muncul jika status audit berubah.

Berkat fasilitas plugin dari MySQL Server, saya memperoleh kemampuan auditing sesuai dengan yang saya butuhkan secara mudah ūüôā

Iklan

Memahami Authentication dan Auditing Di MySQL Server

Salah satu yang unik pada MySQL Server adalah nama akun untuk pengguna memiliki syntax seperti 'nama_user'@'nama_host'. Dengan demikian, sebuah nama_user yang sama boleh dipakai lebih dari sekali untuk nama_host yang berbeda. Bahkan nilai nama_user boleh dikosongkan (anonymous user) sehingga nama_host tertentu boleh mengakses database tanpa perlu memberikan nama_user.

Nilai nama_host dipakai untuk melakukan validasi host asal dimana user melakukan koneksi. Bila saya tidak menyertakan nama_host pada saat membuat pengguna, maka nilai tersebut dianggap sebagai % yang berarti pengguna boleh melakukan koneksi dari mana saja.

Sebagai contoh, saya bisa membuat sebuah user yang hanya boleh diakses dari IP 192.168.56.0 sampai 192.168.56.255 dengan menggunakan perintah SQL seperti berikut ini:

CREATE USER 'inventory'@'192.168.56.0/255.255.255.0';

Tentu saja membatasi host asal tidak cukup! Saya perlu memberikan password sehingga pengguna perlu memberikan nilai password yang benar saat melakukan koneksi. Untuk itu, saya bisa menambahkan klausa IDENTIFIED BY seperti pada perintah SQL berikut ini:

CREATE USER 'inventory'@'192.168.56.0/255.255.255.0'
IDENTIFIED BY 'password_12345';

Salah satu masalah dengan perintah di atas adalah saya mengetik nilai password secara polos. Ini memiliki resiko tersendiri, terutama bila saya menyimpan perintah ini dalam file SQL sebagai cadangan untuk dikerjakan suatu hari nanti. Oleh sebab itu, akan lebih baik bila saya menyimpan password dalam bentuk hash. Untuk mengetahui nilai hash dari sebuah password polos, saya dapat memberikan perintah SQL berikut ini:

SELECT PASSWORD('password_12345');

Saya kemudian bisa memakai hash yang dihasilkan perintah di atas sebagai password pada saat membuat user:

CREATE USER 'inventory'@'192.168.56.0/255.255.255.0' 
IDENTIFIED BY PASSWORD '*E758CB9320D032FDC632D7FAB56BE5A50920BB89';

Untuk tingkat keamanan yang lebih tinggi, MySQL Server sejak versi 5.6.6 memiliki fitur sha256_password (http://dev.mysql.com/doc/refman/5.6/en/sha256-authentication-plugin.html) sebagai authentication plugin. Plugin ini mensyaratkan penggunaan SSL atau RSA pada saat melakukan koneksi guna meng-enkripsi password yang dikirim dari client ke server. MySQL Community Edition (versi gratis yang di-build dengan yaSSL) tidak mendukung enkripsi RSA sehingga untuk menggunakan sha256_password dibutuhkan SSL. Penggunaan SSL bisa membuat database menjadi lebih lambat akibat beban enkripsi & deskripsi koneksi. Ini adalah pengorbanan yang wajar bila menginginkan keamanan yang lebih baik. Sementara itu, MySQL Enterprise Edition (di-build dengan OpenSSL) memiliki dukungan enkripsi RSA sehingga sha256_password dapat dipakai tanpa mengaktifkan SSL.

Selain itu, bagi pengguna Windows yang memakai MySQL 5.6.10 versi berbayar, terdapat Windows Native Authentication Plugin dimana pengguna dapat login ke server database berdasarkan informasi akun Windows. Dengan demikian, pengguna tidak perlu memberikan password lagi karena ia akan dikenali berdasarkan akun di sistem operasi Windows yang sedang dipakainya.

Langkah berikutnya setelah membuat pengguna adalah menentukan apa saja yang boleh diakses oleh penguna tersebut. Secara default, pengguna yang baru dibuat hanya memiliki akses ke database information_schema dan test. Pengguna tersebut juga tidak memiliki hak untuk membuat database baru. Oleh sebab itu, saya perlu memberikan hak akses atas sebuah database yang sudah ada dengan memberikan perintah SQL seperti berikut ini:

GRANT ALL ON inventory.* TO 'inventory'@'192.168.56.0/255.255.255.0';

Selain itu, bila saya ingin seluruh komputer dengan IP 192.168.56.0 sampai 192.168.56.255 boleh membaca isi tabel pesan di database inventory dan tidak boleh menghapus atau mengubah isinya, saya dapat memberikan SQL seperti berikut ini:

CREATE USER ''@'192.168.56.0/255.255.255.0';
GRANT SELECT ON inventory.pesan TO ''@'192.168.56.0/255.255.255.0';

Perintah SQL di atas akan menyebabkan komputer dengan IP 192.168.56.0 sampai 192.168.56.255 boleh melakukan koneksi ke database tanpa harus memberikan nama user dan password. Tetapi, bila melakukan koneksi secara anonymous seperti ini, mereka hanya bisa melihat isi tabel pesan saja:

C:\>mysql -h mydatabase.com

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| inventory          |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> use inventory;
Database changed

mysql> show tables;
+---------------------+
| Tables_in_inventory |
+---------------------+
| pesan               |
+---------------------+
1 row in set (0.00 sec)

mysql> delete from pesan;
ERROR 1142 (42000): DELETE command denied to user ''@'192.168.56.30' for table 'pesan'

Salah satu fasilitas lainnya yang penting dalam mengamankan database adalah auditing. Fasilitas ini memungkinkan database administrator untuk mengetahui kapan pengguna melakukan koneksi dan apa saja query yang diberikan pengguna tersebut. Pada MySQL Server, auditing dilakukan dengan bantuan Audit Server Plugin. Sayang sekali, walaupun MySQL Server menyediakan API untuk menulis Audit Server Plugin, satu-satunya implementasi resmi berupa MySQL Audit Log Plugin hanya tersedia di MySQL Enterprise Edition (versi berbayar).

Sebagai alternatif untuk melakukan auditing tanpa harus beralih ke versi berbayar, saya dapat menggunakan Audit Plugin dari MariaDB di MySQL Server. Walaupun merupakan bagian dari MariaDB, Audit Plugin diprogram berdasarkan Audit Server Plugin API dari MySQL Server dan telah diuji untuk MySQL Server. Tentu saja alasan utama lain untuk memakainya di MySQL Server adalah karena ia dapat di-download secara gratis di https://mariadb.com/products/connectors-plugins.

Pada saat tulisan ini dibuat, versi terakhir Audit Plugin dari MariaDB adalah 1.1.8. Setelah men-download dan men-extract file server_audit-1.1.8.zip, saya perlu memindahkan file server_audit.dll di folder windows-32 (karena saya memakai Windows 32-bit) ke lokasi direktori plugin milik MySQL Server. Saya dapat mengetahui lokasi direktori plugin dengan memberikan perintah SQL seperti berikut ini:

SHOW VARIABLES LIKE 'plugin_dir';

Setelah men-copy file server_audit.dll pada direktori bersangkutan, saya dapat men-install plugin dengan memberikan perintah SQL seperti berikut ini:

INSTALL PLUGIN server_audit SONAME 'server_audit.dll';

Setelah ini, MariaDB Audit Plugin akan aktif. Saya akan menemukan beberapa variabel baru seperti berikut ini:

mysql> SHOW VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.00 sec)

Karena nilai server_audit_logging adalah OFF, saya perlu mengaktifkannya dengan memberikan perintah SQL berikut ini:

SET GLOBAL server_audit_logging = ON;

Untuk perubahan yang permanen, saya perlu menyimpan nilai variabel di file konfigurasi my.cnf.

Sekarang, saya dapat melihat status auditing dengan memberikan perintah SQL seperti berikut ini:

mysql> SHOW STATUS LIKE 'server_audit%';
+----------------------------+------------------+
| Variable_name              | Value            |
+----------------------------+------------------+
| server_audit_active        | ON               |
| server_audit_current_log   | server_audit.log |
| server_audit_last_error    |                  |
| server_audit_writes_failed | 0                |
+----------------------------+------------------+
4 rows in set (0.00 sec)

Terlihat bahwa MariaDB Audit Plugin akan menulis hasil audit ke file dengan nama server_audit.log. Pada instalasi default MySQL Server di Windows, file ini terletak di folder seperti C:\ProgramData\MySQL\MySQL Server 5.6\data.

MariaDB Audit Plugin menyediakan 3 jenis informasi yang di-audit, yaitu CONNECT, QUERY dan TABLE. Pada MySQL Server, hanya CONNECT dan QUERY yang dapat dipakai. Sebagai contoh, bila saya hanya ingin merekam aktifitas koneksi dan mengabaikan aktifitas query, saya dapat memberikan perintah SQL seperti berikut ini:

SET GLOBAL server_audit_events = 'CONNECT';

Bila ada pengguna yang melakukan koneksi atau memutuskan koneksi, saya akan memperoleh baris baru di file server_audit.log yang isinya seperti berikut ini:

20141207 20:00:46,mydatabase.com,inventory,192.168.56.30,12,0,CONNECT,,,0
20141207 20:01:06,mydatabase.com,inventory,192.168.56.30,12,0,DISCONNECT,,,0

Informasi di atas terdiri atas waktu, nama server database yang dihubungi, nama user dan lokasi host asal yang melakukan koneksi, id koneksi, dan informasi seperti CONNECT & DISCONNECT.

Baris yang sangat penting untuk dipantau adalah baris dimana pengguna gagal melakukan koneksi, yang terlihat seperti berikut ini:

20141207 18:06:06,mydatabase.com,inventory,192.168.56.30,13,0,FAILED_CONNECT,,,1045
20141207 18:06:06,mydatabase.com,inventory,192.168.56.30,13,0,DISCONNECT,,,0

Informasi FAILED_CONNECT menunjukkan bahwa ada pengguna yang gagal melakukan koneksi. Nilai 1045 adalah kode kesalahan yang menyebabkan kegagalan koneksi. Berdasarkan informasi di http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html, nilai ini adalah ER_ACCESS_DENIED_ERROR yang memiliki pesan seperti Access denied for user '%s'@'%s' (using password: %s). Hal ini menunjukkan bahwa pengguna gagal melakukan koneksi karena memberikan password yang salah. Bila baris seperti ini sering terlihat, maka ada kemungkinan pengguna (atau hacker) sedang berusaha mengakses sesuatu yang bukan hak-nya.

Membuat Implementasi Auditable Untuk Spring Data Auditing

Pada tulisan Memakai Fitur Auditing di Spring Data JPA, saya memakai AbstractAuditable agar cepat.  Tapi kadang-kadang apa yang disediakan oleh AbstractAuditable berbeda dengan keinginan saya.  Sebagai contoh, AbstractAuditable diturunkan dari AbstractPersistable yang mendefinisikan id dengan @GeneratedValue(strategy=GenerationType.AUTO).  Bagaimana jika saya ingin strategi yang berbeda?  Atau, bagaimana bila saya tidak ingin menyimpan createdDate?

Saya wajib mengimplementasikan interface Auditable pada setiap domain class yang ada.  Tapi daripada membuat implementasi di seluruh domain class yang ada (terjadi duplikasi), saya sebaiknya meletakkan implementasi disebuah abstract class, yang saya beri nama AuditableDomain.  Isi dari class ini saya copy-paste dari isi class AbstractAuditable beserta AbstractPersistable, yaitu:

import java.io.Serializable;
import java.util.Date;

import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.MappedSuperclass;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.joda.time.DateTime;
import org.springframework.data.domain.Auditable;

@MappedSuperclass
public abstract class AuditableDomain<U,PK extends Serializable> implements Auditable<U, PK>{

    private static final long serialVersionUID = -5057318377914867780L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private PK id;

    @ManyToOne
    private U createdBy;

    @ManyToOne
    private U lastModifiedBy;

    @Temporal(TemporalType.TIMESTAMP)
    private Date lastModifiedDate;

    public U getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(final U createdBy) {
        this.createdBy = createdBy;
    }

    public DateTime getCreatedDate() {
        return null
    }

    public void setCreatedDate(final DateTime createdDate) {
                // tidak melakukan apa-apa
    }

    public U getLastModifiedBy() {
        return lastModifiedBy;
    }

    public void setLastModifiedBy(final U lastModifiedBy) {
        this.lastModifiedBy = lastModifiedBy;
    }

    public DateTime getLastModifiedDate() {
        return null == lastModifiedDate ? null : new DateTime(lastModifiedDate);
    }

    public void setLastModifiedDate(final DateTime lastModifiedDate) {
        this.lastModifiedDate = null == lastModifiedDate ? null : lastModifiedDate.toDate();
    }

    public PK getId() {
        return id;
    }

    protected void setId(final PK id) {
        this.id = id;
    }

    public boolean isNew() {
        return null == getId();
    }
}

Perbedaannya dengan class AbstractAuditable adalah pada class AuditableDomain, saya bisa mengubah annotation yang  dipakai sesuka hati saya, misalnya saya memakai strategi GenerationType.IDENTITY dalam menghasilkan id untuk domain class yang ada.   Selain itu, saya juga menghilangkan atribut createdDate karena domain class saya sudah punya atribut tanggal.

Saya memastikan bahwa class AuditableDomain memiliki annotation @MappedSuperclass sehingga Hibernate JPA akan memproses annotation di class ini bila seandainya class lain diturunkan dari class ini.   Sekarang, saya tinggal menurunkan semua domain class saya dari class AuditableDomain seperti yang terlihat dari kode program berikut ini:

@Entity
@Table(name="tblPemesanan")
public class Pemesanan extends AuditableDomain<User, Long> implements Serializable {
  ...
}

Memakai Fitur Auditing di Spring Data JPA

Auditing untuk domain class adalah salah satu fitur yang sering kali dibutuhkan.  Stakeholder perlu mengetahui siapa yang terakhir kali mengubah data sebuah entitas stok.  Mereka juga mungkin ingin tahu kapan sebuah stok dibuat dan dimodifikasi.   Oleh sebab itu saya perlu menambahkan atribut seperti createdBy, createdDate, lastModifiedBy, dan lastModifiedDate pada setiap domain class yang perlu di-audit.  Beruntungnya, jika saya memakai Spring Data JPA, saya tidak perlu menambahkan atribut beserta logic kode program tersebut secara manual.  Spring Data JPA sudah memiliki fitur auditing.  Syaratnya adalah saya harus menambahkan dependency ke artifact spring-aspects.

Dukungan auditing di Spring Data JPA bisa dibilang masih sangat sederhana bila dibandingkan dengan Hibernate Enver yang memiliki tabel riwayat audit.  Tapi kadang-kadang kebutuhan auditing tidak perlu selengkap itu.

Untuk memakai fasilitas auditing di Spring Data JPA, saya perlu mengimplementasikan interface Auditable<U, ID extends Serializable>.  Cara yang lebih cepat untuk memperoleh semua atribut auditing (createdBy, createdDate, lastModifiedBy, dan lastModifiedDate) adalah dengan menurunkan domain class dari class AbstractAuditable<U, PK extends Serializable>.  Sebagai contoh, bila saya ingin menambahkan fitur auditing di domain class Pemesanan,  maka kode program untuk domain class tersebut akan terlihat seperti:

@Entity
@Table(name="pemesanan")
public class Pemesanan extends AbstractAuditable<User, Long> implements Serializable {

  @ElementCollection(fetch=FetchType.EAGER)
  @CollectionTable(joinColumns=@JoinColumn(name="pemesanan_id"))
  private List<ItemPemesanan> listItemPemesanan;

  @Column(name="status")
  @private Status status;

  // ...
  // getter dan setter diabaikan
  // ...
}

Pada class Pemesanan, saya hanya perlu mendefinisikan atribut yang berhubungan dengan pemesanan.  Dengan meng-extends class AbstractAuditable<User, Long>, saya secara otomatis telah memperoleh atribut createdBy, createdDate, lastModifiedBy, dan lastModifiedDate beserta getter dan setter-nya.

Type parameter U di AbstractAuditable untuk class Pemesanan di atas diwakili oleh User, dimana User adalah sebuah domain class buatan saya yang mewakili pengguna yang dapat login ke aplikasi.  Type parameter ini menentukan nilai yang akan dikembalikan oleh getCreatedBy() dan getLastModifiedBy().

Type parameter PK di AbstractAuditable untuk class Pemesanan di atas diwakili oleh Long.   Tipe ini menunjukkan tipe untuk id (primary key) dari domain class yang bersangkutan.   Dengan menurunkan domain class dari AbstractAuditable, saya TIDAK perlu lagi mendefinisikan id secara manual karena domain class secara otomatis akan memilih method getId(), setId(), dan isNew().

Langkah berikutnya saya perlu menambahkan entity listener pada setiap domain class.  Cara no-brainer (ga pakai mikir) adalah dengan menambahkan annotation @EntityListener di setiap domain class yang ada.  Tapi ada cara otomatis yang lebih disarankan.  Saya perlu menambahkan file orm.xml yang akan memberikan definisi entity listener secara global.   Saya akan membuat file orm.xml bila belum ada, dengan men-klik kanan pada  folder src/main/resources/META-INF, memilih menu New, Other, lalu pada dialog yang muncul, saya memilih JPA, JPA ORM Mapping File, seperti yang terlihat pada gambar berikut ini:

Membuat file orm.xml

Membuat file orm.xml

Saya men-klik Next, lalu memastikan bahwa nama file adalah orm.xml.  Bila Eclipse tidak memberikan pilihan Next atau Finish, pastikan bahwa project facet JPA sudah diaktifkan (cara memeriksanya adalah dengan men-klik kanan nama proyek, memilih properties, lalu memilih Project Facets.  Saya boleh menghilangkan tanda centang di JPA setelah file ORM dibuat).  Saya kemudian men-klik tombol Next, mencentang pilih Add to persitence unit.  Setelah itu  saya men-klik Finish.  Kemudian saya melakukan perubahan sehingga isi file orm.xml terlihat seperti berikut ini:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.0" xmlns="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd">
    <persistence-unit-metadata>
        <persistence-unit-defaults>
            <entity-listeners>
                <entity-listener class="org.springframework.data.jpa.domain.support.AuditingEntityListener" />
            </entity-listeners>
        </persistence-unit-defaults>
    </persistence-unit-metadata>
</entity-mappings>

Langkah berikutnya adalah membuat sebuah Spring bean yang meng-implementasi-kan inteface AuditorAware.  Bean ini diperlukan supaya Spring Data JPA tahu user mana yang sedang aktif atau sedang login.  Agar mudah, saya memakai ulang UserServiceImpl yang menyediakan services yang berkaitan dengan user.  Karena saya memakai Spring Security untuk menangani proses login, maka kode program di UserServiceImpl saya akan terlihat seperti:

@Service("userService")
@Repository
public class UserServiceImpl implements UserService, AuditorAware<User> {
   // ...
   // kode program yang sudah ada di UserServiceImpl tidak ditampilkan disini.
   // ...

   @Override
   public User getCurrentAuditor() {
      Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
      if (authentication==null) return null;
      return getUser(authentication.getName());
   }
}

Langkah terakhir adalah mendaftarkan userService di file konfigurasi Spring.  Saya menambahkan baris berikut pada file konfigurasi Spring:

<jpa:auditing auditor-aware-ref="userService" />

Pada bagian kode program yang melakukan penyimpan, saya melakukan sedikit perubahan sehingga terlihat seperti berikut ini:

public Pemesanan savePemesanan(Pemesanan pemesanan) {
  if (!pemesanan.isNew()) {
    Pemesanan oldPemesanan = pemesananRepository.findOne(pemesanan.getId());
    pemesanan.setCreatedBy(oldPemesanan.getCreatedBy());
    pemesanan.setCreatedDate(oldPemesanan.getCreatedDate());
  }
  return pemesananRepository.saveAndFlush(pemesanan);
}

Karena saya selalu membuat domain object baru dari web front end, baik proses insert maupun update, maka kode program di atas diperlukan agar nilai atribut createdBy dan createdDate tidak hilang bila proses update berlangsung.   Jika seandainya saya tetap memakai domain object yang sama, maka saya dapat  langsung menyimpan dengan saveAndFlush() di repository.

Setelah ini, setiap kali  domain object Pemesanan dibuat, maka atribut createdBy dan createdDate. Begitu juga lastModifiedBy dan lastModifiedDate akan di-isi secara otomatis pada saat domain object Pemesanan diubah.  Nilai createdBy dan lastModifiedBy akan diambil dari user yang sedang login.  Nilai createdDate dan lastModifiedDate akan diambil dari waktu saat proses create atau insert berlangsung.

Auditing: Mencari Kesalahan Di Database Oracle

Untuk mengaktifkan fungsi auditing, aku perlu mengubah nilai parameter AUDIT_TRAIL. Disini aku punya dua pilihan. Aku bisa mengganti nilai AUDIT_TRAIL dengan nilai DB untuk menyimpan hasil audit di database, atau memakai nilai OS untuk menyimpan hasil audit ke sistem operasi (karena aku memakai Windows XP, maka hasil audit akan ada di Event Viewer). Aku akan mencoba memakai nilai “OS”.

Lalu, aku akan mencoba meng-audit semua aktifitas yang dilakukan oleh user ‘jocki’ dengan perintah berikut:


AUDIT ALL BY JOCKI;

Setelah meng-koneksikan diri sebagai user jocki dan mencoba melakukan beberapa operasi database, aku menemukan entry baru di Event Viewer di bagian Application dengan Event ID 34. Salah satu contohnya mengandung message sebagai berikut:


SESSIONID: "38645" ENTRYID: "9" STATEMENT: "9" USERID: "JOCKI" USERHOST: "WORKGROUP\PC-JOCKI" TERMINAL: "PC-JOCKI" ACTION: "1" RETURNCODE: "0" OBJ$CREATOR: "JOCKI" OBJ$NAME: "TMP" OS$USERID: "PC-JOCKI\Jocki Hendry" PRIV$USED: 40.

Nilai ACTION adalah “1”. Untuk mengetahui lebih jelas, “1” itu apa, aku melakukan query berikut:

SELECT * FROM AUDIT_ACTIONS
WHERE ACTION = 1;

dan aku mendapatkan jawaban dengan nilai field name berupa “CREATE TABLE”. Ini menunjukkan bahwa pada jam yang tertera di log EventViewer, user jocki membuat sebuah tabel bernama TMP dimana ia login di komputer dengan nama PC-JOCKI.