SQL Audit | How to Quickly Audit Various Types of Databases with SQLE

Author: Sun Jian

Sun Jian, R&D engineer of Aikesheng, responsible for SQLE related development;

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.

---

foreword

In recent years, the database industry has developed rapidly, various emerging databases have sprung up, and the technology stacks of various companies are no longer limited to a certain database. For the SQL quality management platform, it only supports a certain type of database (such as MySQL), so there will be certain limitations. SQLE considers supporting multiple databases at the beginning of the design, so when the product is designed, the process (business) will be reviewed. The code of the specific SQL audit is separated from the code of the specific SQL audit, and the SQL audit is implemented in the form of a plug-in. SQLE provides external interfaces and libraries required for plug-in development, and can quickly create and open an audit plug-in without upgrading the software. By importing the audit plug-in, you can obtain the audit online capability of the corresponding database type and use all the functions of the platform.

Plugin development reference documentation: https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_auditplugin/auditplugin_development.html ,

Reference documentation for the use of plugins: https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_auditplugin/auditplugin_management.html .

This article will demonstrate how to create a simple and usable audit plugin from scratch, as a case study.

Target

First, we will create a Postgres database audit plug-in, and add two rules, "prohibit the use of SELECT *" and "create too many table fields", and combine SQLE in the development process to perform SQL audit on the Postgres database. Online work order test demonstration . The demo code in the following procedure can be downloaded from here https://github.com/actiontech/sqle/tree/main/example/db_plugin .

Practical

Tip: SQLE and plug-ins are developed in GO language. If you want to develop plug-ins, you need to have a little understanding of GO.

1. Create a plugin project

First initialize a go project with go mod, then

 mkdir sqle-pg-plugin
cd sqle-pg-plugin
touch main.go
go mod init sqle-pg-plugin # 初始化go mod 
export GOPROXY=goproxy.cn,goproxy.io,direct # 设置 GoProxy，解决SQLE库下载问题，通过IDEA开发的可以在IDEA软件上设置；
go get github.com/actiontech/sqle@v1.2204.0 # 此版本为该文章编辑时的最新版本。

2. Write minimal plugin code

Write the following code in the main.go file of the project to add a Postgres database audit plug-in as quickly as possible. At this time, the plug-in has no audit rules.

 package main
 
import (
   adaptor "github.com/actiontech/sqle/sqle/pkg/driver"
)
 
func main() {
   plugin := adaptor.NewAdaptor(&adaptor.PostgresDialector{})
   plugin.Serve()
}

After compiling with 'go build', the binary file sqle-pg-plugin is obtained. According to the reference documentation for the use of the plug-in in the preface, we deploy it to the SQLE service. The data source can be added normally, as shown in the following figure:

At this time, the SQL audit online work order is created and launched normally, as shown in the following figure:

3. Add a rule to the plugin

On the basis of the code just now, we add the following code in the main function to add a new rule "prohibit the use of SELECT *", the complete code is as follows.

 package main
 
import (
   "context"
   "strings"
 
   "github.com/actiontech/sqle/sqle/driver"
   adaptor "github.com/actiontech/sqle/sqle/pkg/driver"
 
)
 
func main() {
   plugin := adaptor.NewAdaptor(&adaptor.PostgresDialector{})
   rule1 := &driver.Rule{
      Name:     "pg_rule_1", // 规则ID，该值会与插件类型一起作为这条规则在 SQLE 的唯一标识
      Desc:     "禁止使用 SELECT *",      // 规则描述
      Category: "DQL规范",           // 规则分类，用于分组，相同类型的规则会在 SQLE 的页面上展示在一起
      Level:    driver.RuleLevelError,    // 规则等级，表示该规则的严重程度
   }
    //
   rule1Handler := func(ctx context.Context, rule *driver.Rule, sql string) (string, error) {
      if strings.Contains(sql, "select *") {
         return rule.Desc, nil
      }
      return "", nil
   }
   plugin.AddRule(rule1, rule1Handler)
   plugin.Serve()
}

We compile the plugin binary file in the previous way and deploy it to the SQLE server. You can see that a new rule has been added, as shown in the following figure:

At this point, we submit a work order to verify, and we can see that the rule we just added is triggered

4. Add a configurable complex rule to the plugin

Based on the above code, we add a rule "too many table fields created", which has the following characteristics:

