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

Let's take a look at how AWS RDS Mysql turns on the database audit function. I believe you will benefit a lot after reading it. I hope you want this short article on how AWS RDS Mysql turns on database audit function.

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 feature and is a charging component. For the community version of Mysql, the common audit plug-ins are the MariaDB audit plug-in and the Percona audit plug-in.

1. AWS's RDS Mysql is a community version of the audit method, which uses the "MariaDB audit plug-in". For more information, please see the official documentation. The setting method will not be discussed in this article. What you should pay attention to is the setting of the following parameters.

Https://docs.amazonaws.cn/AmazonRDS/latest/UserGuide/Appendix.MySQL.Options.AuditPlugin.html

Option settin

Effective value

Default value

Description

SERVER_AUDIT_FILE_PATH

/ rdsdbdata/log/audit/

/ rdsdbdata/log/audit/

Location of the log file. The log file contains the active records specified in SERVER_AUDIT_EVENTS. For more information, see viewing and listing database log files and MySQL database log files.

SERVER_AUDIT_FILE_ROTATE_SIZE

1-1000000000

1000000

When the byte size is reached, it causes file rotation. For more information, see Log File size.

SERVER_AUDIT_FILE_ROTATIONS

0-100

nine

The number of log rotations 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. You can log in by yourself to install the MariaDB audit plug-in.

CONNECT: records successful and failed database connections and database disconnections.

QUERY: records all query text run against the database.

TABLE: records the tables that are affected by the query when running the query against the database.

For MariaDB, CONNECT, QUERY, and TABLE are supported.

For MySQL, CONNECT and QUERY are supported.

SERVER_AUDIT_INCL_USERS

Multiple comma separated values

None

Only the activities of the specified user are included. By default, the activity of all users is recorded. If a user is specified in both SERVER_AUDIT_EXCL_USERS and SERVER_AUDIT_INCL_USERS, the activity of that user is recorded.

SERVER_AUDIT_EXCL_USERS

Multiple comma separated values

None

Excludes the activities of the specified user. By default, the activity of all users is recorded. If a user is specified in both SERVER_AUDIT_EXCL_USERS and SERVER_AUDIT_INCL_USERS, the activity of that user is recorded.

Rdsadmin users query the database once a second to check the health 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 for the specified user set by this option.

SERVER_AUDIT_LOGGING

ON

ON

Logging is active. The only valid value is ON. Amazon RDS does not support deactivating logging. If you want to deactivate logging, remove the MariaDB auditing plug-in. For more information, see removing the MariaDB Audit plug-in.

two。 Once the audit function is enabled, we can see the audit log in console.

3. Next, let's take a look at what information audit log records.

3.1. The record of my front desk operation is as follows:

3.1.1 Log in to the database using the administrator user

# mysql- hmysql-rds.cq7qaukj3smd.rds.cn-northwest-1.amazonaws.com.cn-uadmin-pxxxxx

3.1.2 create a user test2 and give it some 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-168168-237.CNWAW. Com. CN' identified by 'xxxxx' WITH GRANT OPTION;Query OK, 0 rows affected, 1 warning (0.02 sec)

3.1.3 use the newly created user to connect to the database

# mysql- hmysql-rds.cq7qaukj3smd.rds.cn-northwest-1.amazonaws.com.cn-utest2-pxxxxx

3.1.4 perform database switching, create tables and other commands

Mysql > use tests;mysql > create table xx as select * from liang;mysql > commit;-comment: create is a DDL statement and is submitted by default, so commit is meaningless here.

3.2 audit log is as follows. You can see the following information

3.2.1. Who visits and executes, and where does it come?

-- test2,ec2-52-80-168-237.cn-north-1.compute.amazonaws.com.cn

3.2.2. Record all operation information performed

