SQL Auditing | How to Automatically Audit Java Applications with SQLE

Authors: Sun Jian, Zhang Qiang

Sun Jian, R&D engineer of Aikesen, responsible for SQLE-related development; Zhang Qiang: member of Aikesen R&D Center, back-end R&D engineer, currently in charge of business development related to DMP product Redis.

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.

---

1. Introduction to SQLE

SQLE is a program initiated by the Aikesen open source community and aimed at database development and management personnel. It realizes the whole process coverage of SQL "development" - "testing" - "online", and refined management of resources and permissions, taking into account simplicity and efficiency. , an open source project that is easy to maintain and expand, and aims to provide users with a set of safe, reliable, self-controllable SQL quality control solutions.

In version 1.2202.0 released in February:

• The user system has been transformed, and user groups and operating permissions have been added;
• Added the function of scoring work order review results;
• Add audit tasks, support MySQL database table structure, Oracle's TopSQL and Java application SQL capture and audit;
• Added Db2 audit plugin to support Db2 audit work order and go online;
• Added 37 MySQL audit rules, such as checking whether DDL creates redundant indexes;
• Other experience optimizations and bug fixes.

Additional details can be found at: https://github.com/actiontech/sqle/releases/tag/v1.2202.0 .

2. Introduction to Java Application Audit

Considering that many users have deployed a large number of Java-based applications and services in actual production, and some have involved extremely important and uninterrupted core businesses. Starting with version 1.2202.0, SQLE supports SQL auditing for Java applications. And on the basis of completing the core functions, it supports zero-cost access to Java applications.

The Java auditing features of SQLE are as follows:

• Support non-intrusive business integration, users can directly run Java applications in the form of environment variables, directly access SQLE without modifying Java business code, reducing user access costs;
• Supports automatic collection and statistics of application SQL. Users can view the classification and statistics of SQL in the application through the statement pool page on SQLE, and quickly grasp the situation of SQL in the application;
• It supports manual or automatic auditing of SQL. Users can know whether the applied SQL conforms to the preset auditing rules in the audit report. Both developers and DBAs can understand the execution status of running business SQL in real time.

3. Effect display

1. Deploy the environment, the Java application to be accessed, and the corresponding database in advance, and add it as a data source. For demonstration, the Java project here is https://gitee.com/surveyking/surveyking ;
2. Create audit tasks for Java applications;

3. start the application;

SQLE_COLLECT_ENABLE=true \SQLE_HOST=XX.XX.XX.XX:10000 \
SQLE_TASK_NAME=surveryking_test \
SQLE_TASK_TOKEN=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJhcG4iOiJqd19hcHAiLCJleHAiOjE2NzcyMjYxNzcsIm5hbWUiOiJhZG1pbiJ9.3d0pA1hiVnFEWJokSFBwCT8d1pKOYV6SViENj4GFqgI \
java -jar surveyking-v0.3.0-beta.4.jar \
--server.port=1991 \
--spring.datasource.url=jdbc:mysql://XX.XX.XX.XX:3306/surveyking \
--spring.datasource.username=root \
--spring.datasource.password=xxxxxx \
& >>/opt/surveyking/std.log

4. View the SQLE audit task details interface, you can see the SQL that has been executed by the current application;

5. Viewing the audit report, the user can know whether the applied SQL conforms to the preset audit rules through the audit report of the audit task, so as to make adjustments in time.

6. Create a user on the sample Java application home page, and then you can observe the corresponding INSERT statements in the SQLE statement pool corresponding to the audit task.

7. At the same time, users can quickly perceive the statement classification and statistics of SQL executed in the application from the pages in the statement pool.

   

Combining the above example steps, Java applications can access SQLE at zero cost. Developers and DBAs can fully grasp the SQL audit results, execution status and statistical information in the application through functions such as "audit report" and "SQL statement pool for audit tasks", completing the process from "development" to "online". SQL quality control.

If you want to learn more about more functions and features of SQLE, please visit the following address:

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