Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb
HTML Download (TGZ) - 192.4Kb
HTML Download (Zip) - 203.7Kb


MySQL Utilities 1.5 Manual  /  ...  /  How do you record only login events in the audit log?

3.5.1 How do you record only login events in the audit log?

The audit log plugin records MySQL servers activity. By default, it is set to write all audit events to the log file which can represent a considerable amount of information. Fortunately, it is possible to control the type of information that is written to the audit log file by changing the audit log plugin's policy. The policy should be set to log only the events of interest, avoiding wasting resources to log unnecessary events.

In particular, if the audit log plugin is only used to monitor access to the database server (for security purposes) then only the login events need to be recorded. The mysqlauditadmin utility allows us to perform such a change in a simple way (as well as changes to other settings).

Objectives

The goal is to set the audit log plugin to write the login events to the log file and no other events. It is assumed that the audit log plugin is enabled and running with the default settings (logging all audit events) on the localhost and default port (3306).

Example Execution

shell> mysqlauditadmin --server=root@localhost:3306 policy --value=LOGINS \
          --show-options
#
# Showing options before command.
#
# Audit Log Variables and Options
#
+---------------------------+---------------+
| Variable_name             | Value         |
+---------------------------+---------------+
| audit_log_buffer_size     | 1048576       |
| audit_log_file            | audit.log     |
| audit_log_flush           | OFF           |
| audit_log_policy          | ALL           |
| audit_log_rotate_on_size  | 0             |
| audit_log_strategy        | ASYNCHRONOUS  |
+---------------------------+---------------+

#
# Executing POLICY command.
#

#
# Showing options after command.
#
# Audit Log Variables and Options
#
+---------------------------+---------------+
| Variable_name             | Value         |
+---------------------------+---------------+
| audit_log_buffer_size     | 1048576       |
| audit_log_file            | audit.log     |
| audit_log_flush           | OFF           |
| audit_log_policy          | LOGINS        |
| audit_log_rotate_on_size  | 0             |
| audit_log_strategy        | ASYNCHRONOUS  |
+---------------------------+---------------+

Discussion

In order to change the type of events recorded to the audit log file, the policy settings must be changed. This is done with the mysqlauditadmin utility using the command 'policy' and specifying the desired policy value with the --value option. As expected the specification of the target server is also required using the --server option.

In the above example, the policy value was set to LOGINS to write only login events to the log file. Nevertheless, other values are also permitted to control the information written to the log file: ALL (write all events), QUERIES (write only query event), NONE (disable logging), DEFAULT (use the default policy).

Permissions Required

User must have the SELECT privilege for the mysql database. To view the log file, the user must have read access to the audit log file on the server.

Tips and Tricks

The policy value was specified using uppercase in this example, however uppercase and lowercase can be mixed to specify the policy value (such as "LoGiNs"). The values for this command are still read correctly independently of the used cases (case insensitive), but if an unsupported value is specified, an error is issued.

In the above example the --show-options option was used, but it is not required. This option simply displays the audit log settings (variables). However, when this option is combined with a command that changes one of the audit log variables, it displays the audit log settings before and after the execution of the command which can be very handy to confirm that the desired change was performed as expected.


User Comments
Sign Up Login You must be logged in to post a comment.