1. Background

Database is the data core of enterprise business, and its security has become an important source of leakage and tampering in the traditional environment. Therefore, it is very important to audit the operation behavior of database, especially the audit log of full SQL execution record.

SLS cooperates with RDS to launch RDS SQL audit function, which delivers RDS SQL audit logs to SLS in real time; SLS provides real-time query, visual analysis, alarm and other functions.

RDS SQL audit log records all the operations performed on the database. The information is obtained through the analysis of network protocol by the system, which has very low CPU consumption on the system and does not affect the efficiency of SQL execution. RDS SQL audit log includes but is not limited to the following operations:

• Log in and log out of the database.
• DDL (data definition language) operation: SQL statements defined for database structure, including create, alter drop, truncate, comment, etc.
• DML (data manipulation language) operation: SQL operation statements, including select, insert, update, delete, etc.
• Other SQL execution operations, including any other control executed through SQL, such as rollback, control, etc.
• SQL execution delay, execution result, number of rows affected, etc.

In addition, SLS also monitors the operation compliance of RDS to detect the abnormal configuration of RDS and ensure the database security.

2. RDS audit log collection

At present, there are two ways to collect SLS from RDS SQL audit logs:

• Cloud product collection channel

• Advantages: It is simple to configure in the scene of a small number of instances collected in the same region.
• Disadvantages: Does not support cross region, cross account; does not support instance dynamic discovery. If you need to cross region and account, you need to build your own data processing task.

• Log Audit Collection channel

• Advantage:

• Support cross account, cross regional centralized collection.
• Support instance discovery, one click to start automatic collection, and support to control collection range through collection strategy.

• Disadvantages:

• AK authorization or manual authorization is required to open the log audit app.
• The SQL explorer function of the collection instance will be automatically turned on, and automatic shutdown is not supported. If you want to turn off SQL explorer, you need to first turn off the audit log collection function, or set the instance not to collect through the collection policy, and then turn off SQL explorer one by one on the RDS console.

2.1 Cloud product collection channel

Single account collection in the same region (RDS audit logs can be only collected in the same region)

In the "Import Data" area on the home page of SLS console, select "RDS SQL Audit". The following is an example of ap-southeast-1.

Because the collected instance is located in ap-southeast-1, you need to create a new project or select the existing project and logstore in ap-southeast-1.

Note: RDS audit logs can be only collected in the same region.

"Specify Data Source" page: You can view all RDS instance information in ap-southeast-1. By default, the "Import Status" is turned off. After completing the ram authorization, you can open the  "Import Status" button to open the log delivery according to the log collection requirements.

Now the collection of SQL audit logs is completed. You can go to the logstore configured above to view the RDS audit logs.

Cross region and cross account collection

Because the collection channel of cloud products has the limitation that RDS audit logs can only be collected to the logstore in the same region, in order to break this limitation and realize cross account and cross region collection, it is necessary to build data processing tasks of cross domain or cross account.

Because the self built data processing task needs more complex authorization, it will not be described in detail here. If necessary, please refer to:

• Authorize Log Service to assume a custom role
• Transfer data across regions

It can be seen that the cloud product collection channel only has the advantage of convenient collection in the simple collection scenario. But when dealing with cross region and cross account collection, not only the data synchronization link is long, but also a relatively complex authorization process is required; moreover, when the instance changes (or the new instances are created), the synchronization link needs to be manually maintained, and the maintenance cost is very high. The audit channel can solve the pain of cross region, cross account collection and high maintenance cost of instance change.

2.2 Log Audit collection channel

Authorization And Config

It is recommended to use Alibaba cloud ram for user operation. Create an aliyun ram user, give the ram user "aliyunramfullaccess" and "aliyunlogfullaccess" permissions, and create an AK.

Log in to the ram user and select "Log Audit Service" on SLS console.

You should configure authorization for log collection for the first time. Enter the AK created in the first step, and select the central project region to store the audit logs.

If the following page appears, the authorization has been completed. After that, you can open the corresponding cloud product logs according to the needs of collecting logs. For example, you need to collect RDS SQL audit logs here.

If you have a requirement for cross account collection, you can configure multiple accounts for log collection.

Configure SQL audit collection

This chapter focuses on how to open RDS SQL audit log and manage the log collection scope through collection policy. The first step to start SQL audit log is configuring the collection policy. See the collection policy document for a complete syntax description. Here are some common policies.

• Collect the instance log of a specific region. For example, only ap-southeast-1 and ap-southeast-1 are collected.

• Instances of specific tags are not collected. For example, the instances with "type" tag which value is "test" are not collected.

• Only specified instance logs are collected.

SQL Audit Query

3. Rds audit log -- report

The SQL audit log based on SLS provides three audit reports

• RDS Audit Center: mainly displays the SQL execution indicators, distribution, trend and other information of all databases. For example: Statistics of PV, UV, operation database / data table, etc.

• RDS audit security center: it mainly shows the failed SQL and dangerous SQL of all databases, as well as the details, distribution and trend of mass deletion or modification events.

• RDS audit performance center: it mainly shows the specific performance indicators of all databases, such as the peak value of SQL execution, the average time of SQL execution, the specific distribution and source of slow SQL, etc.

4. Rds audit log -- alert

SLS log audit has newly released built-in alarm rules, including 19 built-in rules for RDS SQL audit (which will be expanded in the future).

4.1 Rule view

Through "SLS home page" -> "Log Audit Service" -> "Audit Alert" on the left side of console -> "Policy Settings " -> "Alert Rules", you can enter the audit alert rules configuration page. There are two main types of rules:

• SQL audit rules (RDS security): mainly for SQL execution exception monitoring. For example, slow SQL, or batch deletion.

• Premise: enable RDS SQL audit log collection through log audit app.

• RDS operation compliance rules: mainly based on CIS rules, it monitors the operation configuration of RDS.

• Premise: activate actiontrail operation log collection through log audit app.

4.2 Alert Config

Action policy configuration

Set up action policy to send alarm notification. At present, it supports Dingding, mailbox and other channels.

Alert example -- slow SQL audit

• Enable the alert

Set alert parameters according to user needs. For example, slow SQL detection threshold, white list and so on.

Alert Test

# table desc mysql> desc test;+-----------------+------------------+------+-----+---------+----------------+| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+| id              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title           | varchar(100)     | NO   | MUL | NULL    |                |
| author          | varchar(40)      | NO   |     | NULL    |                |
| submission_date | date             | YES  | MUL | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+4 rows inset (0.04 sec)
# mysql> select * from test limit 5;+----+--------+---------+-----------------+| id | title  | author  | submission_date |
+----+--------+---------+-----------------+|  1 | title1 | author1 | 2021-01-12      |
|  2 | title1 | author1 | 2021-01-12      |
|  3 | title1 | author1 | 2021-01-12      |
|  4 | title1 | author1 | 2021-01-12      |
|  5 | title1 | author1 | 2021-01-12      |
+----+--------+---------+-----------------+# Use index to group by# mysql> select title, count(1) as cnt from test where submission_date='2021-01-12' group by title;+--------+-------+| title  | cnt   |
+--------+-------+| title1 | 59392 |
| title2 |  8448 |
+--------+-------+2 rows inset (0.06 sec)
# makes the index invalid.# mysql> select title, count(1) as cnt from test where day(submission_date)=12 group by title;+--------+-------+| title  | cnt   |
+--------+-------+| title1 | 59392 |
| title2 |  8448 |
+--------+-------+2 rows inset (0.58 sec)

SLS monitors slow SQL and sends alert notification to user.