SQL Audit | SQLE-SQL Audit Platform Experience Report

Author: Liu Xinwang

MySQL DBA, has focused on MySQL database for many years. Now he works for a local life service Internet company and is responsible for database related work.

Source of this article: original submission

* Produced by Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate it for reprinting
source.

---

product description

1. Experience environment

• Experience product: SQLE
• Software version: sqle-ce-1.2111.0-pre2
• Deployment environment: CentOS Linux release 7.9.2009 (Core)

2. product description

• SQLE ( https://opensource.actionsky.com/sqle/ ) is developed and open sourced by Shanghai Aikesheng Information Technology Co., Ltd. It supports multi-scenario audits, supports standardized online procedures, natively supports MySQL audits and the database types are available. Extended SQL audit tool.

3. demand analysis

• Product Positioning

  • In the Internet age, an app may be used by tens of millions of users; the scale of its business is large, the update iterations are fast, and many business lines are daily online SQL; the DBA's review and execution of the online SQL has become very busy. How to efficiently ensure the efficient execution and quality of SQL statements has a great impact on the efficient operation and long-term stability of the system.

• User needs analysis

  • The core requirements are: developer self-service, platform preliminary review, DBA review, implementation and launch.

• User segmentation

  • Main users: developers and DBAs; developers want convenient, efficient, independent and controllable online business SQL; DBAs want convenient, efficient, safe, and high-quality audit SQL to go online.

product analysis

1. Product Structure

Through the SQLE product structure diagram, we can see that the SQLE interface design is relatively simple, and the workbench can see the to-do work related to itself; the home page list is placed in the core function [work order] audit, and other functions are collected in the platform management; [Rule] You can consider the income into the [Platform Management]; [Audit Plan] can also consider the income into the [Platform Management], and the audit results can be integrated into the work order to facilitate unified processing.

2. Product use flow chart

As you can see from the SQLE flow chart, the overall process is divided into two parts. One is the basic configuration. After the basic configuration is configured, the online process can be executed; for the unqualified functions, work orders can still be executed. It does not have the meaning of automatic review. For ERROR level errors, it can be automatically rejected without going online; after entering the work order page, the review operation button (work order progress part) cannot be found well, and you need to pull down the window to find it; you can perform the review operation Add an operation column to the end of the audit result list, and the experience will be more friendly. The image below shows the review operation below after zooming 67%.

3. Functional experience analysis

(1) Create SQL work order-SQL statement does not support input association. It is recommended to increase input association to improve input efficiency

(2) Create SQL work order-SQL statement cannot prompt for obvious grammatical errors in time. It is recommended to add grammatical error prompts to find obvious problems in advance

(3) Create a SQL work order-the height of the SQL statement input box is too high, it is not convenient to view the results after clicking the audit, it is recommended to reduce the height and increase the width

(4) Create SQL work orders-work orders do not support custom online time, and do not support scheduled online

4. Presentation layer

• The overall platform is mainly in white and gray tones, with stable and simple color matching, and different colors for different states. It is guessed that it should be the ant design component library; the page list and CURD page are not concise and clear enough, and need to be optimized.

Competitive product analysis

	SQLE	Archery
Review	✓	✓
Inquire	×	✓
implement	✓	✓
Backup	×	✓
Notice	email	DingTalk, corporate WeChat, email notification
Process	✓	✓
whitelist	Enterprise Edition
Slow log audit	Enterprise Edition	Only collect impressions, no review
Audit plan	Support MyBatis Scanner	×
Expand	Support plug-in	Support plug-in

feeling of using

Due to the limited time, it is only a personal test experience; the overall design of SQLE is good, in line with all aspects of the current mainstream platform; I prefer the main design points:

• The management of DB type and instance association template is very detailed
• The data source association process makes the approval granularity more detailed and management more convenient
• SQL whitelist, to deal with special situations, to avoid business being stuck by rules, and to prevent managers from directly operating the database; SQL fingerprints are equivalent to providing a unified configuration function, which is more convenient
• The audit plan is a good thing; it is a good reference for those who want to integrate CI/CD👍
• Audit supports plug-in, easier to extend
• The backend is developed in go language, which is more friendly to deployment compatibility and performance