Change the approval template for SQL Correct - Data Management

Prerequisites

Before you begin, ensure that you have one of the following roles:

DMS administrator
Database administrator (DBA)
Security administrator

How it works

When a data change ticket is submitted, DMS evaluates it against the security rules configured on the SQL Correct tab. You can specify risk levels for data changes based on your business requirements, and then specify different approval processes for tickets at different risk levels — from a strict approval process that controls execution of all SQL statements to a loose process that allows execution without approval.

Conflict resolution

When two conflicting rules are both enabled, the stricter rule takes effect. For example, if "All DML can execute directly in SQL Console" and "All DML must execute by ticket" are both enabled, the second rule prevails.

Checkpoints

Basic Configuration Item

This checkpoint provides five default rules that serve as system-wide defaults.

Rule	Description
Data change default approval Template	The default approval template for data change tickets. Default template ID: 853; default approver: the DBA of the database involved.
Data Change risk level list	Defines the available risk levels used by Risk Identification Rules and Risk Approval Rules. Default levels: LOW (low risk), MIDDLE (medium risk), HIGH (high risk), HIGHEST (major risk).
Whether data import supports selecting speed mode	Specifies whether data can be imported in speed mode. For details, see Import data.
Skip Validation on Affected Rows for Regular Data Change	When enabled, the precheck skips row-count verification for data change tickets.
The Normal Data Modify ticket cannot be submitted if the specified number of affected rows is inconsistent with the result of the row count verification.	When enabled, tickets cannot be submitted if the specified affected-row count differs from the precheck result.

SQL execution rules

SQL execution rules control which SQL statements can run directly in SQL Console versus requiring a ticket.

Example: Allow UPDATE, DELETE, and INSERT to run directly only in non-production environments:

if
  @fac.env_type not in ['product']
  and
  @fac.sql_type in ['UPDATE','DELETE','INSERT']
then
  @act.allow_submit
end

Risk Identification Rules

Risk Identification Rules classify each data change with a risk level. When multiple rules match the same change, the highest risk level applies.

Example: Mark data changes in non-production, non-pre-release environments as low risk:

if
  @fac.env_type not in ['product','pre']
then
  @act.mark_risk 'low' 'Low risk level: offline environment'
end

Risk Approval Rules

Risk Approval Rules map risk levels to approval processes. If a data change is not matched by any Risk Identification Rule, DMS uses the approval template defined in the Data change default approval Template rule under Basic Configuration Item.

Example default behavior: Offline environments (identified as low risk) require no approval.

Batch Data import rules

These rules apply only to data import tickets and are evaluated separately from the data change workflow.

Example rule: Allow bulk import of INSERT statements.

Factors and actions

Rules are written using factors (@fac.*) and actions (@act.*). Factors represent context variables — such as environment type, SQL type, or number of affected rows. Actions define what happens when the rule conditions are met.

Factors

Table 1. Factors provided on the SQL Correct tab

Factor	Type	Description
`@fac.env_type`	String	The environment type of the database, such as `DEV` or `PRODUCT`. See Change the environment type of an instance.
`@fac.sql_type`	String	The SQL statement subcategory, such as `UPDATE` or `INSERT`. See SQL Console for relational databases for the full list of subcategories.
`@fac.detail_type`	String	The type of the data change ticket: `COMMON` (Normal Data Modify), `CHUNK_DML` (Lockless change), `PROCEDURE` (Programmable Object), `CRON_CLEAR_DATA` (History Data Clean), `BIG_FILE` (Large Data Import).
`@fac.is_logic`	Boolean	Whether the target database is a logical database.
`@fac.is_ignore_affect_rows`	Boolean	Whether the row-count verification is skipped.
`@fac.insert_rows`	Number	The number of rows to be inserted.
`@fac.update_delete_rows`	Number	The number of rows to be updated or deleted.
`@fac.max_alter_table_size`	Number	The size of the largest tablespace containing the table to be modified.
`@fac.is_has_security_column`	Boolean	Whether the SQL statement involves sensitive fields.
`@fac.security_column_list`	List	The sensitive fields involved in the SQL statement.
`@fac.risk_level`	String	The risk level assigned to the data change.
`@fac.risk_reason`	String	The reason the data change was assigned this risk level.
`@fac.table_name_list`	List	The names of tables involved in the data change.
`@fac.extra_info`	String	Additional ticket information. Not currently in use.

Example: Mark changes to order tables as high risk:

if
  'tb_order' in @fac.table_name_list or 'tb1_order' in @fac.table_name_list
then
  @act.mark_risk 'high' 'High-level risk: involving order table'
end

Actions

Table 2. Actions provided on the SQL Correct tab

Action	Description
`@act.allow_submit`	Allows the SQL statement to be submitted in a ticket.
`@act.allow_execute_direct`	Allows the SQL statement to run directly in SQL Console.
`@act.forbid_execute`	Blocks execution of the SQL statement.
`@act.mark_risk`	Assigns a risk level to the data change. Format: `@act.mark_risk 'middle' 'Medium-level risk: online environment'`.
`@act.do_not_approve`	Specifies the ID of an approval template. For details, see Configure approval processes.
`@act.choose_approve_template`	—
`@act.choose_approve_template_with_reason`	—

Change the default approval template

Log on to the DMS console V5.0.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.

Note
If you use the DMS console in normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.
On the Security Rules tab, find the security rule set and click Edit in the Actions column.
On the Details page, click the SQL Correct tab on the left.
Select Basic Configuration Item for the Checkpoints parameter.
Find the Data change default approval Template rule and click Edit in the Actions column.
In the Change Configuration Item dialog box, click Switch Approval Template.
In the Switch Approval Template dialog box, find the template and click Select in the Actions column.

To remove the approval requirement entirely, click Reset to Free of Approval.
Click Submit.