SQL Audit | Here are the most commonly used SQL development rules for MySQL/Oracle

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

The SQL Auditing Tool SQLE Enterprise Edition recently launched the first beta preview of the Oracle Auditing Plug-in. We collected problems we encountered with customers in the past and suggestions from industry experts, and compiled a large number of Oracle audit rules. In this version, we have selected a batch of rules with the highest frequency and the largest impact and added them to the Oracle audit plug-in. These rules can cover most scenarios of most customers without causing additional burden on customers. In the follow-up time, we will launch the Oracle audit plug-in to gradually improve the rule puzzle. What follows will give our understanding of high frequency rules. The development of the plugin itself has been introduced before, and the development documentation reference: https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_auditplugin/overview.html .

The article is mainly divided into the following four parts:

1. Introduction to the SQLE project

2. Introduction to the rules

3. List of rules

4. Follow-up plan

1. Introduction to the SQLE project

The SQLE of the Aikesen open source community is a SQL audit tool for database users and managers, which supports multi-scenario auditing, supports standardized online processes, natively supports MySQL auditing, and has scalable database types.

SQLE get

| Type| Address|
| --- | --- |
| Repository | https://github.com/actiontech/sqle |
| Documentation | https://actiontech.github.io/sqle-docs-cn/ | 9000 |
| Release information | https://github.com/actiontech/sqle/releases |
| Data Audit Plugin Development Documentation | https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_auditplugin/auditplugin_development.html |
| Community Edition Online Experience | http://124.70.158.246:8888/ <br/>Super administrator: admin, password: admin. |
|Enterprise Edition Online Experience| http://124.70.158.246:8889/ <br/>User: admin, Password: admin. || Type| Address|

2. Introduction to the rules

SQLE divides existing rules into the following categories according to the usage scenarios of the rules:

1. DDL specification class

This type of rule mainly restricts the table definition in different dimensions, including fields, various attributes of the index, etc. The rule value can be configured according to its own business scenario to meet the specifications of most table design.

2. Query class specification

This type of rules mainly supports some common statements that may affect performance, and can be selectively turned on and off according to their own specifications.

3. Rules for using suggested classes

This type of rule involves the use of red lines in some companies' databases, which are prohibited to use or avoid some potential use or maintenance risks.

4. Naming conventions

This type of rule mainly restricts the format of some table names, field names, and index names, and its main function is to improve readability as a unified naming standard.

5. execution plan class

This type of rule mainly uses the execution plan output when reviewing SQL, interprets the characteristics of the execution plan, and gives user prompts for specific SQL that affects performance.

3. List of rules

1. It is recommended to use a primary key for the table;
2. Tables are not recommended to use foreign keys;
3. It is recommended that the number of indexes do not exceed the threshold;
4. Too many table fields;
5. The number of columns of a composite index is not recommended to exceed the threshold;
6. Table does not recommend using the specified field type;
7. The length of table name, column name and index name cannot be greater than the specified bytes;
8. Database object naming prohibits the use of keywords.
9. select * is deprecated;
10. delete and update statements must have where conditions;
11. Avoid having clauses;
12. Subqueries are deprecated.
13. Drop operations other than indexes are prohibited;
14. Views are prohibited;
15. Triggers are prohibited;
16. A single SQL is not recommended to be too long.
17. unique indexes must use a fixed prefix;
18. Normal indexes must use a fixed prefix.
19. There is a Cartesian product for table associations;
20. Too many levels of table association nested loops;
21. Too many table associations;
22. Perform a full table scan on large tables;
23. There is an index to perform a skip scan;
24. Perform a full scan on large indexes.

4. Follow-up plan

• The Oracle Audit Plug-in will be in the development of the second preview version, and rules will continue to be added;
• Development of the PostgreSQL plugin is in progress;
• The development of the TiDB plugin will start in June.