20190726 1515 UPDATE 10 01Graent SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES CREATE TEMPORARY TABLES ON *. * TO\ 'test2\' @\\ 'ec2-52-80-237.cn-north-1.compute.amazonaws.com.cn\' IDENTIFIED WITH\ 'mysql_native_password\' AS\'* 01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C\ 'WITH GRANT OPTION',020190726 15monger 10-4-2-104 memoir EC2-52-80-168-237.cnnorthnav 1.compute.amazonaws.com.cn Magi 13JI DISCONNECT Mirror 0.20190726 Ip-10-4-2-104The ip-10-4-2-104memoir Test2 talent EC2-52-80-168-237.cnlect 14893Q QUERERYY last quarter select @ @ version_comment limit 1 0190726 15V 10V 10ip 10p Mill 10-4-2-104 MIT Test2 talent EC2-52-80-168-237.cnMill 14893Q UERYY final select @ @ QUERYY last 020190726 15VM 19ip 19ip Ec2-52-80-16837.cnmilonaws.com.cnmeme 14894QUERYREE DATABASE ()', 020190726 15lb 10lv 19lle Test2Magi EC2-52-80-168168-237.cnWhile northMust1.compute.amazonaws.com.cnMere 14,896 QUERYYJESTSTS show databases',020190726 15freestures show 19Querty 10-4-2-104 Magi Test2 Ec2-52-80-168-237.cnAfter northhouse 1.compute.amazonaws.com.cngrape 14897Q QUERYE tables',020190726 show tables',020190726 15 10Gill 31Mile ipsquire 10-4-2-104 Test2Mold EC2-52-80-168-237.cnhouse northhouse 1.compute.amazonaws.com.cnm14913Q QUERYMUERYMUERYMUERYMUERYMUERYMUERYMET Test2 create * from liang',1064.20190726 15magent10-4-2-104i Test2 Ec2-52-80-168-237.cnmurnorthafay1.compute.amazonaws.com.cnmpic 14-922 QUERYJEI table xx as select * from liang',020190726 15 create QUERYJESTESTESTONE 15GV 103MUERYJIZHI 10-4-2-104 TESTELE2 EC2-52-80-168-237.cnMays northMutual 1.compute.amazonaws.com.cnMill 14,923Q QUERYJESTESTESTI examples 0

4. Of course, the audit log records all the operations of the database, and naturally there is some information that we don't need. For example, the RDS background operates all the operation records of the user rdsadmin.

I suddenly realized that audit log can also be used to study some of the principles of automated operation and maintenance implemented behind AWS RDS.

20190726 15-15-10-2-10 4-10 4-10 4-10 4-10 4-10 4-10 4-10 4-10 4-10 4-10 4-10 4-10-10 4-2-10 4-10 4-2-10 4, 10-4, 10, 10, 10, 10, 10, 10, 10, 10, 10, 4, 4, 4, 10, 10, 10, 10, 10, 4, 4, 10, 10, 10, 10, 10, 4, 10, 4, 10, 10, 10, 4, 10, 4, 10, 4, 10, 10, 4, 10, 4, 10, 4, 10, 10, 4, 10, 10, 4, 2, 10, 10, 4, 2, 10, 10, 4, 2, 10, 4, 10, 4, 10, 4, 10, 4, 10, 4, 10, 4, 10, 4, 10, 10, 4, 2, 10, 10, 4, 2, 10, 10, 4, 2, 10, 10, 4, 2, 10, 4, 2, 10, 4, 2, 10, 4, 2, 10, 4, 4, 2, 10, 4, 2, 10, 4, 4, 2, 10, 4, 4, 2, 10, 4, 4, 2, 10, 4, 2, 10, 4, 2, 10, 4, 2, 10, 4, 4, 2, 10, 4, 2, 10, 4, 4, 2, 10, 4, 2, 10, 4, 2, 10, 4, 4, 10, 4, 2, 10, 4, 2, 10, 4, 4, 10, 4 'SELECT count (*) from information_schema.TABLES WHERE TABLE_SCHEMA =\' mysql\ 'AND TABLE_NAME =\' rds_heartbeat2\', 020190726 1515 mysql\ 'AND TABLE_NAME =\'\'', 151514 mysql\'10'', 020190726 1510 purveyor 20ptel 10-4-2-104 rdsadmingrine localhostler2m0905minute QUERYJECTSELECT value FROM mysql.rds_heartbeat2',020190726 1514 10WR 20ippense 10-4-2-104, rdsadmindrolocalhostLocalhost2, 906 'QUERYMINE 10-4-2-104 SELECT 10-4-2-104 15 NAME 10-4-10 4-10 4-10 4-10 4 rdsadmindSelect @ @ GLOBAL.read_only',020190726 15 Rd sadminmLocalhostMagee 10-4-2-10 4-10 4-2-10 4-10 4-10 4-2-10 4-10 4-10 4-10 4-10 4-10 4-10 4-10 4-10-10 Localhost,2,910,QUERY,'SELECT 1 recorder 020190726 15 BINARY LOGS TO localhost2pr select @ @ session.transaction_read_only',020190726 15Rd sadminmlop10-4-2-104proct 10-4-2-104 rdsadminmlore localhostMagicalhost2PUERYPUERYREE select @ @ rdsadmind10-2-104 rdsadmindlore localhost.0,0

