Run Normal Data Changes Safely via DMS Tickets - Data Management

In Data Management (DMS), making a data change without guardrails is risky—a DELETE in production can go wrong, and a mistaken TRUNCATE cannot be rolled back. DMS addresses this with security rules that govern which SQL statements run directly in the SQLConsole, which require ticket approval, and which are blocked entirely. This topic walks through four common scenarios in Security Collaboration mode so you can configure the right governance policy for each environment.

How it works

Every data change in DMS follows this lifecycle:

Stage	Description
Write SQL	Enter SQL statements in the SQLConsole or attach a `.sql`, `.txt`, or `.zip` file in a ticket.
Precheck	DMS automatically validates the SQL against your security rules before execution.
Approval	If the security rules require a ticket, the change goes to the designated approvers (default: DBAs).
Execute	Run the change immediately or schedule it. Optionally wrap data manipulation language (DML) statements in a single transaction.
Backup and rollback	DMS can generate backup statements for `UPDATE` and `DELETE` operations so you can roll back if needed.

The behavior at each stage is governed by security rules—configurable policies attached to each database instance.

Prerequisites

Before you begin, make sure you have:

A DMS administrator account, or the permissions to submit data change tickets
The poc_prod and poc_dev database instances registered in DMS under Security Collaboration mode
The data_modify table created in both databases

Create the `data_modify` table

In the four examples in this topic, the schema design feature of DMS was used to create the data_modify table. You can also run this statement directly in the SQLConsole—DDL statements in development databases do not require tickets to execute.

CREATE TABLE `data_modify` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
 `name` varchar(256) NOT NULL COMMENT 'Name',
 `phone` varchar(32) DEFAULT NULL COMMENT 'Phone number',
 `sex` varchar(32) DEFAULT NULL COMMENT 'Gender',
 `email` varchar(256) DEFAULT NULL COMMENT 'Email address',
 `remarks` varchar(1024) DEFAULT NULL COMMENT 'Remarks',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Personal information';

The security rule sets used in all four scenarios:

Database instance	Security rule set
`poc_dev`	Security Rules for POC Development Databases
`poc_prod`	Security Rules for POC Production Databases

Submit a ticket for a regular data change

This scenario inserts rows into the data_modify table in poc_prod by submitting a data change ticket.

Log on to the DMS console V5.0DMS console V5.0.
Move the pointer over the icon in the upper-left corner and choose All Features > Database Development > Data Change > Normal Data Modify.
In normal mode, choose Database Development > Data Change > Normal Data Modify in the top navigation bar.
Fill in the ticket parameters. Key parameters:
Parameter Description
SQL text The SQL statement to execute. Separate multiple statements with semicolons (;).
Attachment Upload a .txt, .zip, or .sql file. Maximum file size: 15 MB.
Click Submit. DMS runs a precheck automatically. If the precheck fails, update the SQL as prompted and resubmit.
After the precheck passes, click Submit for Approval, then click OK in the confirmation prompt.
Data change tickets are approved by DBAs by default. To change the default approval template, see the Change the default approval template section in the SQL Correct topic.

Parameter	Description
SQL text	The SQL statement to execute. Separate multiple statements with semicolons (`;`).
Attachment	Upload a `.txt`, `.zip`, or `.sql` file. Maximum file size: 15 MB.

After the ticket is approved, click Execute Change. In the Task Settings dialog box, configure the execution parameters and click Confirm Execution.

If you set Execution Method to After Audit Approved, Auto Execute during the apply step, this step is skipped automatically. After a suspended task is resumed, execution continues from where it left off.

Parameter	Description
Execution strategy	Running immediately (default): executes as soon as the ticket is confirmed. Schedule: executes at the specified time.
Enable Submit as Single Transaction	Default: off. On: if any DML statement fails, all DML statements in the transaction are rolled back (DDL statements cannot be rolled back). Off: each statement executes independently; execution stops on failure but prior statements are not rolled back.
Enable Backup	Default: on. DMS generates backup statements for `UPDATE` and `DELETE` statements. See Backup support for database-specific details.

Optional. After execution completes, open the SQLConsole for poc_prod and verify the results.

Backup support

Backup applies only to UPDATE and DELETE statements. INSERT and DDL statements are not backed up.

Database type	Backup statement generated	Notes
MySQL, MariaDB	`REPLACE INTO`	Includes ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, and self-managed MySQL
All other supported databases	`INSERT`	—
MongoDB, Redis	Not supported	—

Allow DML statements to run directly in the development database

By default, DMS security rules require all DML statements to go through tickets. For development databases, this slows down iteration. Configure the security rules to let developers run DML statements directly in the SQLConsole—without submitting a ticket each time.

Step 1: Update the security rules for poc_dev

Log on to the DMS console V5.0DMS console V5.0 as a DMS administrator.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.
In normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.
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.
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.
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 page, find the POC development database rules and click Edit in the Actions column.
In the left-side navigation pane, click SQL Correct. Set Checkpoints to SQL execution rules.
Enable All DML can execute directly in SQLConsole and disable All DML must execute by ticket.

