Shulou(Shulou.com)06/01 Report--

It is believed that many inexperienced people have no idea about how to configure log audit in MySql. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Mysql audit configuration 1. Selection of audit methods

Using the database audit plug-in to turn on database audit will sacrifice part of the database performance. It is recommended to choose the audit plug-in or bypass audit equipment according to the actual business situation.

Common mysql free audit plug-ins: mariadb audit plugin, macfee mysql-audit

We take mariadb audit plugin as an example to complete the configuration installation and analysis.

2. Installation and configuration of audit plug-in

Environment: windows server 2008 R2 PhpStudy8.1 Magi MySQL 5.5.29 Magi sqliriplabs

Download the mariadb-5.5.68 version on the official website, use the command line to execute the following command, and extract it to the target directory

Msiexec / a "d:\ mariadb-5.5.68-winx64.msi" / qb TARGETDIR= "D:\ abc"

Extract the required plug-in server_audit.dll in the MariaDB 5.5\ lib\ plugin directory, open the mysql command line, and query the plugin directory

Copy server_audit.dll to the plug-in directory, mysql command line to install the plug-in, query the plug-in status

Query the status configuration of the server_audit plug-in. The default is off, and log audit is enabled.

The specific definition of these parameters:

Server_audit_events

Specify the event type recorded to the log. Multiple values (connect,query,table,query_ddl, etc.) separated by commas can be used. If query cache (query cache) is enabled, the query returns data directly from the query cache, and there will be no table record.

Server_audit_excl_users

The user behavior of the list will not be recorded and connect will not be affected by this setting

Server_audit_file_path

Audit log path. If server_audit_output_type is FILE, audit logs are stored in the data directory by default. That is, under the data directory corresponding to parameter datadir. If you modify server_audit_file_path, the old audit log files will not be deleted. It needs to be cleaned and deleted manually. In addition, MySQL will start a new audit log rotation.

Server_audit_file_rotate_now

Force a rotation, and when the script SET GLOBAL server_audit_file_rotate_now = ON; is executed, the audit log is forced to rotate once.

Server_audit_file_rotate_size

Limit the size of a single rotation audit log, and rotate automatically when the limit is exceeded. The default value is 1000000, and the unit is byte. It is recommended to set it a little larger, for example, 64m size, 67108864. Set together with the parameter server_audit_file_rotations according to the actual demand.

Server_audit_file_rotations

The total number of rotation logs. When set to 0, audit logs are not rotated. The default value is 9. Generally, it needs to be modified according to the actual needs.

Server_audit_incl_users

Specify which users' activities will be recorded in the audit log. Connect will not be affected by this variable, which has a higher priority than server_audit_excl_users.

Server_audit_loc_info

This is a parameter used internally by the plug-in and doesn't make any sense to the user. In previous versions, users treated it as a read-only variable, but in later versions, it was not visible to users.

Server_audit_logging

The audit function is turned on and off. ON means to enable the audit function, and OFF means to disable the audit function.

Server_audit_mode

Identifies the version for development testing. This variable does not have any special meaning for the user. Its value mainly reflects the server version used to start the plug-in for developers to test.

Server_audit_output_type

Specify the type of audit log. You can choose either SYSLOG or FILE. The default is FILE.

Server_audit_query_log_limit

The length limit of the query string in the record. The default is 1024

Server_audit_syslog_facility

When the audit log type is syslog mode, it defines the "functionality" of the records that will be sent to the system log. You can use this parameter to filter logs later.

Server_audit_syslog_ident

Set up ident as part of each syslog record

Server_audit_syslog_info

The specified info string will be added to the syslog record

Server_audit_syslog_priority

Define the syslogd priority for logging

3. Error log

Query the path to the err log. At this point, the log configuration and the required files are ready for intrusion analysis based on the audit log server _ audit.log and data.err.

II. Thinking of actual combat

By building the sqli-labs test environment, according to the mysql log, analyze the log records of the three attack modes of mysql brute force cracking, manual injection and sqlmap os-shell, restore the attack scene as much as possible, and trace the attack source information.

1. Mysql brute force cracking

Mysql opens the port to the public and uses mysql to crack the log information.

The ip address of the attack and the time of the first connection are recorded in data.err

200909 10:15:41 [Warning] IP address' 192.168.106.180' could not be resolved: I don't know such a host.

Server_audit.log you can see from the following figure that the attacker cracked the account root with multiple threads, and the attacker ip 192.168.106.180 returned code 1045. Four query operations were performed after a successful login.

The log format is:

[timestamp], [serverhost], [username], [host], [connectionid], [queryid], QUERY, [database], [object], [retcode]

You can filter and analyze the log according to the account number, ip address and return code, and query the operation characteristics after login, and you can speculate about the cracking tool used.

2. Sql injection attack

Using sqli-labs to simulate the vulnerable application, github downloads the sqli-labs code and copies it to the phpstudy www directory, runs the project, and obtains the webshell by manual input.

Simulation scenario: it is known that the server has been phpinfo and is suspected to have been invaded, so you need to analyze the intrusion point and the method to obtain permissions through the log.

There is no change in data.err this time. We analyze the access.log of nginx and the server_audit.log of audit plug-in.

It is found that both access.log and server_audit.log record that the attack is sql injection, and write the backdoor to the web directory through mysql outfile, execute phpinfo, and trigger the alarm. Because access.log does not record the body of the post request, server_audit.log can see more detailed attack details.

3. Sqlmap os-shell attack

Obtain mysql os-shell through sqlmap, analyze the os-shell process, and what are the characteristics of the log

Simulation scenario: suppose that in scenario 2, during the sql injection attack, the application fixes the sql vulnerability and restricts the permissions of the web directory, and the monitoring discovers the alarm of command execution and tries to analyze the intrusion point.

Sqlmap-d "mysql://root:root@192.168.x.x:3306/mysql"-os-shell, execute whoami after success

Looking at the server_audit.log log, you can see the complete process of sqlmap os-shell execution, including detecting the mysql version, character set, operating system type, how to implement udf to achieve command execution, and so on.

The log contains many characteristics of sqlmap, and udf achieves the characteristics of command execution, which can be preliminarily judged to be the execution of udf commands caused by mysql password disclosure in combination with other logs.

Errors in the execution of the command are recorded by the error log data.err, which refers to a screenshot of the mysql blackmail virus, revealing the server address information controlled by the attacker.

After reading the above, have you mastered how to configure log audit in MySql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.