Due to different security requirements, database audit is also a topic of concern to many customers. For RDS Mysql, can we audit each user's login and operation information? The answer is yes.
MySQL Enterprise Edition has this function and belongs to the charging component. Community version of Mysql, the common audit plug-in is "MariaDB audit plug-in" and Perona audit plug-in.
1. The RDS Mysql of AWS is a community edition. It uses the "MariaDB Audit Plug-in" for auditing. The use method is detailed in the official documents. This article will not dwell on the setting method. It should be noted that the following parameters are set.
https://docs.amazonaws.cn/AmazonRDS/latest/UserGuide/Appendix.MySQL.Options.AuditPlugin.html
Option Settings |
RMS |
Default values |
describe |
SERVER_AUDIT_FILE_PATH |
/rdsdbdata/log/audit/ |
/rdsdbdata/log/audit/ |
Location of log files. The log file contains the activity records specified in SERVER_AUDIT_EVENTS. For more information, see View and list database log files and MySQL database log file. |
SERVER_AUDIT_FILE_ROTATE_SIZE |
1–1000000000 |
1000000 |
When the byte size is reached, file rotation occurs. For more information, see Log file size. |
SERVER_AUDIT_FILE_ROTATIONS |
0–100 |
9 |
The number of log rotation to save. For more information, see Log file size and Download database log files. |
SERVER_AUDIT_EVENTS |
CONNECT,QUERY |
CONNECT,QUERY |
The type of activity to record in the log. Install MariaDB Audit Plug-in to log in. CONNECT: Record successful and failed database connections and database disconnection. QUERY: Record all query text running against the database. TABLE: Record tables that are affected by queries when running queries against a database. For MariaDB, CONNECT, QUERY and TABLE are supported. For MySQL, CONNECT and QUERY are supported. |
SERVER_AUDIT_INCL_USERS |
Multiple comma-separated values |
nothing |
Includes only the activities of the specified user. By default, all user activities are recorded. If a user is specified in SERVER_AUDIT_EXCL_USERS and SERVER_AUDIT_INCL_USERS, the user's activity is recorded. |
SERVER_AUDIT_EXCL_USERS |
Multiple comma-separated values |
nothing |
Exclude activities of specified users. By default, all user activities are recorded. If a user is specified in SERVER_AUDIT_EXCL_USERS and SERVER_AUDIT_INCL_USERS, the user's activity is recorded. Rdsadmin users will query the database once a second to check the status of the database. Depending on your other settings, this activity may cause your log file size to grow very rapidly. If you do not need to record this activity, add the rdsadmin user to the SERVER_AUDIT_EXCL_USERS list. Be careful CONNECT activity is always recorded for all users, even if the option sets the specified user. |
SERVER_AUDIT_LOGGING |
ON |
ON |
Logging is active. The only valid value is ON. Amazon RDS does not support disabled logging. If you want to disable logging, delete the MariaDB audit plug-in. For more information, see Delete MariaDB Audit Plug-in. |
2. After opening the audit function, we can see the audit log in console.
3. Next, let's see what information audit log s actually record.
3.1. The record of my front desk operation is as follows:
3.1.1 Use Administrator User to Log on to Database
# mysql -hmysql-rds.cq7qaukj3smd.rds.cn-northwest-1.amazonaws.com.cn -uadmin -pxxxxx
3.1.2 Create user test2 and give it something completely new
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES ON *.* TO 'test2'@'ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn' identified by 'xxxxx' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.02 sec)
3.1.3 Connect the database with the newly created user
# mysql -hmysql-rds.cq7qaukj3smd.rds.cn-northwest-1.amazonaws.com.cn -utest2 -pxxxxx
3.1.4 Implement database switching, create tables and other commands
mysql> use tests; mysql> create table xx as select * from liang; mysql> commit; -----Notes: create yes DDL Statement, default submission, so here commit Meaningless.
3.2 Auditlog is as follows, and you can see the following information
3.2.1. Who visited and executed it, and where did it come from?
--test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn
3.2.2. Recording of all operational information performed
20190726 15:10:01,ip-10-4-2-104,admin,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,13,883,QUERY,,'GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES ON *.* TO \'test2\'@\'ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn\' IDENTIFIED WITH \'mysql_native_password\' AS \'*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C\' WITH GRANT OPTION',0 20190726 15:10:03,ip-10-4-2-104,admin,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,13,0,DISCONNECT,,,0 ...... 20190726 15:10:10,ip-10-4-2-104,test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,14,0,CONNECT,,,0 20190726 15:10:10,ip-10-4-2-104,test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,14,893,QUERY,,'select @@version_comment limit 1',0 20190726 15:10:19,ip-10-4-2-104,test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,14,894,QUERY,,'SELECT DATABASE()',0 20190726 15:10:19,ip-10-4-2-104,test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,14,896,QUERY,tests,'show databases',0 20190726 15:10:19,ip-10-4-2-104,test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,14,897,QUERY,tests,'show tables',0 20190726 15:10:31,ip-10-4-2-104,test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,14,913,QUERY,tests,'create table as select * from liang',1064 ...... 20190726 15:10:39,ip-10-4-2-104,test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,14,922,QUERY,tests,'create table xx as select * from liang',0 20190726 15:10:43,ip-10-4-2-104,test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,14,923,QUERY,tests,'commit',0
4. Of course, the audit log will record all the operations of the database, and naturally there will be some information which we do not need. For example, RDS background operation user rdsadmin's full operation record.
Suddenly realized that audit log can also be used to study some of the principles of automated operation and maintenance implemented after AWS RDS.
20190726 15:10:20,ip-10-4-2-104,rdsadmin,localhost,2,900,QUERY,,'SELECT 1',0 20190726 15:10:20,ip-10-4-2-104,rdsadmin,localhost,2,901,QUERY,,'SELECT 1',0 20190726 15:10:20,ip-10-4-2-104,rdsadmin,localhost,2,902,QUERY,,'SELECT 1',0 20190726 15:10:20,ip-10-4-2-104,rdsadmin,localhost,2,903,QUERY,,'SELECT count(*) from information_schema.TABLES WHERE TABLE_SCHEMA = \'mysql\' AND TABLE_NAME = \'rds_heartbeat2\'',0 20190726 15:10:20,ip-10-4-2-104,rdsadmin,localhost,2,904,QUERY,,'SELECT 1',0 20190726 15:10:20,ip-10-4-2-104,rdsadmin,localhost,2,905,QUERY,,'SELECT value FROM mysql.rds_heartbeat2',0 20190726 15:10:20,ip-10-4-2-104,rdsadmin,localhost,2,906,QUERY,,'SELECT 1',0 20190726 15:10:20,ip-10-4-2-104,rdsadmin,localhost,2,907,QUERY,,'SELECT @@GLOBAL.read_only',0 20190726 15:10:25,ip-10-4-2-104,rdsadmin,localhost,2,908,QUERY,,'SELECT 1',0 20190726 15:10:25,ip-10-4-2-104,rdsadmin,localhost,2,909,QUERY,,'SELECT NAME, VALUE FROM mysql.rds_configuration',0 20190726 15:10:25,ip-10-4-2-104,rdsadmin,localhost,2,910,QUERY,,'SELECT 1',0 20190726 15:10:25,ip-10-4-2-104,rdsadmin,localhost,2,911,QUERY,,'SELECT @@session.transaction_read_only',0 20190726 15:10:25,ip-10-4-2-104,rdsadmin,localhost,2,912,QUERY,,'PURGE BINARY LOGS TO \'mysql-bin-changelog.008941\'',0
5. But the real operation information of rdsadmin does not need to be audited. We can consider not recording the audit information of rdsadmin users, just modify the parameter SERVER_AUDIT_EXCL_USERS=rdsadmin of the "Options Group"
5.1 Auditlog output after closing:
20190727 14:24:54,ip-10-4-0-205,rdsadmin,localhost,19,0,CONNECT,,,0 20190727 14:24:54,ip-10-4-0-205,rdsadmin,localhost,19,0,DISCONNECT,,,0
Normally, rdsadmin users will query the database every second to check the status of the database. Without recording rdsadmin's information, the world immediately cleared up a lot.
20190727 14:25:04,ip-10-4-0-205,admin,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,18,2689,QUERY,,'create database user',0 20190727 14:25:06,ip-10-4-0-205,admin,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,18,2690,QUERY,,'SELECT DATABASE()',0 20190727 14:25:06,ip-10-4-0-205,admin,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,18,2692,QUERY,user,'show databases',0 20190727 14:25:06,ip-10-4-0-205,admin,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,18,2693,QUERY,user,'show tables',0 20190727 14:25:26,ip-10-4-0-205,admin,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,18,2715,QUERY,user,'create table xx (id int)',0 20190727 14:25:29,ip-10-4-0-205,admin,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn,18,2716,QUERY,user,'commit',0
6. For database auditing, encryption and other operations, while increasing security, we also have to pay the cost of performance loss.
The following is the official AWS blog, which discusses that RDS Mysql still suffers from considerable performance loss after the audit is started.
7. After opening audit, a large number of audit log s will be generated, but the RDS logs will be scrolled and deleted, which can not be preserved for a long time. If audits are saved for long-term auditing, more statistics and analysis can be done.
The audit log is output to cloudwatch through the Modify page of RDS.
So far, we have learned how to use RDS Mysql database audit function. In the future, I will continue to share how to use audit as a data source.
Data are processed and aggregated into data sets/lakes for further analysis and use.