SQL audit | SQLE secondary development environment construction

Author: Jason

Worked for Home Credit Consumer Finance Co., Ltd. as a DBA. He has been engaged in the development of oracle, mongo, mysql DBA, and 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)

Let's build the secondary development environment of SQLE: SQLE adopts the development mode of front-end and back-end separation.

Background use: restful API published by go

Front desk adopts: nodeJS + react

Let's look at the first part first:

Background use: restful API published by go

Prepare in advance: environment for go language, version 1.16

Development environment linux or mac os

It should be emphasized here that the following code in windows will not work: you will encounter the following errors

..\..\..\vendor\github.com\openark\golib\log\log.go:90:19: undefined: syslog.Writer
..\..\..\vendor\github.com\openark\golib\log\log.go:110:22: undefined: syslog.New
..\..\..\vendor\github.com\openark\golib\log\log.go:110:33: undefined: syslog.LOG_ERR

The explanation on the GO official website is as follows: The general meaning is that this syslog package is not implemented in the windows environment.

This package is not implemented on Windows. As the syslog package is frozen, Windows users are encouraged to use a package outside of the standard library. For background, see https://golang.org/issue/1108.
This package is not implemented on Plan 9.
This package is not implemented on NaCl (Native Client).

This time our development environment is to choose to build on the linux server.

As a first step, we first need to download the complete project from github. (Generally, scientific Internet access is required in China)

I downloaded it directly to the linux test environment

TEST mongo@wqdcsrv3066[16:03:03]:/data/jason $ git clone https://github.com/actiontech/sqle.git
Cloning into 'sqle'...
remote: Enumerating objects: 28097, done.
remote: Counting objects: 100% (2998/2998), done.
remote: Compressing objects: 100% (1124/1124), done.
Receiving objects:  45% (12786/28097), 34.29 MiB | 2.00 MiB/s

After the project is downloaded, we need to open the IDE on the linux system to import the project: I use GOland here.

Since the IDE is a graphical interface, we need to use the VNC viewer on the local windows to connect with the linux development machine:

After connecting to the server, execute the command to start Goland

Next, we need to import the project we downloaded earlier.

After importing, let's take a look at the project directory: The package main package is started in the two files: sqle/cmd/sqld/sqled.go and sqle/cmd/sqld/gen_secret_pass.go

Since the project startup needs to open the port of the web service and connect to the database, we can create a sqled.yml file ourselves

server:
  sqle_config:
    server_port: 10000
    #auto_migrate_table: 'AUTO_MIGRATE_TABLE'
    #debug_log: 'DEBUG'
    log_path: '/data/sqle-main/logs'
    #plugin_path: '/data/sqle-main/plugins'
  db_config:
    mysql_cnf:
      mysql_host: '10.67.200.37'
      mysql_port: 3306
      mysql_user: 'app_sqle'
      mysql_password: '*******'
      mysql_schema: 'sqle'

We need to create the corresponding database and account in MySQL:

