Monitoring MariaDB server with Wazuh

MariaDB is an open source database system that stores and manages structured data. It is often chosen for its reliability, speed, and compatibility with MySQL. Many websites, applications, and services use MariaDB to handle data securely and efficiently. Monitoring MariaDB provides real-time visibility into database activities, helping to detect suspicious behavior, prevent unauthorized access, and strengthen overall security posture.

This blog post shows how to monitor a MariaDB server using Wazuh. We use the MariaDB audit plugin to generate logs of database activity, such as login attempts and SQL queries, and configure Wazuh to collect and analyse these logs for security monitoring.

Infrastructure

We use the following infrastructure to demonstrate MariaDB server monitoring.

• A pre-built, ready-to-use Wazuh OVA 4.12.0, which includes the Wazuh central components (Wazuh server, Wazuh indexer, and Wazuh dashboard). Follow this guide to download and set up the Wazuh virtual machine.
• A Fedora server 42 endpoint with:
  • The Wazuh agent 4.12.0 installed and enrolled in the Wazuh server.
  • MariaDB server 10.11 installed following these instructions.

Configuration

In this section, we set up the MariaDB audit plugin on the Fedora server. Next, we create decoders and rules on the Wazuh server to detect relevant database activity.

Fedora server endpoint

Perform the following steps to configure the MariaDB audit plugin. This plugin helps to log and monitor database activity.

1. Append the following settings to the /etc/my.cnf configuration file:

[server]
server_audit_logging=ON

[mariadb]
plugin_load_add=server_audit

[mysqld]
server_audit_output_type=SYSLOG
server_audit_events=connect,query,table,query_ddl,query_dml
server_audit_syslog_info=mariadb
server_audit_syslog_ident=mariadb-server_auditing

Where:

• server_audit_logging: Enables the MariaDB audit plugin at server startup.
• plugin_load_add: Loads the audit plugin.
• server_audit_output_type: Defines the format in which audit logs are sent.
• server_audit_events: Defines the types of events to log.
• server_audit_syslog_info: Adds a custom info tag to the log entries.
• server_audit_syslog_ident: Defines the identifier used in syslog logs. This will be used as the program_name when creating the Wazuh decoder.

2. Restart the MariaDB server to apply the changes:

# systemctl restart mariadb.service

Wazuh server

Perform the following steps to create custom decoders and rules on the Wazuh server.

1. Create a custom decoder file mariadb_decoders.xml in the  /var/ossec/etc/decoders/ directory and insert the following:

<decoder name="mariadatabase-decoder">
    <program_name>mariadb-server_auditing</program_name>
</decoder>

<decoder name="mariadatabase-syslog">
    <parent>mariadatabase-decoder</parent>
    <regex type="pcre2">^(\S+)\s+(\S+),([^,]*),([^,]*),(\d+),(\d+),([^,]*),([^,]*),(.*),(\d*)$</regex>
    <order>mariadb.info,mariadb.serverhost,mariadb.username,mariadb.userhost,mariadb.connectionid,mariadb.queryid,mariadb.operation,mariadb.database,mariadb.object,mariadb.retcode</order>
</decoder>

The custom Wazuh decoder above was created based on the official MariaDB Audit Plugin log format documentation. This ensures that the decoder properly interprets the structure and fields of the audit logs generated by MariaDB.

2. Create a custom rule  file mariadb_rules.xml  in the /var/ossec/etc/rules/ directory and insert the following rules:

<group name="mariadatabase,">
  <rule id="100700" level="0">
    <decoded_as>mariadatabase-decoder</decoded_as>
    <description>"MariaDB audit -"</description>
  </rule>

<!-- successful account manipulation -->
  <rule id="100701" level="7">
    <if_sid>100700</if_sid>
    <field name="mariadb.object" type="pcre2">(?i)GRANT|REVOKE|CREATE USER|DROP USER|RENAME USER</field>
    <field name="mariadb.retcode" type="pcre2">^0$</field>
    <description>"MariaDB audit -" Successful account manipulation: $(mariadb.object)</description>
    <mitre>
      <id>T1078</id>
    </mitre>
  </rule>

<!-- successful DROP/TRUNCATE/DELETE execution -->
  <rule id="100702" level="10">
    <if_sid>100700</if_sid>
    <field name="mariadb.object" type="pcre2">(?i)DROP TABLE|DROP DATABASE|TRUNCATE|DELETE</field>
    <field name="mariadb.retcode" type="pcre2">^0$</field>
    <description>"MariaDB audit -" Successful deletion: $(mariadb.object)</description>
    <mitre>
      <id>T1485</id>
    </mitre>
  </rule>

<!-- successful modification-->
  <rule id="100703" level="7">
    <if_sid>100700</if_sid>
    <field name="mariadb.object" type="pcre2">(?i)ALTER|UPDATE</field>
    <field name="mariadb.retcode" type="pcre2">^0$</field>
    <description>"MariaDB audit -" Successful schema/data modification: $(mariadb.object)</description>
    <mitre>
      <id>T1565.001</id>
    </mitre>
  </rule>