Step 2: Verify the rule change

In the upper-left corner of the DMS console, search for the poc_dev instance.

On the SQLConsole tab, run the following INSERT statements:

INSERT INTO data_modify (name, phone, sex) VALUES ('dms_a', '19000001','Male');
INSERT INTO data_modify (name, phone, sex) VALUES ('dms_b', '19000002','Female');
INSERT INTO data_modify (name, phone, sex) VALUES ('dms_c', '19000003','Male');

In the Execution Confirmation dialog box, click Execute. If Executed appears in the result, the rule change is working correctly.

Require ticket approval for high-risk SQL in the production database

Some SQL statements—like DELETE—carry significant risk in production. Configure security rules to flag DELETE as high-risk and route it through a dedicated approval process.

Step 1: Create an approval process

Log on to the DMS consoleDMS console as a DMS administrator.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Approval Processes.
In normal mode, choose Security and disaster recovery (DBS) > Approval Processes in the top navigation bar.
Click Create Approval Template and configure the approval nodes. Add nodes in ascending order—node 1 is the first approver, node 2 is the second, and so on.
Click Submit.

Step 2: Configure a risk identification rule

Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.
In normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.
On the Security Rules page, find the POC production database rules and click Edit in the Actions column.
Click SQL Correct in the left-side navigation pane. Set Checkpoints to Risk Identification Rules and click Create Rule.
In the Create Rule - SQL Correct dialog box, set the following parameters:
Parameter Value
Rule name Production environments. The execution of DELETE statements is a high-risk operation
Rule DSL See the DSL code below
```
if
    @fac.env_type in ['product','pre']
    and
    @fac.sql_type in
    [ 'DELETE']
then
    @act.mark_risk 'high' 'High-risk SQL statements: DELETE in the production environments'
end
```
Click Submit.
Find the Production environments. The execution of DELETE statements is a high-risk operation rule and click Enable in the Actions column. Click OK in the confirmation message. The rule takes effect immediately—all DELETE statements are identified as high-risk operations.

Parameter	Value
Rule name	`Production environments. The execution of DELETE statements is a high-risk operation`
Rule DSL	See the DSL code below

Step 3: Link the approval process to the risk approval rule

Set Checkpoints to Risk Approval Rules. Find the High risk approval process rule and click Edit in the Actions column.
In the Change Rule - SQL Correct dialog box, replace the template ID in the Rule DSL field with the ID of the approval template you created in Step 1. Click Submit.
Find the High risk approval process rule and click Enable. Click OK to confirm.

Step 4: Verify the rule

In the DMS console, search for the poc_prod instance.
On the SQLConsole tab, run the following statement:
```
DELETE FROM data_modify WHERE id = 1;
```
If the Execution History area shows a failure message due to security rules, the rule is working. Click Apply for Data Change to submit a ticket and go through the approval process.
Executing DELETE in a production database is a high-risk operation. The ticket must be approved by the approver specified in the approval template before it can be executed.

Block TRUNCATE statements in the production database

TRUNCATE clears all rows from a table and cannot be rolled back. To prevent accidental data loss in production, configure a security rule that blocks TRUNCATE statements—both in the SQLConsole and through tickets.

Step 1: Modify the security rule to block TRUNCATE

Log on to the DMS consoleDMS console as a DMS administrator.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.
In normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.
On the Security Rules page, find the POC production database rules and click Edit in the Actions column.
In the left-side navigation pane, click SQL Correct. Set Checkpoints to SQL execution rules.
Find the Allow TRUNCATE to be executed directly in the SQL console rule and click Edit in the Actions column.
Rename the rule to Forbid TRUNCATE statements. Replace the existing domain-specific language (DSL) with the following and click Submit:
This DSL blocks TRUNCATE in both the SQLConsole and through tickets. For more information about DSL syntax, see DSL syntax for security rules.
```
if
    @fac.sql_type in
      ['TRUNCATE']
then
    @act.forbid_execute
end
```
Find the TRUNCATE cannot be executed directly in the SQL console. It must be executed as a ticket. rule and click Enable in the Actions column. Click OK to confirm.

Step 2: Verify the rule in the SQLConsole

In the DMS console, search for the poc_prod instance.
On the SQLConsole tab, run the following statement:
```
TRUNCATE TABLE `data_modify`;
```
Execution Failed is displayed. The security rule is blocking the TRUNCATE statement.

Step 3: Verify the rule for ticket submissions

Submit a data change ticket with the following SQL statement (see Submit a ticket for a regular data change):

TRUNCATE TABLE `data_modify`;

After you submit the ticket, check the precheck results. If the precheck fails, the rule is also blocking TRUNCATE through the ticket workflow.

What's next

DSL syntax for security rules — write custom rules for your own SQL governance policies
Manage security rules — configure, enable, and disable rules across database instances
Schema design — create and manage table schemas without submitting DDL tickets