• The rules added above are based on string matching, which is not accurate and cannot match different writing formats, such as case, newline, etc. Therefore, we will develop a rule based on the SQL parser here to test the parsing library used https://github.com/pganalyze/pg_query_go ;
• In order to increase the applicability of the rules, we are going to add a dynamic configuration to the rules to provide users with options.

code show as below:

 package main
 
import (
   "context"
   "fmt"
   "strings"
 
   "github.com/actiontech/sqle/sqle/driver"
   adaptor "github.com/actiontech/sqle/sqle/pkg/driver"
   "github.com/actiontech/sqle/sqle/pkg/params"
   parser "github.com/pganalyze/pg_query_go/v2"
)
 
func main() {
   plugin := adaptor.NewAdaptor(&adaptor.PostgresDialector{})
 
   rule1 := &driver.Rule{
      Name:     "pg_rule_1",           // 规则ID，该值会与插件类型一起作为这条规则在 SQLE 的唯一标识
      Desc:     "避免查询所有的列",            // 规则描述
      Category: "DQL规范",               // 规则分类，用于分组，相同类型的规则会在 SQLE 的页面上展示在一起
      Level:    driver.RuleLevelError, // 规则等级，表示该规则的严重程度
   }
   rule1Handler := func(ctx context.Context, rule *driver.Rule, sql string) (string, error) {
      if strings.Contains(sql, "select *") {
         return rule.Desc, nil
      }
      return "", nil
   }
 
   // 定义第二条规则
   rule2 := &driver.Rule{
      Name:     "pg_rule_2",
      Desc:     "表字段不建议过多",
      Level:    driver.RuleLevelWarn,
      Category: "DDL规范",
      Params: []*params.Param{ // 自定义参数列表
         &params.Param{
            Key:   "max_column_count",  // 自定义参数的ID
            Value: "50",                // 自定义参数的默认值
            Desc:  "最大字段个数",            // 自定义参数在页面上的描述
            Type:  params.ParamTypeInt, // 自定义参数的值类型
         },
      },
   }
 
   // 这时处理函数的参数是 interface{} 类型，需要将其断言成 AST 语法树。
   rule2Handler := func(ctx context.Context, rule *driver.Rule, ast interface{}) (string, error) {
      node, ok := ast.(*parser.RawStmt)
      if !ok {
         return "", nil
      }
      switch stmt := node.GetStmt().GetNode().(type) {
      case *parser.Node_CreateStmt:
         columnCounter := 0
         for _, elt := range stmt.CreateStmt.TableElts {
            switch elt.GetNode().(type) {
            case *parser.Node_ColumnDef:
               columnCounter++
            }
         }
         // 读取 SQLE 传递过来的该参数配置的值
         count := rule.Params.GetParam("max_column_count").Int()
         if count > 0 && columnCounter > count {
            return fmt.Sprintf("表字段不建议超过%d个，目前有%d个", count, columnCounter), nil
         }
      }
      return "", nil
   }
 
   plugin.AddRule(rule1, rule1Handler)
   plugin.AddRuleWithSQLParser(rule2, rule2Handler)
 
   // 需要将 SQL 解析的方法注册到插件中。
   plugin.Serve(adaptor.WithSQLParser(func(sql string) (ast interface{}, err error) {
      // parser.Parse 使用 PostgreSQL 的解析器，将 sql 解析成 AST 语法树。
      result, err := parser.Parse(sql)
      if err != nil {
         return nil, fmt.Errorf("parse sql error")
      }
      if len(result.Stmts) != 1 {
         return nil, fmt.Errorf("unexpected statement count: %d", len(result.Stmts))
      }
      // 将 SQL 的语法树返回。
      return result.Stmts[0], nil
   }))
 
   plugin.Serve()
}

Open the SQLE rule interface, you can see that the rule has been added to SQLE, as shown in the figure:

Let's make the value of the rule in the rule template smaller and test it

First, we submit a table creation statement with more than 5 fields. At this time, SQLE will trigger the rule and give the expected prompt information, as shown in the following figure:

Then we submit a table building statement with no more than 5 fields. At this time, SQLE will not trigger the rule, as shown in the following figure:

Summarize

Through the above demonstration, the simple development and testing process of the SQLE database audit plug-in is roughly introduced. You can follow similar steps to develop a set of rules that meet your company's needs, and combine it with the SQLE platform to meet daily use. We also provide some common database audit plug-ins, you can also develop on this basis, refer to the document: https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_auditplugin/overview.html