[root@localhost][performance_schema]> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)
[root@localhost][performance_schema]> CREATE DATABASE IF NOT EXISTS sqle default character set utf8mb4 collate utf8mb4_unicode_ci;
Query OK, 1 row affected (3.00 sec)
[root@localhost][performance_schema]> create user app_sqle@'%' identified with mysql_native_password by "***********";
Query OK, 0 rows affected (0.01 sec)
[root@localhost][performance_schema]> grant all on sqle.* to app_sqle@'%';
Query OK, 0 rows affected (0.00 sec

Next we try to start the project:

The package main package that is started is the two files in the path: sqle/cmd/sqld/sqled.go and sqle/cmd/sqld/gen_secret_pass.go

We need to specify our configuration file: --config /data/sqle-main/sqle/sqled.yml

Let's try go bulild this project:

Check out the console output:

We finally use postman to test the login API: 10.67.200.38:10000/v1/login

So far, our background environment has been built, and the next step is to build the foreground environment:

Front desk adopts: nodeJS + react

Again, since windows system does not support nodejs very well, it is recommended to use MAC OS or Linux system for development.

We use this time to install vs code on windows to debug on Linux remotely.

First, let's install the node js environment on Linux OS:

Download nodejs: http://nodejs.cn/download/

After the download is complete, upload it to the linux server and decompress it and install it as follows:

TEST mongo@wqdcsrv3066[15:24:08]:/data/jason/nodejs $ xz -d
node-v16.14.0-linux-x64.tar.xz
TEST mongo@wqdcsrv3066[15:25:38]:/data/jason/nodejs $ tar
-xvf  node-v16.14.0-linux-x64.tar
TEST mongo@wqdcsrv3066[15:27:11]:/data/jason/nodejs $ mv
node-v16.14.0-linux-x64 node
  
TEST mongo@wqdcsrv3066[15:28:19]:/data/jason/nodejs/node $
sudo ln -s /data/jason/nodejs/node/bin/node /usr/bin/node
TEST mongo@wqdcsrv3066[15:28:27]:/data/jason/nodejs/node $
sudo ln -s /data/jason/nodejs/node/bin/npm /usr/bin/npm
TEST mongo@wqdcsrv3066[15:29:03]:/data/jason/nodejs/node $
mkdir node_global
TEST mongo@wqdcsrv3066[15:29:10]:/data/jason/nodejs/node $
mkdir node_cache
TEST mongo@wqdcsrv3066[15:29:15]:/data/jason/nodejs/node $
npm config set prefix "node_global"
TEST mongo@wqdcsrv3066[15:29:21]:/data/jason/nodejs/node $
npm config set cache "node_cache"
TEST mongo@wqdcsrv3066[15:29:33]:/data/jason/nodejs/node $
node --version
v16.14.0

We need to install the YARN package to compile the code and use it for build:

TEST mongo@wqdcsrv3066[16:52:45]:/home/mongo $ npm install -g yarn
changed 1 package, and audited 2 packages in 2s
found 0 vulnerabilities

At the same time, we also need to configure a soft connection for the YARN command:

TEST mongo@wqdcsrv3066[17:14:00]:/home/mongo $ sudo ln -s /data/jason/nodejs/node/node_global/bin/yarn /usr/bin/yarn
TEST mongo@wqdcsrv3066[17:16:47]:/home/mongo $ yarn version
yarn version v1.22.17
...
...

So far, we have configured the nodeJS environment on linux. Next, we need to download the code of the SQLE UI library from github: https://github.com/actiontech/sqle-ui

TEST mongo@wqdcsrv3066[21:38:21]:/data/jason $ git clone
https://github.com/actiontech/sqle-ui.git
Cloning into 'sqle-ui'...
remote: Enumerating objects: 4982, done.
remote: Counting objects: 100% (4982/4982), done.
remote: Compressing objects: 100% (1998/1998), done.
remote: Total 4982 (delta 2809), reused 4648 (delta 2505),
pack-reused 0
Receiving objects: 100% (4982/4982), 2.01 MiB | 89.00 KiB/s,
done.
Resolving deltas: 100% (2809/2809), done.

Next, let's configure the settings for remote connection to linux in the IDE vscode on windows

Install the Remote Development plugin for remote connections

After the installation is complete, the icon will also be generated.

Configure the server information for remote login:

You need to enter a password to connect to the server:

We can see that the project has been imported successfully:

So far, we have configured the development environment, the next step we try to compile and start the project:

Of course, we also need to modify the API release address of the background service: we find the file package.json

There is also the file craco.config.js

Our next step is to download the packages required by the project:

TEST mongo@wqdcsrv3066[21:26:16]:/data/jason/sqle-ui-new/sqle-ui $ yarn install
yarn install v1.22.17
[1/5] Validating package.json...
[2/5] Resolving packages...
[3/5] Fetching packages...
[4/5] Linking dependencies...
warning " > @testing-library/user-event@12.8.3" has unmet peer dependency "@testing-library/dom@>=7.21.4".
warning "craco-less > less-loader@7.3.0" has unmet peer dependency "webpack@^4.0.0 || ^5.0.0".
warning " > monaco-editor-webpack-plugin@3.1.0" has unmet peer dependency "webpack@^4.5.0 || 5.x".
warning " > monaco-editor-webpack-plugin@3.1.0" has unmet peer dependency "monaco-editor@0.22.x || 0.23.x || 0.24.x".
warning " > react-monaco-editor@0.43.0" has incorrect peer dependency "@types/react@^17.x".
[5/5] Building fresh packages...
Done in 61.65s.

Then we need to compile the code:

$ node ./scripts/moveAntdCss.js && node ./scripts/getGitVersion.js
$ craco build
current mode: ce
Creating an optimized production build...
Browserslist: caniuse-lite is outdated. Please run:
npx browserslist@latest --update-db
Why you should do it regularly:
https://github.com/browserslist/browserslist#browsers-data-updating
Compiled successfully.
File sizes after gzip:
  588.6 KB   build/static/js/OrderDetail.4a59ec36.chunk.js
  580.25 KB  build/static/js/CreateOrder.8026b0aa.chunk.js
  532.43 KB  build/static/js/22.c21d2951.chunk.js
  61.76 KB   build/static/js/DataSource.829fc664.chunk.js
  59.74 KB   build/static/js/AuditPlan.9f3f1b23.chunk.js
  57.64 KB   build/static/js/Order.003be5b1.chunk.js
  47.66 KB   build/static/js/User.9fe363ac.chunk.js
  46.36 KB   build/static/js/UserGroup.044f6248.chunk.js
  45.92 KB   build/editor.worker.js
  44.41 KB   build/static/js/RuleTemplate.86cc7bbb.chunk.js
  39.9 KB    build/static/js/Role.039a086a.chunk.js
  29.54 KB   build/static/js/PlanDetail.90341552.chunk.js
  20.63 KB   build/static/js/main.21b0188c.chunk.js
  13.98 KB   build/static/js/3.1aa16012.chunk.js
  13.98 KB   build/static/js/Account.3465705d.chunk.js
  13.23 KB   build/static/js/1.1bb8d820.chunk.js
  10.94 KB   build/static/js/System.9ea63f78.chunk.js
  9.32 KB    build/static/css/CreateOrder.90ee172f.chunk.css
  9.32 KB    build/static/css/OrderDetail.90ee172f.chunk.css
  8.65 KB    build/static/js/4.dfda53f3.chunk.js
  6.44 KB    build/static/js/23.d9ad2202.chunk.js
  5.89 KB    build/static/js/Login.19923fb4.chunk.js
  5.86 KB    build/static/js/Rule.828dfc27.chunk.js
  5.51 KB    build/static/js/0.d9a06cf9.chunk.js
  5.13 KB    build/static/js/2.68214f76.chunk.js
  4.87 KB    build/static/js/Home.aa89add8.chunk.js
  1.88 KB    build/static/js/runtime-main.e8405fb1.js
  1.4 KB     build/static/js/24.6c710535.chunk.js
  982 B      build/static/css/22.b58f2af8.chunk.css
  819 B      build/static/css/main.cdb4f5a9.chunk.css
  493 B      build/static/css/Login.141bcc4c.chunk.css
  313 B      build/static/css/AuditPlan.f5209654.chunk.css
  250 B      build/static/css/RuleTemplate.770a3f55.chunk.css
  139 B      build/static/css/PlanDetail.b50384ea.chunk.css
  96 B       build/static/css/Rule.864c09ef.chunk.css
The project was built assuming it is hosted at /.
You can control this with the homepage field in your package.json.
The build folder is ready to be deployed.
You may serve it with a static server:
  yarn global add serve
  serve -s build
Find out more about deployment here:
  https://cra.link/deployment
Done in 79.65s.

After the code is compiled, we try to start the project in the IDE: execute yarn run start

Try browser access to SQLE: http://10.67.200.38:3000

Try to log in to admin/admin

At this point, the front desk development environment is completed!

In general, this development method with front-end and back-end separation requires relatively high technical stacks and comprehensive capabilities of developers.

In addition, professional knowledge in the database field must be added, which cannot be completed by one or two people.

Finally, I would like to thank Exxon for open-sourced its own code review system!

---

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.

method of obtaining:

| Type| Address|
| --- | --- |
| Repository | https://github.com/actiontech/sqle |
| Documentation | https://actiontech.github.io/sqle-docs-cn/ |
| Releases | https://github.com/actiontech/sqle/releases |

For more technical issues related to SQLE, you can scan the code or directly join the official QQ technical exchange group (637150065) for communication. Welcome to "harassment"...