5. But the operation information of the real rdsadmin does not need to be audited. We can consider not recording the audit information of rdsadmin users, but simply modify the parameter SERVER_AUDIT_EXCL_USERS=rdsadmin of the "option group"

5.1output of audit log after closing:

20190727 14PUBLING 54LINGOMINE 10-4-0-205 momentum rdsadmindirection localhostMagic 19LROENECTJECTHERY 020190727 14DRONECT.24RAPHY 54LIPLY 10-4-0-205

-normally, rdsadmin users query the database once a second to check the health of the database. After not recording the information of rdsadmin, the world is much cleaner immediately.

20190727 14 database user',020190727 25lux 04LINGUERYREE 10-4-0-205The adminrecoveryec2-52-80-168-237.cnfut.com. CNL 18pr 2689 QUERYREE create the database user',020190727 14MUERYREE 10-4-0-205g adminee EC2-52-80-168-237.cnFENTnorthFEN 1.compute.amazonaws.com.cnMere 1890QUERYLSCECT DATABASE ()', 020190727 14Mover25paraphrase 10-4-0-205pm admin Ec2-52-80-168237.cnAfter-80,168-237.cnAfter 2692writ QUERYWORER show databases',020190727 14Rover 25ver 06WEL ipLec2-52-80-168-237.cnFERT-1.compute.amazonaws.com.cnMere 18m2693Q UUERYJERT show tables',020190727 14Rover 2552-205i 10-4-0-205sec adminMageec2-52-80-168168-237.cn-north-1.compute.amazonaws.com.cn 18meme2715QUERYgramme userguide create table xx (id int)', 020190727 14purgQUERYJIZOREX 29pRICHY 10-4-0-205 admin. EC2-52-80-168-237.cnnorthLeg1.compute.amazonaws.com.cnLing 18,2716QUERYUERYUERYUERYUERYLTERYUERYLTERYUERYLTERYUERYLTERYUERYLTERYUERYLUERYUERYUERYLTERYUERYLINTERYLTERYUERYLINTERYUERYLING

6. For database audit, encryption and other operations, not only increase security, but also pay the cost of performance loss.

The following is the official blog of AWS, in which it is discussed that RDS Mysql still has a large performance loss after starting the audit.

Https://aws.amazon.com/cn/blogs/china/cloudwatch-logs-kinesis-firehose-athena-quicksight-amazon-aurora/?nc1=b_rp

7. After audit is enabled, a large number of audit log will be generated, but the log of RDS will be scrolled and deleted and cannot be saved for a long time. More statistics and analysis can be done if the audit is saved for the purpose of long-term audit.

Output the audit log to cloudwatch through the Modify page of RDS.

At this point, you have learned how to use the RDS Mysql database audit feature. In the future, I will continue to share how to use audit as a data source.

Through data processing, it is summarized into the data set / lake for further analysis and use.

After reading this article on how AWS RDS Mysql turns on database audit, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.