SQL审核 | SQLE 如何开发一条自定义的规则

爱可生开源社区

发布于 2022-05-23 08:48:35

3040

发布于 2022-05-23 08:48:35

文章被收录于专栏：爱可生开源社区

作者：Jason

就职于捷信消费金融有限公司，担任 DBA 工作。先后从事过 oracle 、mongo 、mysql 的 DBA ，以及大数据 ETL 的开发工作。对 NEWSQL 以及云原生分布式数据库具有浓厚的兴趣爱好。

本文来源：原创投稿

* 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

SQLE 是由上海爱可生信息技术股份有限公司开发并开源，支持SQL审核、索引优化、事前审核、事后审核、支持标准化上线流程、原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。

官方主页 https://opensource.actionsky.com/sqle/

官方文档 Introduction · SQLE manual (actiontech.github.io)

(！！！事先声明：二次开发纯属于个人技术研究，不得进行任何商业盈利行为)

大家好！本次来分享的是如何开发一条自定义的review rule 的规则（基于 MySQL 5.7 的语法）。

在开发代码前，我们先从后台API调用的角度梳理一下审核SQL的具体流程：

我们可以先用 POSTMAN 从调用一遍代码审核这个方法：

首先，先确定规则验证的API接口：http://10.25.15.83:10000/v1/tasks/audits

与API相对应的源代码方法的入口是：api.controller.v1.task.go --> func CreateAndAuditTask(c echo.Context)

由于系统做了JWT的登录验证, 我们需要先模拟一下登录：

1）调用登录的API 10.25.15.83:10000/v1/login，获得token: ****************************

默认的是 admin/admin ,登录成功后，我们可拿到 token

2）我们调用一下规则验证的API接口：http://10.25.15.83:10000/v1/tasks/audits

我们准备了一下简单的SQL语句：

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

这个语句不符合审计的规则如下：1)无主键 2)无列和表级别的comment 3)无innodb engine 指定

我们首先设置参数：可以参考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"`
}

参数1：InstanceName -》 DBA 参数2：instance_schema =》 testdb 参数3：sql =》 create table test (id int not null, name varchar(20));

我们还需要设置一下 token: 这个token 从登录的API返回值可以获取到

下面我们尝试调用一下审计接口：http://10.25.15.83:10000/v1/tasks/audits

我们来看一下返回给我们的response的含义 :

{
   "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语句的方式
   }
}

我们要想知道具体的审计返回的结果需要查询数据库：（"task_id": 1, --返回的审核结果保存在 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)

从 response 的信息来看是完全符合我们之前的预期的结果。

下面我们要代码的角度追踪一下这个审计的整个流程：SQLE 采用的是类似数据库driver插件的开发模式，接口 driver.go 已经定义好了抽象的函数，具体的实现需要每一种具体的数据库来完成例如 mysql.go, oracle.go

下面是如何添加一条新的rule 到审核平台的具体步骤：

New Rule的规则是: 我们想要在新建的TABLE 表中必须含有指定的列，像是cdate cdate,creator,edate,editor 这几个审计字段。

首先找到对应的数据库里面rule的表是 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

....

我们来手动插入一条新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)

我们可以从页面看到这条新加的规则已经出现在了列表里面：

我们把这条新的规则追加到我们自定义的规则模板中：

最后一步，提交规则的模板信息。

下面我们从页面上测试一下我们新加的规则：（当然了，对应的规则要添加对应的后台代码，这个后面会有介绍~）

我们新建一个审核任务：

输入建表语句：

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

点击审核按钮

我们可以看到我们自定义的审核规则已经生效：

最后我们来看看后台代码是如何开发实现这个规则的：

后台对应的这条规则的逻辑代码是：（入门级别的简单代码）这段代码加入到 sqle/driver/mysql/rule/rule.go中用Map的结构体来判断，必要的列的名字是否存在review 语句的表中

//可以在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
}

同时我们添加rule的mapping 规则：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,
},

添加常量定义：sqle/driver/mysql/rule/rule.go中

DDLCheckAuditColumn                         = "ddl_check_audit_column"

我们还可以在 sqle/driver/mysql/audit_offline_test.go 进行unit testing:

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),
   )
}