MariaDB Audit Plugin - Log Settings

You are viewing an old version of this article. View the current version here.

The events that are logged by the MariaDB Audit Plugin can be grouped into different types: connect, query, and table events. To do this, set the variable, server_audit_events to connect, query, or table. To have the Audit Plugin log more than one type of events, put them in a comma-separated list like so:

SET GLOBAL server_audit_events = 'connect,query,table';

You can put the equivalent of this in the configuration file like so:

[mysqld]
...
server_audit_events=connect,query

By default, logging is set to off. To enable it, set the server_audit_logging variable to ON. Note that if the query cache is enabled, and a query is returned from the query cache, no TABLE records appear in the log, as the server didn't open or access any tables.

There are actually a few types of events that may be logged. A full list of related system variables are detailed on the Server_Audit System Variables page, and status variables on the Server_Audit Status Variables page of this documentation. Some of the major ones are highlighted below:

TypeDescriptionIntroduced
CONNECTLogs connects, disconnects and failed connects (including the error code).
QUERYQueries issued and their results (in plain text), including failed queries due to syntax or permission errors.
TABLEWhich tables were affected by query execution.
QUERY_DDLWorks as the 'QUERY' value, but filters only DDL-type queries (CREATE, ALTER, DROP, RENAME and TRUNCATE statements. Exceptions are CREATE/DROP [PROCEDURE / FUNCTION / USER] and RENAME USER - they are not DDL).MariaDB 5.5.42. MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DMLWorks as the 'QUERY' value, but filters only DML-type queries (DO, CALL, LOAD DATA/XML, DELETE, INSERT, UPDATE, HANDLER and REPLACE statements).MariaDB 5.5.42, MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DCLWorks as the 'QUERY' value, but filters only DCL-type queries (CREATE USER, DROP USER, RENAME USER, GRANT, REVOKE and SET PASSWORD statements.)MariaDB 5.5.43, MariaDB 10.0.18, MariaDB 10.1.5

There are other query types than DDL and DML, so 'QUERY_DDL, QUERY_DML' is not equal to 'QUERY'. Starting in version 1.3.0, there is the value, 'QUERY_DCL' so that DCL-type queries (GRANT, REVOKE) can be filtered. In the same version, server_audit_query_log_limit variable was added to be able to set the length of a log record. Previously, the length of the log record was truncated for long query strings.

Logging Connect Events

If the Audit Plugin has been configured to log connect events, it will log connects, disconnects, and failed connects. For a failed connection, the log includes also the error code.

It's possible to define a list of users, for which events can be excluded or included for tracing their database activities. This list will be ignored, though, for the loggings of connect events. This is because auditing standards distinguish between technical and physical users. Connects need to be logged for all types of users; access to objects need to be logged only for physical users.

Logging Query Events

If query event logging is enabled, queries that are executed will be logged for the defined users. The queries will be logged exactly as they are executed, in plain text. This is a security vulnerability. Someone who has access to the log files can therefore read the queries and data. So make sure that only trusted users have access to the log files and that the files are in a protected location. An alternative is not to use query event logging, but to use only table event logging.

Queries are also logged if they cannot be executed, if they're unsuccessful. For example, a query will be logged because of a syntax error or because the user doesn't have the privileges necessary to access an object. These queries can be parsed by the error code that's provided in the log.

Failed queries might be more interesting: They can reveal problems with applications (e.g., an SQL statement in the code doesn't match the current schema). They can also reveal if a malicious user is guessing at the names of tables and columns to get access to data.

Logging Table Events

MariaDB has the ability to record table events in the logs -- this is not a feature of MySQL. This feature is the only way to log which tables have been accessed through a view, a stored procedure, a stored function, or a trigger. Without this feature, a log entry for a query shows only the view, stored procedure or function used, not the underlying tables. Of course, you could create a custom application to parse each query executed to find the SQL statements used and the tables accessed, but that would be a drain on system resources. Table event logging is much simpler: it adds a line to the log for each table accessed, without any parsing. It includes notes as to whether it was a read or a write.

If you want to monitor user access to specific databases or tables (e.g., mysql.user), you can search the log for them. Then if you want to see a query which accessed a certain table, the audit log entry will include the query identificaiton number. You can use it to search the same log for the query entry. This can be useful when searching a log containing tens of thousands of entries.

Because of the table option, you may disable query logging and still know who accessed which tables. You might want to disable query event logging to prevent sensitive data from being logged. Since table event logging will log who accessed which table, you can still watch for malicious activities with the log. This is often enough to fulfill auditing requirements.

Logging User Activities

The Audit Plugin will log the database activities of all users, or only the users that you specify. A database activity is defined as a query event or a table event. Connect events are logged for all users.

You may specify users to include in the log with the server_audit_incl_users variable or exclude users with the server_audit_excl_users variable. This can be useful if you would like to log entries, but are not interested in entries from trusted applications and would like to exclude them from the logs.

You would typically use either the server_audit_incl_users variable or the server_audit_excl_users variable. You may, though, use both variables. If a username is inadvertently listed in both variables, database activities for that user will be logged because server_audit_incl_users takes priority.

Although MariaDB considers a user as the combination of the username and hostname, the Audit Plugin logs only based on the username. MariaDB uses both the username and hostname so as to grant privileges relevant to the location of the user. Privileges are not relevant though for tracing the access to database objects. The host name is still recorded in the log, but logging is not determined based on that information.

The following example shows how to add a new username to the server_audit_incl_users variable without removing previous usernames:

SET GLOBAL server_audit_incl_users = CONCAT(@@global.server_audit_incl_users, ',Maria');

Remember to add also any new users to be included in the logs to the same variable in MariaDB configuration file. Otherwise, when the server restarts it will discard the setting.

Excluding or Including Users

By default events from all users are logged, but certain users can be excluded from logging by using the server_audit_excl_users variable. For example, to exclude users valerianus and rocky from having their events logged:

server_audit_excl_users=valerianus,rocky

This option is primarily used to exclude the activities of trusted applications.

Alternatively, server_audit_incl_users can be used to specifically include users. Both variables can be used, but if a user appears on both lists, server_audit_incl_users has a higher priority, and their activities will be logged.

Note that CONNECT events are always logged for all users, regardless of these two settings. Logging is also based on username only, not the username and hostname combination that MariaDB uses to determine privileges.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.