MYSQL8 Security – Audit Management

Audit concepts

Audit: record the user’s actions for later verification, butproduction environmentThe database itselfnot recommendedOn, it affects performance and can be usedparticular purposeRealization audits.

I. MYSQL8 open source audit mysql-audit

mysql5.7Enterprise EditionSelf-audit function, requiredcover the costs。
community editionIt is possible to useMcAfeeOpen source software providedmysql Audit Pluging
Project Address:https://github.com/trellix-enterprise/mysql-audit

mysql-audit installation and configuration

0、Download and unzip the plug-in

# Download the plugin zip
wget -c https://github.com/trellix-enterprise/mysql-audit/releases/download/v1.1.13/audit-plugin-mysql-8.0-1.1.13-1008-linux-x86_64.zip

# Unzip
unzip audit-plugin-mysql-8.0-1.1.13-1008-linux-x86_64.zip

# Go to the lib directory
cd audit-plugin-mysql-8.0-1.1.13-1008/lib

# Assign executable privileges
chmod +x libaudit_plugin.so 

# Modify the owner and group to msyql:mysql
chown mysql:mysql libaudit_plugin.so

1, check mysql plugin location

-- View plug-in location
SHOW global variables LIKE '%plugin_dir%';

2. Upload the library file to the plug-in directory

# Copy to plugin directory
cp audit-plugin-mysql-8.0-1.1.13-1008/lib/libaudit_plugin.so /usr/lib64/mysql/plugin/

3. Modify my.cnf.

# Stop the mysqld service
systemctl stop mysqld

modificationsmy.cnfconfiguration file

[mysqld]
# Load the AUDIT audit plugin named libaudit_plugin.so
plugin-load=AUDIT=libaudit_plugin.so
# Enable audit logging in JSON format
audit_json_file=on
# Specify the event audit file path
audit_json_log_file=/var/log/mysql-audit.json
# Specify the type of audit event
## If audit_record_cmds is not specified, all DDL, DML full record
audit_record_cmds='insert,delete,update,create,drop,alter,grant,truncate'

Start the mysqld service

-- Start the mysqld service
systemctl start mysqld

4、Installation of plug-ins

-- root Log in to mysql
mysql -uroot

-- Installation of the audit plugin
install plugin audit soname 'libaudit_plugin.so'；

-- View audit plugin version
SHOW global status LIKE 'audit_version';

5. View mysql-audit logs

json viewer tool:https://blog.csdn.net/omaidb/article/details/125581170

# Check to install the json viewer tool jq
dnf install jq -y

# View the last 100 logs
tail -100 /var/log/mysql-audit.json

# View json format logs with jq
tail -100 /var/log/mysql-audit.json |jq

Error installing plugin

mountinglibaudit_plugin.soThe plugin reports an error.

Solution:

countmysqldtheshiftThe following is a list of the most important things that you can do to help you.mysqldAmount of deviation.

1. Calculate the offset

# Install the gdb package
dnf install -y gdb

# Find the offset-extract.sh script

# Calculate the offset using the offset-extract.sh script
offset-extract.sh /usr/sbin/mysqld

2. Add the offset to my.cnf

[mysqld]
audit_offsets = calculated offsets

3. Adding an offset still reports an error

Second, MYSQL comes with init-connect + binlog to achieve mysql audit

1. Create a table to store connection information

-- Create a table of connection information
CREATE database auditdb DEFAULT CHARSET utf8mb4;

-- Access to auditdb repository
use auditdb;

-- Create auditdb.accesslog (access log) table
CREATE TABLE auditdb.accesslog(
    ID INT PRIMARY KEY auto_increment,
    ConnectionID INT,
    ConnUserName VARCHAR(30),
    PrivMatchName VARCHAR(30),
    LoginTime timestamp
);

2、Configuration authority

-- Configuration rights
-- Inserts a record into the mysql.db table, authorizing all users to access the auditdb database on any host with select and insert operation privileges.
-- host, db, user, select_priv, and insert_priv are the names of the fields in the table;
-- % stands for wildcard, indicating that any IP address can be matched using this record;
-- 'auditdb' indicates the name of the database to be authorized, in this case auditdb;
-- '' indicates the name of the user to authorize, an empty string here means that all users will match on this record;
-- YY indicates that the user has privileges for select and insert operations on the auditdb database.
INSERT into mysql.db(host, db, user, select_priv, insert_priv)
values('%', 'auditdb', '', 'Y', 'Y');

-- Submission of services
cmomit;

-- Application rights configuration
FLUSH PRIVILEGES;

3, placement init-connect

# This configuration can be used to record basic information about all database connections for auditing and monitoring purposes.
# init-connect: SQL statement will be executed after each new client connects successfully
# Insert a record into the table named "auditdb.accesslog" that contains the ConnectionID, ConnUserName, PrivMatchName, and LoginTime.
## The connection_id() function is used to get the ID of the current connection.
## The user() function is used to get the user name of the current connection.
## The current_use() function is used to get the name of the permission match used by the current connection
The ## now() function is used to get the current system time.
init-connect='INSERT into auditdb.accesslog(ConnectionID,ConnUserName,PrivMatchName,LoginTime) values(connection_id(),user(),current_use(),now());'

# Specify the binlog storage path and filename prefix.
## binlog logs all modifications to the database, including inserts, updates, and deletes
log_bin=/var/lib/mysql/binlog
# Specify the storage path and file name of the binlog index file
log_bin_index=/var/lib/mysql/binlog.index

Restart the mysqld service

# Restart the msyql service
systemctl restart mysqld

4. Record and track tests

If it isrootLog in.It won't be recorded.Information.

# Use the mysqlbinlog utility to read the binary log file named binlog.000001
## The --start-datetime and --stop-datetime parameters specify the time range to be searched, i.e., starting at 16:00 on April 12, 2018 and ending at 16:00 on April 12, 2018, respectively
## The -i parameter indicates that case is ignored
## grep -B 20 Displays the first 20 lines of the match
mysqlbinlog --start-datetime='2018-04-12 16 00' --stop-datetime='2018-04-12 16 00' binlog.000001 |grep -i 'keyword' -b 20

-- View access log table
slect * from auditdb.accesslog;