Mysql：来源于Mariadb的【审计】插件：server_audit

一：直接从mariadb的GA版本中提取server_audit{.so|.dll}，根据测试，不支持 Mysql 8+

　　主要的、最近的版本都已经是 1.4.4+：完整的功能了。

　　　　CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, QUERY_DCL, QUERY_DML_NO_SELECT (MariaDB Audit Plugin >= 1.4.4)

二：根据测试，Mysql可以通过--plugin-load选项加载；通过install plugin方式加载在重启mysqld服务器后会出现问题！

三：基本配置

plugin-load=server_audit=server_audit.dll
server_audit=force_plus_permanent
server_audit_logging=on
server_audit_output_type=file
server_audit_file_path=d:/server_audit.log
server_audit_file_rotate_size=1024024024
server_audit_query_log_limit=2048

四：参考资料

A full list of related system variables is 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:

Type	Description	Introduced
CONNECT	Connects, disconnects and failed connects—including the error code
QUERY	Queries executed and their results in plain text, including failed queries due to syntax or permission errors
TABLE	Tables affected by query execution
QUERY_DDL	Same as `QUERY`, but filters only DDL-type queries (`CREATE`, `ALTER`, `DROP`, `RENAME` and `TRUNCATE` statements—except `CREATE/DROP [PROCEDURE / FUNCTION / USER]` and `RENAME USER` (they're not DDL)	MariaDB 5.5.42. MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DML	Same as `QUERY`, but filters only DML-type queries (`DO`, `CALL`, `LOAD DATA/XML`, `DELETE`, `INSERT`, `SELECT`, `UPDATE`, `HANDLER` and `REPLACE` statements)	MariaDB 5.5.42, MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DML_NO_SELECT	Same as `QUERY_DML`, but doesn't log SELECT queries. (since version 1.4.4) (`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_DCL	Same as `QUERY`, 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

Since there are other types of queries besides DDL and DML, using the QUERY_DDL and QUERY_DML options together is not equivalent to using QUERY. Starting in version 1.3.0 of the Audit Plugin, there is the QUERY_DCL option for logging DCL types of queries (e.g., GRANT and REVOKE statements). In the same version, the server_audit_query_log_limit variable was added to be able to set the length of a log record. Previously, a log entry would be truncated due to long query strings.

CONNECT, QUERY, TABLE (MariaDB Audit Plugin < 1.2.0)
CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML (MariaDB Audit Plugin >= 1.2.0)
CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, QUERY_DCL (MariaDB Audit Plugin >=1.3.0)
CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, QUERY_DCL, QUERY_DML_NO_SELECT (MariaDB Audit Plugin >= 1.4.4)
See MariaDB Audit Plugin - Versions to determine which MariaDB releases contain each MariaDB Audit Plugin versions.

MariaDB Audit Plugin - Versions

Below is a list of the releases of the MariaDB Audit Plugin, the most recent version first, and in which versions of MariaDB each plugin version was included.

Version	Introduced
1.4.7	MariaDB 10.1.41, MariaDB 10.2.26, MariaDB 10.3.17, MariaDB 10.4.7
1.4.5	MariaDB 10.2.24, MariaDB 10.3.15, MariaDB 10.4.5
1.4.4	MariaDB 5.5.61, MariaDB 10.0.36, MariaDB 10.1.34, MariaDB 10.2.15, MariaDB 10.3.7, MariaDB 10.4.0
1.4.0	MariaDB 5.5.48, MariaDB 10.0.24, MariaDB 10.1.11
1.3.0	MariaDB 5.5.43, MariaDB 10.0.18, MariaDB 10.1.5
1.2.0	MariaDB 5.5.42, MariaDB 10.0.17, MariaDB 10.1.4
1.1.7	MariaDB 5.5.38, MariaDB 10.0.11, MariaDB 10.1.0
1.1.6	MariaDB 5.5.37, MariaDB 10.0.10
1.1.5	MariaDB 10.0.09
1.1.4	MariaDB 5.5.36
1.1.3	MariaDB 5.5.34, MariaDB 10.0.7

MariaDB Audit Plugin - Log Settings

Events that are logged by the MariaDB Audit Plugin are grouped generally into different types: connect, query, and table events. To log based on these types of events, set the variable, server_audit_events to CONNECT, QUERY, or TABLE. To have the Audit Plugin log more than one type of event, 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 will appear in the log since the server didn't open or access any tables and instead relied on the cached results. So you may want to disable query caching.

There are actually a few types of events that may be logged, not just the three common ones mentioned above. A full list of related system variables is 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:

Type	Description	Introduced
CONNECT	Connects, disconnects and failed connects—including the error code
QUERY	Queries executed and their results in plain text, including failed queries due to syntax or permission errors
TABLE	Tables affected by query execution
QUERY_DDL	Same as `QUERY`, but filters only DDL-type queries (`CREATE`, `ALTER`, `DROP`, `RENAME` and `TRUNCATE` statements—except `CREATE/DROP [PROCEDURE / FUNCTION / USER]` and `RENAME USER` (they're not DDL)	MariaDB 5.5.42. MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DML	Same as `QUERY`, but filters only DML-type queries (`DO`, `CALL`, `LOAD DATA/XML`, `DELETE`, `INSERT`, `SELECT`, `UPDATE`, `HANDLER` and `REPLACE` statements)	MariaDB 5.5.42, MariaDB 10.0.17, MariaDB 10.1.4
QUERY_DML_NO_SELECT	Same as `QUERY_DML`, but doesn't log SELECT queries. (since version 1.4.4) (`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_DCL	Same as `QUERY`, 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

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 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 defined users. The queries will be logged exactly as they are executed, in plain text. This is a security vulnerability: anyone who has access to the log files will be able to read the queries. 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.

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

Below is an example in which a user attempts to execute an UPDATE statement on a table for which he does not have permission:

UPDATE employees 
SET salary = salary * 1.2 
WHERE emp_id = 18236;

ERROR 1142 (42000): 
UPDATE command denied to user 'bob'@'localhost' for table 'employees'

Looking in the Audit Plugin log (server_audit.log) for this entry, you can see the following entry:

20170817 11:07:18,ip-172-30-0-38,bob,localhost,15,46,QUERY,company,
'UPDATE employees SET salary = salary * 1.2 WHERE emp_id = 18236',1142

This log entry would be on one line, but it's reformatted here for better rendering. Looking at this log entry, you can see the date and time of the query, followed by the server host, the user and host for the account. Next is the connection and query identification numbers (i.e., 15 and 46). After the log event type (i.e., QUERY), the database name (i.e., company), the query, and the error number is recorded.

Notice that the last value in the log entry is 1142. That's the error number for the query. To find failed queries, you would look for two elements: the notation indicating that it's a QUERY entry, and the last value for the entry. If the query is successful, the value will be 0.

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.

Below is an example with both TABLE and QUERY events logging. For this scenario, suppose there is a VIEW in which columns are selected from a few tables in a company database. The underlying tables are related to sensitive employee information, in particular salaries. Although we may have taken precautions to ensure that only certain user accounts have access to those tables, we will monitor the Audit Plugin logs for anyone who queries them—directly or indirectly through a view.

20170817 16:04:33,ip-172-30-0-38,root,localhost,29,913,READ,company,employees,
20170817 16:04:33,ip-172-30-0-38,root,localhost,29,913,READ,company,employees_salaries,
20170817 16:04:33,ip-172-30-0-38,root,localhost,29,913,READ,company,ref_job_titles,
20170817 16:04:33,ip-172-30-0-38,root,localhost,29,913,READ,company,org_departments,
20170817 16:04:33,ip-172-30-0-38,root,localhost,29,913,QUERY,company,
'SELECT * FROM employee_pay WHERE title LIKE \'%Executive%\'  OR title LIKE \'%Manager%\'',0

Although the user executed only one SELECT statement, there are multiple entries to the log: one for each table accessed and one entry for the query on the view, (i.e., employee_pay). We know primarily this is all for one query because they all have the same connection and query identification numbers (i.e., 29 and 913).

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.

MariaDB Audit Plugin Options and System Variables

System Variables

Options

server_audit

There are a several options and system variables related to the MariaDB Audit Plugin, once it has been installed. System variables can be displayed using the SHOW VARIABLES statement like so:

SHOW GLOBAL VARIABLES LIKE '%server_audit%';

+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           | CONNECT,QUERY,TABLE   |
| 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          | ON                    |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+

To change the value of one of these variables, you can use the SET statement, or set them at the command-line when starting MariaDB. It's recommended that you set them in the MariaDB configuration for the server like so:

[mariadb]
...
server_audit_excl_users='bob,ted'
...

System Variables

Below is a list of all system variables related to the Audit Plugin. See Server System Variables for a complete list of system variables and instructions on setting them. See also the full list of MariaDB options, system and status variables.

`server_audit_events`

Description: If set, then this restricts audit logging to certain event types. If not set, then every event type is logged to the audit log. For example: SET GLOBAL server_audit_events='connect, query'
Commandline: --server-audit-events=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string
Valid Values:
- CONNECT, QUERY, TABLE (MariaDB Audit Plugin < 1.2.0)
- CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML (MariaDB Audit Plugin >= 1.2.0)
- CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, QUERY_DCL (MariaDB Audit Plugin >=1.3.0)
- CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, QUERY_DCL, QUERY_DML_NO_SELECT (MariaDB Audit Plugin >= 1.4.4)
- See MariaDB Audit Plugin - Versions to determine which MariaDB releases contain each MariaDB Audit Plugin versions.

`server_audit_excl_users`

Description: If not empty, it contains the list of users whose activity will NOT be logged. For example: SET GLOBAL server_audit_excl_users='user_foo, user_bar'. CONNECT records aren't affected by this variable - they are always logged. The user is still logged if it's specified in server_audit_incl_users.
Commandline: --server-audit-excl-users=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string
Size limit: 1024 characters

`server_audit_file_path`

Description: When server_audit_output_type=file, sets the path and the filename to the log file. If the specified path exists as a directory, then the log will be created inside that directory with the name 'server_audit.log'. Otherwise the value is treated as a filename. The default value is 'server_audit.log', which means this file will be created in the database directory.
Commandline: --server-audit-file-path=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: server_audit.log

`server_audit_file_rotate_now`

Description: When server_audit_output_type=file, the user can force the log file rotation by setting this variable to ON or 1.
Commandline: --server-audit-rotate-now[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`server_audit_file_rotate_size`

Description: When server_audit_output_type=file, it limits the size of the log file. Reaching that limit turns on the rotation - the current log file is renamed as 'file_path.1'. The empty log file is created as 'file_path' to log into it. The default value is 1000000.
Commandline: --server-audit-rotate-size=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1000000

`server_audit_file_rotations`

Description: When server_audit_output_type=file', this specifies the number of rotations to save. If set to 0 then the log never rotates. The default value is 9.
Commandline: --server-audit-rotations=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 9
Range: 0 to 999

`server_audit_incl_users`

Description: If not empty, it contains a comma-delimited list of users whose activity will be logged. For example: SET GLOBAL server_audit_incl_users='user_foo, user_bar'. CONNECT records aren't affected by this variable - they are always logged. This setting has higher priority than server_audit_excl_users. So if the same user is specified both in incl_ and excl_ lists, they will still be logged.
Commandline: --server-audit-incl-users=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string
Size limit: 1024 characters

`server_audit_loc_info`

Description: Used by plugin internals. It has no useful meaning to users.
- In earlier versions, users see it as a read-only variable.
- In later versions, it is hidden from the user.
Commandline: N/A
Scope: Global
Dynamic: No
Data Type: string
Default Value: Empty string
Introduced: MariaDB 10.1.12, MariaDB 10.0.24, MariaDB 5.5.48
Hidden: MariaDB 10.1.18, MariaDB 10.0.28, MariaDB 5.5.53

`server_audit_logging`

Description: Enables/disables the logging. Expected values are ON/OFF. For example: SET GLOBAL server_audit_logging=on If the server_audit_output_type is FILE, this will actually create/open the logfile so the server_audit_file_path should be properly specified beforehand. Same about the SYSLOG-related parameters. The logging is turned off by default.
Commandline: --server-audit-logging[={0|1}]
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF

`server_audit_mode`

Description: This variable doesn't have any distinctive meaning for a user. Its value mostly reflects the server version with which the plugin was started and is intended to be used by developers for testing.
Commandline: --server-audit-mode[=#]

`server_audit_output_type`

Description: Specifies the desired output type. Can be SYSLOG or FILE. For example: SET GLOBAL server_audit_output_type=file file: log records will be saved into the rotating log file. The name of the file set by server_audit_file_path variable. syslog: log records will be sent to the local syslogd daemon with the standard <syslog.h> API. The default value is 'file'.
Commandline: --server-audit-output-type=value
Scope: Global
Dynamic: Yes
Data Type: enum
Default Value: file
Valid Values: SYSLOG or FILE

`server_audit_query_log_limit`

Description: Limit on the length of the query string in a record.
Commandline: --server-audit-query-log-limit=#
Scope: Global
Dynamic: Yes
Data Type: numeric
Default Value: 1024
Range: 0 to 2147483647
Introduced: MariaDB 5.5.43, MariaDB 10.0.18, MariaDB 10.1.5

`server_audit_syslog_facility`

Description: SYSLOG-mode variable. It defines the 'facility' of the records that will be sent to the syslog. Later the log can be filtered by this parameter.
Commandline: --server-audit-syslog-facility=value
Scope: Global
Dynamic: Yes
Data Type: enum
Default Value: LOG_USER
Valid Values: LOG_USER, LOG_MAIL, LOG_DAEMON, LOG_AUTH, LOG_SYSLOG, LOG_LPR, LOG_NEWS, LOG_UUCP, LOG_CRON, LOG_AUTHPRIV, LOG_FTP, and LOG_LOCAL0–LOG_LOCAL7.

`server_audit_syslog_ident`

Description: SYSLOG-mode variable. String value for the 'ident' part of each syslog record. Default value is 'mysql-server_auditing'. New value becomes effective only after restarting the logging.
Commandline: --server-audit-syslog-ident=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: mysql-server_auditing

`server_audit_syslog_info`

Description: SYSLOG-mode variable. The 'info' string to be added to the syslog records. Can be changed any time.
Commandline: --server-audit-syslog-info=value
Scope: Global
Dynamic: Yes
Data Type: string
Default Value: Empty string

`server_audit_syslog_priority`

Description: SYSLOG-mode variable. Defines the priority of the log records for the syslogd.
Commandline: --server-audit-syslog-priority=value
Scope: Global
Dynamic: Yes
Data Type: enum
Default Value: LOG_INFO
Valid Values:LOG_EMERG, LOG_ALERT, LOG_CRIT, LOG_ERR, LOG_WARNING, LOG_NOTICE, LOG_INFO, LOG_DEBUG

Options

`server_audit`

Description: Controls how the server should treat the plugin when the server starts up.
- Valid values are:
  - OFF - Disables the plugin without removing it from the mysql.plugins table.
  - ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.
  - FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
  - FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
- See MariaDB Audit Plugin - Installation: Prohibiting Uninstallation for more information on one use case.
- See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.
Commandline: --server-audit=val
Data Type: enumerated
Default Value: ON
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT

posted on 2020-03-14 18:51 jinzhenshui 阅读(1154) 评论(0) 编辑收藏举报