<!-- Unauthorized access to MariaDB server -->
  <rule id="100704" level="5">
    <if_sid>100700</if_sid>
    <field name="mariadb.operation" type="pcre2">FAILED_CONNECT</field>
    <description>"MariaDB audit -" Authentication failed: $(mariadb.username)@$(mariadb.userhost)</description>
    <group>authentication_failures,</group>
    <mitre>
      <id>T1110.001</id>
    </mitre>
  </rule>

<!-- Possible brute force attack -->
  <rule id="100705" level="7" frequency="5" timeframe="30">
    <if_matched_sid>100704</if_matched_sid>    
    <field name="mariadb.operation" type="pcre2">FAILED_CONNECT</field>
    <same_field>mariadb.userhost</same_field>
    <description>"MariaDB audit -" Possible brute force attack from $(mariadb.username)@$(mariadb.userhost)</description>
    <group>authentication_failures,</group>
    <mitre>
      <id>T1110</id>
    </mitre>
  </rule>

<!-- Data definition operation-->
  <rule id="100706" level="5">
    <if_sid>100700</if_sid>
    <field name="mariadb.object" type="pcre2">(?i)RENAME</field>
    <field name="mariadb.retcode" type="pcre2">^0$</field>
    <description>"MariaDB audit -" Successful data definition operation: $(mariadb.object)</description>
    <mitre>
      <id>T1036</id>
    </mitre>
  </rule>

  <rule id="100707" level="5">
    <if_sid>100700</if_sid>
    <field name="mariadb.object" type="pcre2">(?i)CREATE</field>
    <field name="mariadb.retcode" type="pcre2">^0$</field>
    <description>"MariaDB audit -" Successful data definition operation: $(mariadb.object)</description>
    <mitre>
      <id>T1565.001</id>
    </mitre>
  </rule>

<!-- Data manipulation operation-->
  <rule id="100708" level="5">
    <if_sid>100700</if_sid>
    <field name="mariadb.object" type="pcre2">(?i)INSERT|MERGE</field>
    <field name="mariadb.retcode" type="pcre2">^0$</field>
    <description>"MariaDB audit -" Successful data manipulation operation: $(mariadb.object)</description>
    <mitre>
      <id>T1565.001</id>
    </mitre>
  </rule>

<!-- successful disconnection from MariaDB server -->
  <rule id="100709" level="3">
    <if_sid>100700</if_sid>
    <field name="mariadb.operation">DISCONNECT</field>
    <description>"MariaDB audit -" User session closed: $(mariadb.username)@$(mariadb.userhost)</description>
  </rule>

<!-- successful authentication to MariaDB server -->
  <rule id="100710" level="3">
    <if_sid>100700</if_sid>
    <field name="mariadb.operation" type="pcre2">^CONNECT</field>
    <field name="mariadb.retcode" type="pcre2">^0$</field>
    <description>"MariaDB audit -" Successful authentication: $(mariadb.username)@$(mariadb.userhost)</description>
  </rule>


<!-- Data query-->
  <rule id="100711" level="3">
    <if_sid>100700</if_sid>
    <field name="mariadb.object" type="pcre2">(?i)SELECT|SHOW|DESCRIBE|USE|EXPLAIN</field>
    <field name="mariadb.retcode" type="pcre2">^0$</field>
    <description>"MariaDB audit -" Successful data query: $(mariadb.object)</description>
    <mitre>
      <id>T1082</id>
    </mitre>
  </rule>

<!-- Plugin operation -->
<rule id="100712" level="7">
  <if_sid>100700</if_sid>
  <field name="mariadb.object" type="pcre2">(?i)INSTALL|UNINSTALL|PLUGIN</field>
  <field name="mariadb.retcode" type="pcre2">^0$</field>
  <description>"MariaDB audit -" Plugin operation detected: $(mariadb.object)</description>
</rule>

<!-- Execution Error -->
  <rule id="100713" level="3">
    <if_sid>100700</if_sid>
    <field name="mariadb.retcode" type="pcre2">(?!0\b)\d+</field>
    <description>"MariaDB audit -" An operation returned error $(mariadb.retcode): $(mariadb.object)</description>
  </rule>

<!-- All other successful operations -->
  <rule id="100714" level="3">
    <if_sid>100700</if_sid>
    <field name="mariadb.retcode" type="pcre2">^0$</field>
    <description>"MariaDB audit -" Successful operation: $(mariadb.object)</description>
  </rule>
</group>

Where:

