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

>作者：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语句： ```mysql create table test (id int not null, name varchar(20)); ``` 这个语句不符合审计的规则如下： 1)无主键 2)无列和表级别的comment 3)无innodb engine 指定 我们首先设置参数： 可以参考request的结构体。 ```mysql 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的含义 : ```mysql { "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 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 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 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) ``` 我们可以从页面看到这条新加的规则已经出现在了列表里面：  我们把这条新的规则追加到我们自定义的规则模板中：  最后一步，提交规则的模板信息。  下面我们从页面上测试一下我们新加的规则：（当然了，对应的规则要添加对应的后台代码，这个后面会有介绍~） 我们新建一个审核任务： 输入建表语句： ```mysql create table test (id int not null, name varchar(20)); ``` 点击审核按钮  我们可以看到我们自定义的审核规则已经生效：  最后我们来看看后台代码是如何开发实现这个规则的： 后台对应的这条规则的逻辑代码是：（入门级别的简单代码）这段代码加入到 sqle/driver/mysql/rule/rule.go中 用Map的结构体来判断，必要的列的名字是否存在review 语句的表中 ```mysql //可以在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中 ```mysql { 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中 ```mysql DDLCheckAuditColumn = "ddl_check_audit_column" ``` 我们还可以在 sqle/driver/mysql/audit_offline_test.go 进行unit testing: ```mysql 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), ) } ``` <br>  最后测试通过后，我们即可提交代码，发布新的程序来从页面上验证我们自己定义的规则了。 总体来说，爱可生的代码是十分规范的，便于二次代码开发和维护。如果你是golang的开发爱好者，亦可作为学习项目的经典案例。