You can change data by entering SQL statements on the SQLConsole tab or submitting a data change ticket in the Data Management (DMS) console. You can also create custom security rules to manage data change operations in databases that reside in different environments. This topic uses four examples to describe how to configure security rules and submit data change tickets in typical scenarios.
Usage notes
- The security rule set for the
POC_dev
instance isSecurity Rules for POC Development Databases
. - The security rule set for the
POC_prod
instance isSecurity Rules for POC Production Databases
.
Submit a ticket to change data
In this example, a ticket is submitted to execute INSERT
statements.
- Configure security rules. Note The default security rules require tickets to be submitted to execute DML statements. For more information, see Create and apply security rules.
- Execute
INSERT
statements.- Log on to the DMS console as a regular user.
- In the top navigation bar, choose SQL Console > SQL Console.
- In the Please select the database first dialog box, select the
poc_prod
database from the drop-down list or enter a keyword in the field to search for the database. Then, click Confirm. - On the SQLConsole tab, enter the following SQL statements in the SQL editor and click
Execute in the upper part of the SQL editor.
The statements insert three rows of data into the database.
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');
- Click Apply for Data Change.
Note The default security rules require data change tickets to be submitted to execute
INSERT
statements. Therefore, DMS prompts that you must submit a data change ticket. - In the Create a SQL change ticket dialog box, set the parameters that are described in the following table.
Parameter Description Reason Category Required. The reason for the data change. Business Background Required. The purpose or objective of the data change. Stakeholder Optional. The stakeholders of the data change. All the specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details. Execution Method Required. The way in which you want the ticket to be submitted for execution. In this example, select the default value After Audit Approved, Order Submitter Execute. Other valid values: - After Audit Approved, Auto Execute
- Last Auditor Execute
SQL Text The SQL statements to be executed for the data change. Affected Rows Required. The estimated number of data rows that will be affected by the data change. To obtain the actual number of affected rows, you can use the COUNT
function in SQL statements and execute the SQL statements on the SQLConsole tab.SQL Statements for Rollback Optional. The SQL statements that can be executed to roll back the data change. - Click OK. In the Submit ticket success message, click the generated ticket number to go to the Ticket Details page.
Note Alternatively, you can choose Database Development > R&D Space > My Tickets in the top navigation pane. DMS prechecks the SQL statement that you want to execute. The precheck items include the syntax of the SQL statement, whether the type of the SQL statement matches the type that you configured in the security rules, the permissions of the user who submitted the ticket, and the estimated number of rows to scan.
- After the ticket details are confirmed and the precheck is complete, click Submit for Approval and wait until the approval is complete. Note
- You can modify the ticket details only before you submit the ticket for approval.
- For more information about how to approve tickets, see Approve a data change ticket.
- In the Approval step, you can click View Approval Details to view the approval progress.
- After the ticket is approved, click Execute Change. In the dialog box that appears, set the parameters that are described in the following
table
Parameter Description Execution Strategy The time when the data change is executed. Valid values: - Running immediately: The data change is immediately executed after the ticket is submitted. This is the default value.
- Schedule: The data change is executed as scheduled.
Enable Submit as Single Transaction Specifies whether to enable transaction control. Valid values: - on: If an SQL statement fails to be executed, all the executed DML statements in the same transaction are rolled back. DDL statements cannot be rolled back.
- off: One SQL statement is executed at a time. If an SQL statement fails to be executed, the transaction is stopped. The other executed SQL statements in the same transaction are not rolled back. This is the default value.
Enable Backup Specifies whether to back up data. Valid values: - on: DMS generates REPLACE INTO statements to back up the data that will be affected
when
UPDATE
andDELETE
statements are executed. This helps with data restoration after misoperations.Note The REPLACE INTO statements are stored in an Object Storage Service (OSS) bucket. - off: No backup statements are generated.
- Click Confirm Execution.
After the data change is executed, you can view the execution information of this ticket, such as operation logs and scheduling logs.
Configure security rules to allow DML statement execution without the need to submit tickets
If tickets are required for every data change operation in development databases, the research and development (R&D) efficiency of developers decreases. To resolve this issue, DMS allows you to configure security rules that allow DML statements to be executed on the SQLConsole tab without the need to submit tickets. This improves R&D efficiency and ensures data security. The security rules are powered by the security rules engine of DMS.
This example shows you how to execute INSERT
statements in the poc_dev database on the SQLConsole tab.
- Configure security rules.
- Log on to the DMS console as a DMS administrator.
- In the top navigation bar, choose Security and Specifications > Security Rules.
- In the search box, enter
Security Rules for POC Development Databases
. Find the security rule set and click Edit in the Actions column. - On the Details page, click the SQL Correct tab in the left-side pane and set the Checkpoints parameter to SQL execution rules.
- Find the All DML can execute directly in SQLConsole rule and click Edit in the Actions column.
- Replace the domain-specific language (DSL) code in the rule with the following DSL
code and click Submit.
if @fac.sql_type in [ 'UPDATE','DELETE','INSERT','INSERT_SELECT','REPLACE','REPLACE_INTO','MERGE'] AND @fac.env_type not in ['product','pre'] then @act.allow_execute_direct elseif @fac.sql_type in [ 'UPDATE','DELETE','INSERT','INSERT_SELECT','REPLACE','REPLACE_INTO','MERGE'] AND @fac.env_type in ['product','pre'] then @act.allow_submit end
Note In DMS, you can define the type of environment where a database resides. For example, you can define theproduct
,pre
,dev
, ortest
environment for different databases. The preceding DSL code for security rules indicates that you can execute DML statements on databases whose environment type is notproduct
orpre
. For other environment types, you must submit tickets to execute DML statements. The DML statements include theUPDATE
,DELETE
, andINSERT
statements. For more information, see DSL syntax for security rules. - Enable the All DML can execute directly in SQLConsole rule and disable the opposite rule.
Note
- The opposite rule of the All DML can execute directly in SQLConsole rule is the All DML must execute by ticket rule.
- If you enable two rules that have opposite effects at the same time, the rule that imposes strict control on SQL statement execution takes effect. In this example, tickets must be submitted to execute DML statements.
- Execute
INSERT
statements.- Log on to the DMS console as a regular user.
- In the top navigation bar, choose SQL Console > SQL Console.
- In the Please select the database first dialog box, select the
poc_dev
database from the drop-down list or enter a keyword in the field to search for the database. Then, click Confirm. - On the SQLConsole tab, enter the following SQL statements in the SQL editor and click
Execute in the upper part of the SQL editor:
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');
- After you click Execute, DMS executes the SQL statements.
Configure security rules to allow high-risk SQL statement execution in the poc_prod database with tickets required
You can configure security rules to define the approval processes for different types
of SQL statements. For example, security risks may occur after you execute a DELETE
statement. Therefore, you must configure a strict approval process to execute DELETE
statements. The following example shows you how to add a data owner and a DBA to the
ticket approval process that is used to execute DELETE
statements.
- Configure an approval process.
- Log on to the DMS console as a DMS administrator.
- In the top navigation bar, choose Security and Specifications > Approval Processes.
- Click Create Approval Template.
- In the Create Approval Template dialog box, set the parameters that are described
in the following table.
Parameter Description Template Name The name of the approval template. Remarks The description of the approval template. Approval Node Click Add Node to add one or more approval nodes to the approval template. You must add the approval nodes in ascending order. For example, the approval nodes whose sequence numbers are 0 and 1 are the first and second approval nodes. Note In this example, the ticket is approved by the data owner and then by the DBA.
- Click Submit.
- Configure security rules.
- Log on to the DMS console as a DMS administrator.
- In the top navigation bar, choose Security and Specifications > Security Rules.
- Find the
Security Rules for POC Production Databases
rule set and click Edit in the Actions column. - On the Details page, click the SQL Correct tab in the left-side pane.
- Set the Checkpoints parameter to Risk Identification Rules and click Create Rule.
- In the Create Rule - SQL Correct dialog box, set the parameters that are described in the following table.
Parameter Description Checkpoints Required. In this example, select Risk Identification Rules. DMS also supports the following checkpoints: - SQL execution rules
- Risk Approval Rules
- Batch Data import rules
Template Database Optional. DMS provides a large number of security rule templates for different checkpoints. For more information, see SQL Correct. Note In this example, you must enter DSL code in the Rule DSL field.Rule Name Required. In this example, enter Production environments. The execution of DELETE statements is a high-risk operation
.Rule DSL Required. In this example, enter the following DSL code: 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.
- On the Details page, find the Production environments. The execution of DELETE statements is a high-risk operation rule and click Enable in the Actions column. In the message that appears, click OK.
Note After you click OK, the rule takes effect. All
DELETE
statements are identified as high-risk operations. - Set the Checkpoints parameter 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, change the template ID in the Rule DSL field to the ID of the template that you create in Step 1, and click Submit.
- Find the High risk approval process rule and click Enable in the Actions column. In the message that appears, click OK.
- Execute a
DELETE
statement.- Log on to the DMS console as a regular user.
- In the top navigation bar, choose SQL Console > SQL Console.
- In the Please select the database first dialog box, select the
poc_prod
database from the drop-down list or enter a keyword in the field to search for the database. Then, click Confirm. - On the SQLConsole tab, enter the following SQL statements in the SQL editor and click
Execute in the upper part of the SQL editor:
SELECT * FROM `data_modify` ;
Note This SQL statement queries data in thedata_modify
table. After you execute this SQL statement, three data records are returned and displayed below the SQL editor. - On the SQLConsole tab, enter the following SQL statements in the SQL editor and click
Execute in the upper part of the SQL editor:
DELETE FROM data_modify WHERE id = 1;
Note This SQL statement deletes one row of data. - Click Apply for Data Change.
Note As specified by the configured security rule, the execution of
DELETE
statements in a production database is a high-risk operation. You must submit a ticket and wait for the data owner and the DBA to approve the ticket. - In the Create a SQL change ticket dialog box, set the parameters that are described in the following table.
Parameter Description Reason Category Required. The reason for the data deletion operation. Business Background Required. The purpose or objective of the data deletion operation. Stakeholder Optional. The stakeholders of the data deletion operation. All the specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and DBAs are not allowed to view the ticket details. Execution Method Required. The way in which you want the ticket to be submitted for execution. In this example, select the default value After Audit Approved, Order Submitter Execute. Other valid values: - After Audit Approved, Auto Execute
- Last Auditor Execute
Affected Rows Required. The estimated number of data rows that will be affected by the data deletion operation. To obtain the actual number of affected rows, you can use the COUNT
function in SQL statements and execute the SQL statements on the SQLConsole tab.SQL Text Required. The SQL Statements to be executed for the data deletion operation. SQL Statements for Rollback Optional. The SQL statements that can be executed to roll back the data deletion operation. - Click OK. In the Submit ticket success message, click the generated ticket number to go to the Ticket Details page.
Note Alternatively, you can choose Database Development > R&D Space > My Tickets in the top navigation pane.
- On the Ticket Details page, confirm the ticket details.
Note You can view the ticket details on the Ticket Details page. DMS prechecks the SQL statement that you want to execute. The precheck items include the syntax of the SQL statement, whether the type of the SQL statement matches the type that you configured in the security rules, the permissions of the user who submitted the ticket, and the estimated number of rows to scan.
- After the ticket details are confirmed and the precheck is complete, click Submit for Approval and wait until the approval is complete. Note
- You can modify the ticket details only before you submit the ticket for approval.
- In the Approval Details dialog box, you can view the approval details and progress. In this example, the approval nodes for the data deletion operation are the data owner and the DBA, which are assigned to the same user. For more information about how to approve tickets, see Approve a data change ticket.
- After the ticket is approved, click Execute Change. In the dialog box that appears, set the parameters that are described in the following
table
Parameter Description Execution Strategy The time when the data change is executed. Valid values: - Running immediately: The data change is immediately executed after the ticket is submitted. This is the default value.
- Schedule: The data change is executed as scheduled.
Enable Submit as Single Transaction Specifies whether to enable transaction control. Valid values: - on: If an SQL statement fails to be executed, all the executed DML statements in the same transaction are rolled back. DDL statements cannot be rolled back.
- off: One SQL statement is executed at a time. If an SQL statement fails to be executed, the transaction is stopped. The other executed SQL statements in the same transaction are not rolled back. This is the default value.
Enable Backup Specifies whether to back up data. Valid values: - on: DMS generates REPLACE INTO statements to back up the data that will be affected
when
UPDATE
andDELETE
statements are executed, which helps with data restoration after misoperations. This is the default value.Note The REPLACE INTO statements are stored in an OSS bucket. - off: No backup statements are generated.
- Click Confirm Execution.
After the data change is executed, you can view the execution information of this ticket, such as operation logs and scheduling logs.
Configure security rules to forbid TRUNCATE statements in the poc_prod database
You can configure security rules to define the database types for different types
of SQL statements. TRUNCATE
statements delete all data from a table, which can cause unexpected data losses.
Therefore, the TRUNCATE statements are often forbidden in production databases. You
can configure security rules to forbid TRUNCATE
statements. This example shows you how to forbid TRUNCATE
statements and verify whether TRUNCATE
statements are forbidden.
- Configure security rules.
- Log on to the DMS console as a DMS administrator.
- In the top navigation bar, choose Security and Specifications > Security Rules.
- Find the
Security Rules for POC Production Databases
rule set and click Edit in the Actions column. - On the Details page, click the SQL Correct tab in the left-side pane and set the Checkpoints parameter to SQL execution rules.
- Find the Allow TRUNCATE to be executed directly in the SQL console rule and click Edit in the Actions column.
- Change the rule name to
Forbid TRUNCATE statements to be executed
, overwrite the original DSL code with the following DSL code, and then click Submit.if @fac.sql_type in ['TRUNCATE'] then @act.forbid_execute end
Note The preceding DSL code for security rules indicates thatTRUNCATE
statements cannot be executed on the SQLConsole tab. You must submit tickets to execute TRUNCATE statements. For more information, see DSL syntax for security rules. - Find the Forbid TRUNCATE statements to be executed rule and click Enable in the Actions column. In the message that appears, click OK.
- Execute a
TRUNCATE
statement on the SQLConsole tab.- Log on to the DMS console as a regular user.
- In the top navigation bar, choose SQL Console > SQL Console.
- In the Please select the database first dialog box, select the
poc_prod
database from the drop-down list or enter a keyword in the field to search for the database. Then, click Confirm. - On the SQLConsole tab, enter the following SQL statements in the SQL editor and click
Execute in the upper part of the SQL editor:
This SQL statement deletes all data from the
data_modify
table.TRUNCATE TABLE `data_modify`;
Note As specified by the configured security rule, DMS forbidsTRUNCATE
statements.
- Submit a ticket to execute the
TRUNCATE
statement.- Log on to the DMS console as a regular user.
- In the top navigation bar, choose Database Development > Data Change > Normal Data Modify.
- Set the parameters that are described in the following table and click Submit.
Parameter Description Database Required. In this example, select the poc_prod
database.Reason Category Required. The reason for the data clear operation. This helps you find the ticket in subsequent operations. Business Background Required. The purpose or objective of the data clear operation. This reduces unnecessary communication. Change Stakeholder Optional. The stakeholders of the data clear operation. All the specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and DBAs are not allowed to view the ticket details. Execution Method Required. The way in which you want the ticket to be submitted for execution. In this example, select the default value After Audit Approved, Order Submitter Execute. Other valid values: - After Audit Approved, Auto Execute
- Last Auditor Execute
Affected Rows Required. The estimated number of data rows that may be affected by the data clear operation. To obtain the actual number of affected rows, you can use the COUNT
function in SQL statements and execute the SQL statements on the SQLConsole tab.SQL Statements for Change Required. You can select Text or Attachment. In this example, select Text. SQL Text Required. In this example, enter the following TRUNCATE
statement:TRUNCATE TABLE `data_modify`;
SQL Statements for Rollback Optional. The SQL statements that can be executed to roll back the data clear operation. - After you submit the ticket, confirm the precheck results.
If the precheck fails, the security rule takes effect.
Note As specified by the configured security rule, DMS forbids TRUNCATE statements.