• Rule ID 100700 is a base rule triggered for all MariaDB audit logs parsed by the custom decoder.
• Rule ID 100701 is triggered when a user account is created, removed, or modified using CREATE USER, DROP USER, RENAME USER, GRANT, or REVOKE commands.
• Rule ID 100702 is triggered when a DROP TABLE, DROP DATABASE, TRUNCATE, or DELETE command is successfully executed on a database, indicating data or table removal.
• Rule ID 100703 is triggered when a schema or data modification uses the ALTER or UPDATE statements.
• Rule ID 100704 is triggered when a login attempt to the MariaDB server fails.
• Rule ID 100705 is triggered when five failed login attempts occur within 30 seconds, indicating a possible brute-force attack.
• Rule ID 100706 is triggered when a RENAME command is used to alter database objects.
• Rule ID 100707 is triggered when a CREATE command is used to define database objects.
• Rule ID 100708 is triggered when data is added or merged using INSERT or MERGE commands.
• Rule ID 100709 is triggered when a user session is closed.
• Rule ID 100710 is triggered when a user successfully authenticates to the MariaDB server.
• Rule ID 100711 is triggered when users perform read-only queries such as SELECT, SHOW, DESCRIBE, USE, or EXPLAIN.
• Rule ID 100712 is triggered when a PLUGIN operation, such as INSTALL or UNINSTALL is successfully executed on the MariaDB server, indicating plugin-level changes.
• Rule ID 100713 is triggered when an SQL command returns an error (non-zero return code).
• Rule ID 100714 is triggered for any successful MariaDB operation not covered by more specific rules.

3. Restart the Wazuh manager to apply the changes:

# systemctl restart wazuh-manager

Testing

We perform the following actions on the Fedora server to trigger alerts:

• Authentication events
• Data definition operations
• Data manipulation operations
• Data destruction operations
• Data access and query events
• Account and privilege operations
• Failed operations

Authentication events

1. Run the following command to simulate a brute force attack. This triggers the alerts 100704, 100705, and 100709:

$ for i in {1..5}; do mariadb -u attacker -pwrongpasword; done

2. Log in to the MariaDB CLI using the default MariaDB root account. This triggers the alert 100710:

$ mariadb -u root -p

Data definition operations

1. Create the database demo_db  by running the command below. This triggers the alert 100707:

CREATE DATABASE demo_db;

2. Switch to the demo_db database. This triggers the alert 100711:

USE demo_db;

3. Create a users table. This triggers the alert 100707:

CREATE TABLE users (id INT, name VARCHAR(100));

4. Rename the table from users to clients. This triggers the alert 100706 :

RENAME TABLE users TO clients;

5. Alter the clients table. This triggers the alert 100703:

ALTER TABLE clients ADD email VARCHAR(255);

Data manipulation operations

1. Insert data in the clients table. This triggers the alert 100708:

INSERT INTO clients (id, name, email) VALUES (1, 'John Doe', 'john@example.com');
INSERT INTO clients (id, name, email) VALUES (2, 'Jane Doe', 'jane@example.com');

2. Update a record in the clients table. This triggers the alert 100703:

UPDATE clients SET email = 'john.doe@example.com' WHERE id = 1;

Data destruction operations

1. Delete an entry in the clients table. This triggers the alert 100702:

DELETE FROM clients WHERE id = 1;

2. Drop the clients table. This triggers the alert 100702:

DROP TABLE clients;

Data access and query events

1. Show existing databases. This triggers the alert 100711:

SHOW DATABASES;

Account and privilege operations

1. Create a user named tester that can connect only from localhost, with a defined password. This triggers the alert 10071:

CREATE USER 'tester'@'localhost' IDENTIFIED BY 'testpass';

2. Grant full privileges to the user tester on the demo_db database. This triggers the alert 10071:

GRANT ALL PRIVILEGES ON demo_db.* TO 'tester'@'localhost';

3. Revoke all privileges from the user tester on the demo_db database. This triggers the alert 10071:

REVOKE ALL PRIVILEGES ON demo_db.* FROM 'tester'@'localhost';

4. Drop the user tester. This triggers the alert 10071:

DROP USER 'tester'@'localhost';

Failed operations

1. Run the command below to trigger an unexpected event. This triggers the alert  100713:

DROP TABLE non_existent_table;

Visualizing alerts on the Wazuh dashboard

Perform the following steps to visualize the alerts generated on the Wazuh dashboard:

1. Navigate to Threat intelligence > Threat Hunting > Events on the Wazuh dashboard. 
2. In the search bar, type rule.groups:mariadatabase, and click Update.

3. Click Inspect document details on an alert to have a detailed view.

Conclusion

This blog post demonstrates how Wazuh can monitor activities performed on a MariaDB server. By integrating MariaDB auditing with Wazuh, organizations gain visibility into database operations, enabling them to detect suspicious behavior and enhance their overall security posture.

Wazuh is a free and open source security solution designed for threat detection, incident response, and compliance management. Wazuh continues to provide new integrations and capabilities, and has an active and supportive community. 

To learn more, explore our documentation and blog posts.

References

• Wazuh ruleset XML syntax
• MariaDB audit plugin