Background information
After a system administrator grants a user permission to manage development rules, the user can manage SQL check rules in an environment under Security Rules.
When a user executes SQL statements in the SQL window, creates a database change, or creates a table, OceanBase Developer Center (ODC) checks the SQL statements against the configured SQL check rules.
This document describes how to edit and use SQL check rules.
Concepts
Rule level: The severity of an SQL check rule. Rule levels are divided into the following three categories:
Compliant: An SQL statement that hits this rule is compliant and requires no improvement.
Suggestion: An SQL statement that hits this rule has a minor issue. You can choose to improve it. ODC provides a suggestion for improvement.
Must Improve: An SQL statement that hits this rule has a critical issue. If you execute the statement without improvement, it may affect data or business security. You must improve the statement before you can execute it.
Rule status: The state of an SQL check rule. If a rule is enabled, ODC uses it to check SQL statements when a user submits a ticket or executes SQL. If a rule is disabled, ODC ignores it.
Rule type: The type of database object to which the rule applies. The following rule types are available:
TABLE: The rule applies to table objects.
SELECT: The rule applies to query statements.
INSERT: The rule applies to insert statements.
UPDATE: The rule applies to update statements.
DELETE: The rule applies to delete statements.
DDL: The rule checks DDL statements.
DML: The rule checks DML statements.
DQL: The rule checks DQL statements.
INDEX: The rule checks INDEX creation statements.
Execution flow
A user with permission to manage development rules edits and enables SQL check rules in an environment.
When an ODC user executes SQL statements in the command line window or anonymous block window, or as part of a database change, ODC initiates an SQL check.
If the SQL statements are compliant, ODC executes them. Otherwise, ODC blocks the statements.
Prerequisites
A user with operation permissions for developer specifications.
Procedure
Example: In the Test environment for SQL check rules, edit and enable the Columns must have comments rule. Then, edit and execute a CREATE TABLE statement to trigger an SQL check.
Item |
Example value |
Project name |
odc_4.2.0 |
Data source |
mysql_4.2.0 |
Database name |
odc_test |
Table name |
employee |
Log on to the ODC web client. In the project collaboration window, click Security Rules > Environment > Test > SQL Check Rules.
As shown in the preceding figure, on the SQL Check Rules tab, edit the Columns must have comments rule.
Click Submit to save the rule.
On the SQL Check Rules tab, enable the Columns must have comments rule.
In the project collaboration window, click Project > All Projects > odc_4.2.0.
Click the database, log on to it, and navigate to the SQL development window.
In the SQL window, edit the SQL statement to create the
employeetable. Then, click Run F8 to run all SQL statements in the current SQL window.
CREATE TABLE `odc_test`.`employee` ( `emp_no` int(120) NULL, `birthday` date NULL, `name` varchar(120) NULL ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci ;As shown in the preceding figure, the log displays the message No column comments.
