SQL Audit | How SQLE Develops a Custom Rule

Author: Jason

Worked for Home Credit Consumer Finance Co., Ltd. as a DBA. He has worked as a DBA for Oracle, Mongo, MySQL, and the development of big data ETL. He has a strong interest in NEWSQL and cloud-native distributed databases.

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.

---

SQLE is developed and open sourced by Shanghai Aikesen Information Technology Co., Ltd. It supports SQL audit, index optimization, pre-audit, post-audit, standardized online process, native support for MySQL audit, and scalable database type SQL audit tool.

Official homepage https://opensource.actionsky.com/sqle/

Official Documentation Introduction · SQLE manual (actiontech.github.io)

(!!! Advance statement: The secondary development is purely personal technical research, and no commercial profit is allowed)

Hello everyone! This time I will share how to develop a custom review rule (based on MySQL 5.7 syntax).

Before developing the code, let's sort out the specific process of auditing SQL from the perspective of background API calls:

We can first use POSTMAN from the code to review this method:

First, determine the API interface for rule verification:
http://10.25.15.83:10000/v1/tasks/audits

The entry to the source code method corresponding to the API is:
api.controller.v1.task.go --> func CreateAndAuditTask(c echo.Context)

Since the system has done JWT login verification, we need to simulate the login first:

1) Call the login API 10.25.15.83:10000/v1/login to get the token:

The default is admin/admin, after successful login, we can get the token

2) We call the API interface of rule verification: http://10.25.15.83:10000/v1/tasks/audits

We prepared a simple SQL statement:

 create table test (id int not null, name varchar(20));

This statement does not meet the audit rules as follows:
1) No primary key
2) No column and table level comments
3) No innodb engine specified

We first set the parameters: You can refer to the structure of the request.

 type CreateAuditTaskReqV1 struct {
   InstanceName   string `json:"instance_name" form:"instance_name" example:"inst_1" valid:"required"`
   InstanceSchema string `json:"instance_schema" form:"instance_schema" example:"db1"`
   Sql            string `json:"sql" form:"sql" example:"alter table tb1 drop columns c1"`
}

Parameter 1: InstanceName -> DBA
Parameter 2: instance_schema = "testdb"
Parameter 3: sql => create table test (id int not null, name varchar(20));

We also need to set the token: this token can be obtained from the API return value of the login

Let's try to call the audit interface: http://10.25.15.83:10000/v1/tasks/audits

Let's take a look at the meaning of the response returned to us:

 {
   "code": 0,
   "message": "ok", --表示api调用成功
   "data": {
       "task_id": 1,  --返回的审核结果保存在 task_is =1的任务中
       "instance_name": "DBA", --实例名
       "instance_schema": "testdb", --DB名
       "audit_level": "error", --ERROR 级别的错误
       "pass_rate": 0,  --通过率为0
       "status": "audited", --已经审计的状态
       "sql_source": "form_data" --直接获得SQL语句的方式
   }
}

If we want to know the results returned by the specific audit, we need to query the database: ("task_id": 1, -- the returned audit results are stored in the task with task_is = 1)

 mysql> select  audit_result from execute_sql_detail where task_id=1\G
*************************** 1. row ***************************
audit_result: [notice]列建议添加注释
[error]表必须有主键
[notice]必须使用Innodb数据库引擎
[notice]表建议添加注释
1 row in set (0.00 sec)

From the response information, it is completely in line with our previous expected results.

Let's track the entire process of this audit from the perspective of code:
SQLE adopts a development model similar to the database driver plug-in. The interface driver.go has defined abstract functions. The specific implementation requires each specific database to complete such as mysql.go, oracle.go

Here are the specific steps on how to add a new rule to the review platform:

The rules of the New Rule are:
We want to have the specified columns in the newly created TABLE table, such as audit fields such as cdate, cdate, creator, edate, and editor.

First find the rule table in the corresponding database is rule:

 mysql> select * from rules \G
