Shulou(Shulou.com)05/31 Report--

Today I'll show you how to build RDS audit compliance monitoring based on SLS. The content of the article is good. Now I would like to share it with you. Friends who feel in need can understand it. I hope it will be helpful to you. Let's read it along with the editor's ideas.

Background

Database is the data core of enterprise business, and its security problems have become an important source of leakage and tampering in the traditional environment. Therefore, the operation of the database, especially the audit log recorded by the full amount of SQL execution, is particularly important.

SLS and RDS jointly launched RDS SQL audit feature to deliver RDS SQL audit logs to SLS in real time; SLS provides real-time query, visual analysis, alarm and other functions.

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

Login and logout of the database.

DDL (Data Definition Language) operation: SQL statements defined on the database structure, including CREATE, ALTER DROP, TRUNCATE, COMMENT, and so on.

DML (Data Manipulation Language) operation: SQL operation statements, including SELECT, INSERT, UPDATE, DELETE, etc.

Other SQL performs operations, including any other controls performed through SQL, such as rollback, control, and so on.

Information about the delay of SQL execution, the execution result, the number of rows affected, and so on.

In addition, SLS also monitors the operational compliance of RDS and discovers abnormal configuration of RDS in time to ensure the security of the database.

RDS Log Audit-Collection

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

Cloud product collection channel

Advantages: it is easy to configure in a small amount and in the same region instance collection scenario.

Disadvantages: cross-region and cross-account are not supported, and dynamic discovery of instances is not supported. If you need to cross-region and cross-account, you need to build your own data processing task.

Log audit channel

Advantages:

Support cross-account and cross-regional centralized collection to facilitate audit report analysis.

Support instance discovery, enable automatic collection with one click, and control the collection range through acquisition strategy.

Disadvantages:

AK authorization or manual authorization is required to turn on the log audit APP.

The SQL insight feature of collected instances is automatically enabled, and automatic shutdown is not supported. If you need to disable SQL insight, you need to disable the log audit RDS collection feature or use the collection policy to control instance collection, and then go to the RDS console to disable SQL insight instance by instance.

Cloud product collection channel single account collection scenario in the same region (only RDS audit logs can be collected in the log database in the same region)

On the home page of the SLS console, "access data" area, select "RDS Audit". Below, take the example of collecting Zhangjiakou as an example.

Because the collected instance is located in Zhangjiakou, you need to build or select existing project and logstore in Zhangjiakou.

Note: the audit logs of RDS instances collected only support collection in the same region.

On the "data Source configuration" page, you can view all the RDS instance information in Zhangjiakou after completing the RAM authorization. The default log delivery function is turned off. After that, you can select the corresponding "enable delivery" button according to the log collection requirements, and enable the log delivery feature of the corresponding instance.

At this point, the collection of SQL audit logs is enabled, and you can jump to the logstore configured above to view the RDS instance logs.

Cross-region and cross-account collection scenarios

Because the cloud product collection channel can only collect RDS audit logs to log databases in the same region, to break this limit to achieve cross-account cross-region collection, it is necessary to build cross-domain or cross-account data processing tasks.

Because self-built data processing tasks require more complex authorization, they will not be described in detail here. If necessary, please refer to:

1. Configure custom role authorization

2. Best practices: transfer data across regions

3. Best practice: the "cross-account distribution" section of multi-objective Logstore data distribution.

Thus it can be seen that the cloud product collection channel only has the advantage of convenient collection in simple acquisition scenarios, but when dealing with cross-region and cross-account collection, not only the data synchronization link is long, but also requires a more complex authorization process; and when the instance changes (or even new instances appear), you need to maintain the synchronization link manually, which is very expensive to maintain. The log audit channel can well solve the pain points of cross-regional, cross-account collection and high cost of instance change and maintenance.

Log audit collection channel log audit authorization

It is recommended to use Aliyun RAM user operation.

Create an Aliyun RAM user, grant the user AliyunRAMFullAccess and AliyunLogFullAccess permissions, and create an AK.

Log in to the above RAM user and select "Log Audit Service" in the SLS console.

First entry requires authorization to open. Enter the AK created in step 1 here, and select the central Project region where the audit log is stored.

If the following page appears, it indicates that it has been authorized to complete. Then you can open the corresponding cloud product logs according to the needs of log collection. For example, you need to collect operation audit (Actiontrail) logs and RDS SQL audit logs.

Configure SQL audit collection

This article focuses on how to open the RDS SQL audit log and manage the log collection scope through the collection policy. First of all, you need to configure the collection policy to enable SQL audit log. For a complete syntax description, please see the collection strategy document. Here are some common strategies.

Collect instance logs for a specific area. For example: only collect examples of Hangzhou and Shanghai.

Instances of specific tags are not collected. For example: label the test instance with type and take the value test.

Only limited instance logs are collected.

SQL audit

RDS Log Audit-report

The SLS-based SQL audit log provides three audit reports:

RDS Audit Center: mainly shows the SQL implementation indicators, distribution, trends and other information of all databases. For example: statistics of PV, UV, operating database / datasheet, etc.

RDS Audit Security Center: 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 execution of SQL, the average time of SQL execution, the specific distribution and source of slow SQL, etc.

RDS log audit-alarm

SLS Log Audit has released new built-in alarm rules, including 19 built-in rules for RDS SQL auditing (which will be expanded later).

Rule View

Go to the SLS homepage-> Log Audit Service-> Audit alarm on the left side of the console-> Rule configuration / alarm rules to enter the audit alarm rule configuration page. There are two main categories of rules:

SQL audit rules (RDS security): mainly monitor the execution exception of SQL. For example, slow SQL, or bulk deletion, and so on.

Premise: enable RDS SQL audit log collection through log audit APP.

RDS operation compliance rules: it is mainly based on CIS rules to monitor the operation configuration of RDS.

Prerequisite: enable Actiontrail to operate log collection through log audit APP.

Alarm configuration

Action policy configuration

Nailing Channel Notification

The following example: all alarms send nail notifications.

SMS / voice channel notification:

The following figure example: when the alarm level is greater than critical, send a voice alert to the SLS Audit built-in user Group. For more information about the phone number of the notifier, please see creating users and user groups.

Alarm sample

Next, we use two specific examples to introduce the use of RDS audit alerts.

SQL audit sample-- slow SQL audit

Turn on the alarm

Set alarm parameters according to the needs of users. For example, slow SQL detection threshold, filtering whitelist and so on.

Click the open button and the alarm will be turned on.

Structural anomaly

Test data set

# Table structure mysql > desc test +-+ | Field | Type | Null | Key | Default | Extra | +- -- +-+ | id | int (10) unsigned | NO | PRI | NULL | auto_increment | title | varchar | NO | MUL | NULL | author | | varchar (40) | NO | | NULL | submission_date | date | YES | MUL | NULL | | +-| -+ 4 rows in set (0.04 sec) # data 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 | +-

Slow SQL

# use the index field group bymysql > 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 in set (0.06 sec) # the index field is group by after operation, which 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 in set (0.58sec)

Alarm monitors slow SQL and initiates alarm notification.

Sample RDS operation compliance-instance access whitelist exception configuration

This is all about how to build RDS audit compliance monitoring based on SLS. For more content related to how to build RDS audit compliance monitoring based on SLS, you can search the previous articles or browse the following articles to learn! I believe the editor will add more knowledge to you. I hope you can support it!

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.