*************************** 28. row ***************************
  name: ddl_check_object_name_using_keyword
db_type: mysql
  desc: 数据库对象命名禁止使用保留字
 level: error
  type: 命名规范
params: NULL
*************************** 29. row ***************************
  name: ddl_check_pk_name
db_type: mysql
  desc: 建议主键命名为"PK_表名"
 level: notice
  type: 命名规范
params: NULL

....

Let's manually insert a record for a new rule:

 mysql> INSERT INTO sqle.rules
   -> (name, db_type, `desc`, `level`, `type`, params)
   -> VALUES('ddl_check_audit_column', 'mysql', '建表语句需要包含4个审计列(cdate,creator,edate,editor)', 'notice', '命名规范', NULL);
Query OK, 1 row affected (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)

We can see from the page that this newly added rule has appeared in the list:

We append this new rule to our custom rule template:

The last step is to submit the template information for the rule.

Let's test our newly added rules from the page: (Of course, the corresponding rules need to add the corresponding background code, which will be introduced later~)

We create a new audit task:

Enter the table creation statement:

 create table test (id int not null, name varchar(20));

Click the Review button

We can see that our custom audit rule has taken effect:

Finally, let's take a look at how the background code is developed to implement this rule:

The logic code corresponding to this rule in the background is: (entry-level simple code) This code is added to sqle/driver/mysql/rule/rule.go to use the Map structure to determine whether the necessary column names exist review statement in the table

 //可以在linux下进行命令行的代码单元测试
// dlv test github.com/actiontech/sqle/sqle/driver/mysql -- -test.run ^TestCheckAuditColumn$
func checkAuditColumn(ctx *session.Context, rule driver.Rule, res *driver.AuditResult, node ast.Node) error {
   var auditCols = [4]string{"cdate","edate","creator","editor"}
   var set map[string]struct{}
   set = make(map[string]struct{})
   for _, value := range auditCols{
      set[value] = struct{}{}
   }
    var cnt int = 0
   switch stmt := node.(type) {
   case *ast.CreateTableStmt:
      for _,value := range stmt.Cols {
         fmt.Println(value.Name.Name)
         if _, ok := set[value.Name.Name.String()];ok {
            cnt++
         } 

      }
   if cnt != 4{
      addResult(res, rule, rule.Name)
   }  


   }


   return nil
}

At the same time, we add the mapping rule of the rule: in sqle/driver/mysql/rule/rule.go

 {
   Rule: driver.Rule{
      Name:     DDLCheckAuditColumn,
      Desc:     "建表语句必须包含审计列(cdate,creator,edate,editor)",
      Level:    driver.RuleLevelError,
      Category: RuleTypeUsageSuggestion,
   },
   Message:      "建表语句必须包含审计列(cdate,creator,edate,editor)",
   AllowOffline: true,
   Func:         checkAuditColumn,
},

Add constant definition: sqle/driver/mysql/rule/rule.go

 DDLCheckAuditColumn                         = "ddl_check_audit_column"

We can also unit test in sqle/driver/mysql/audit_offline_test.go:

 func TestCheckAuditColumn(t *testing.T) {
   fmt.Println("start..............")
   runSingleRuleInspectCase(rulepkg.RuleHandlerMap[rulepkg.DDLCheckAuditColumn].Rule, t,
      "create table test (id int not null, name varchar(20));  ",
      DefaultMysqlInspectOffline(),
      `create table test (id int not null,
                                name varchar(20),
                                cdate datetime,
                                edate datetime,
                                creator1 varchar(20),
                                editor1 varchar(20));`,
      newTestResult().addResult(rulepkg.DDLCheckAuditColumn),
   )
}

After the final test is passed, we can submit the code and publish a new program to verify our own defined rules from the page.

In general, Aikesen's code is very standardized, which is convenient for secondary code development and maintenance. If you are a development enthusiast of golang, it can also be used as a classic case for learning projects.