How to add security rules for schema design and modify the default approval template-阿里云帮助中心

This topic describes the basic configurations, checkpoints, factors, actions, and template library for schema design, and explains how to modify the default approval template and add new rules.

Background information

A schema design ticket typically involves multiple instances and follows a standard process: designing the schema in a test environment and then publishing it to a production environment. Therefore, the basic configurations, design rules, and risk identification rules within the security rules for schema design use the security rules associated with the change baseline database (a database in the test environment).

Design rules include Save and Edit - Check Table Header, Save and Edit - Check Field, and Save and Edit - Check Index. On the security rule edit page, these three checkpoints appear as tabs in the Checkpoints section.
Risk identification rules include Control Risks of Creating Tables, Control Risks of Modifying Fields, Control Risks of Modifying Indexes, and Control Risks of SQL Executions. These also appear as tabs in the Checkpoints section.

The approval process, however, uses the security rules of the database in the production environment. For example, if a schema design ticket is published to database B, the system applies the security rules of instance B.

Basic configurations

Schema design has several basic configurations:

Enable Asymmetric Publishing: By default, schema design changes can only be published symmetrically, meaning changes to a table can only be published to a table with the same name in another database. After you enable asymmetric publishing, you can publish changes to any target table, regardless of its name.

Warning
This feature carries significant risks. Enable it with caution and only for specific scenarios.

Development Process: This is a key configuration in schema design security rules. It defines all stages in the lifecycle of a schema design ticket. The following table describes these options.

Parameter	Description
Step	Consists of Design and Publish nodes. Design node: The Design node is the default and cannot be removed. It determines the environment in which the schema change task is designed. Publish node: After completing the design, the process moves to the publish stage to apply the changes. You can add multiple publish nodes.
Node Name	A custom node name, up to 10 characters long.
Database Environment	The environment where the node runs.
Execution Strategy	Immediately: The task is executed immediately after it is approved. Timed: You can specify a time to publish the task. If the task is approved before the specified time, it is executed at that time. Otherwise, the task is canceled.
Can be Rolled Back	Specifies if the publish task can be rolled back to the Design node.
Can be Skipped	Specifies whether the current step can be skipped.
Anchor	Marks the point where ticket editing is frozen. After the anchored step successfully completes, the schema can no longer be edited, and the ticket status changes to Published.
Operation	You can remove a Publish node based on your requirements.

Field Type Configuration: Configures the data types that are allowed for the Add Field option.
Index Type Configuration: Specifies the data types allowed in Add Index.
Table Creation Template: Configures a template for creating tables. New tables load this template by default. This template is optional and can be modified during table creation.
Prohibit modifying the data types of original fields: Prohibits modifying the data types of existing fields when changing a table.
Prohibit deleting original fields: Specifies whether to prohibit deleting existing fields when changing a table. This is a high-risk operation, so we recommend enabling this option.
Prohibit renaming original fields: Specifies whether to prohibit renaming existing fields when changing a table. Renaming existing fields is a high-risk operation. We recommend that you enable this option.
Table Character Set Permission Configuration: Configures the allowed character sets for new tables, such as utf8 and utf8mb4.
Default Approval Template for Schema Design: If no approval rule is configured for the Approval Rule Check checkpoint, schema design tickets use this default approval template. You can change the approval process of the default template by using the Switch Approval Template feature. For more information, see Modify the default approval template.
Automatically advance the ticket to the closed state upon release: If enabled, after the node that contains the anchor in the development process successfully executes a change, the system automatically advances the ticket to the closed state. (The anchor must be on the last release node in the development process.)

Checkpoints

Schema design involves two processes:

Save and edit process: This process corresponds to the following three checkpoints, which sequentially check the specifications for table headers, fields, and indexes.
- Save and Edit - Check Table Header
- Save and Edit - Check Field
- Save and Edit - Check Index
Change execution process: This process corresponds to the following five checkpoints. The first four identify the risks of Data Definition Language (DDL) changes, and the last one specifies the approval process for each risk.
- Control Risks of Creating Tables
- Control Risks of Modifying Fields
- Control Risks of Modifying Indexes
- Control Risks of SQL Executions
- Approval Rule Check

Both processes have numerous built-in security rule templates that you can use directly or customize. For more information, see Add a new rule.

Factors and actions

Factor: A factor is a built-in system variable that provides contextual information for security rule checks, such as the SQL type or the number of affected rows. All factors start with @fac. followed by the factor name. Each module provides different factors for different checkpoints. The following table describes the factors available for schema change.

Factor	Description
@fac.table_kind	The type of table being edited. Valid values: new: A new table. old: An existing table.
@fac.column_kind	The type of field in the table schema. Valid values: new: A new field. old: An existing field.
@fac.xxxx_old	Retrieves the original value of a field or index for comparison.
@fac.column_is_primary	Indicates if a field is part of the primary key. Valid values: true: The field is in the primary key. false: The field is not in the primary key.
@fac.column_type_support_default	Indicates if a default value can be set for the field's data type. Valid values: true: Supported. false: Not supported. For example, the `CHAR` data type supports a default value, but the `TEXT` data type does not.
@fac.index_kind	The type of index in the table schema. Valid values: new: A new index. old: An existing index.
@fac.index_column_count	The number of fields in the index.
@fac.change_type	The type of field or index change identified by the database where the DDL statement is executed. Valid values: add modify delete
@fac.altered_table_size	The size of the modified table, in MB.
@fac.online_execute	Indicates whether the change can be executed online. Valid values: true: ONLINE false: NOT ONLINE
@fac.change_risk_level	The risk level of the change. Valid values: high: High risk medium: Medium risk low: Low risk
@fac.env_type	The environment type, which is the environment identifier, such as `DEV` or `PRODUCT`. For more information about environment types, see Change the environment type of an instance.

Action: An action is a behavior that the system performs after an if condition is met, such as prohibiting a ticket submission, selecting a workflow, allowing an execution, or denying an execution. These actions define the main purpose of a security rule. All actions start with @act. followed by the action name. Each module provides different actions for different checkpoints. The following table describes the actions available for schema design.

Actions	Description
@act.block_submit	Blocks submission and displays an error message at the table-saving stage. Usage: `@act.block_submit 'Reason for blocking'`.
@act.show_warning	Used during the table saving stage. Displays a warning message without blocking submission. Usage: `@act.show_warning 'Warning content'`.
@act.mark_middle_risk	Used during the risk identification stage. Marks the change as medium risk. Usage: `@act.mark_middle_risk 'Reason'`.
@act.mark_high_risk	Used during the risk identification stage. Marks the change as high risk. Usage: `@act.mark_high_risk 'Reason'`.
@act.forbid_submit_publish	Used during the approval process configuration stage. Blocks the change from being executed. Usage: `@act.forbid_submit_publish 'Reason'`.
@act.do_not_approve	Specifies the ID of an approval template. For more information, see Set the approval process.
@act.choose_approve_template
@act.choose_approve_template_with_reason

Template library

Security Rules provides numerous rule templates that you can enable directly or modify before enabling. The following table lists the templates for schema change.

Checkpoint	Template
Save and Edit - Check Table Header	Prohibit using keywords as the table name for a new table
	Limit the length of the name for a new table
	Require comments for a new table
	Require the name of a new table to be in lowercase
	Require a primary key (PK) for a new table
	Require a unique key (PK/UK) for a new table
	Check the number of indexes and warn the user if the number is excessive
	Check whether certain fields exist for a new table
Save and Edit - Check Field	Prohibit using keywords as the field name for a new field
	Require comments for a new field
	Specify case sensitivity for the name of a new field
	Require a default value when you add a non-null field to an existing table
	Require a default value when you change an existing field from nullable to non-nullable
	Requires that a new primary key field be an integer type
	Requires that all fields in a new table be non-nullable
	Limit the length of a new field of the CHAR type
	Limit the length of a new field of the VARCHAR type
Save and Edit - Check Index	Prohibit using keywords as the index name for a new index
	Specify case sensitivity for the name of a new index
	Limit the format of the name for a new UNIQUE index
	Limit the format of the name for a new NORMAL index
	Limit the format of the name for a new FULLTEXT index
	Limit the format of the name for a new SPATIAL index
	Limit the number of new primary key fields
	Limit the number of new index fields
Control Risks of Creating Tables	Control the number of shards for a logical table
	Control the primary key of a table
	Control the total number of indexes in a table
Control Risks of Modifying Fields	Control field deletion
	Control the data type of a new field
	Control field renaming
	Control the modification of a field data type
Control Risks of Modifying Indexes	Control primary key deletion
	Control index deletion
	Control the change from a primary key to an index
	Controls changes to columns in a primary key
	Control the modification of a common index
	Control the type of a new index
Control Risks of SQL Executions	Control risks of core instances
	Control the storage engine of a table
	Control risks of table locking or large table schema changes
Approval Rule Check	Prohibit applying any DDL changes to a database
	Prohibit applying any DDL changes to a production database
	Configure approval-free DDL changes for a test database
	Set approval rules based on risk levels for the production environment

Modify the default approval template

Log in to DMS 5.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 page, find the target rule set and click Edit in the Actions column.
In the left-side navigation pane of the Details page, click Schema Design.
On the Schema Design tab, click Basic Configurations.
Find Default Approval Template for Schema Design and click Edit in the Actions column.
In the Edit Configuration Item dialog box, click Switch Approval Template.
Find the target template by its Template Name and click Select in the Actions column.

Note
You can also click Reset to No Approval to allow tickets to skip the approval process.
Click Submit.

Add a new rule

Log in to DMS 5.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 page, find the target rule set and click Edit in the Actions column.
In the left-side navigation pane of the Details page, click Schema Design.
On the Schema Design tab, click Add Rule to the right of Actions.

In the Add Rule - Schema Design dialog box, configure the following parameters.

Parameter	Description
Checkpoint (Required)	Select a checkpoint for the new rule. Schema design provides multiple checkpoints. For more information, see Checkpoints.
Template Library (Optional)	The template library provides many rule templates. After you select a Checkpoint, you can load a rule template from the Template Library. For a list of available templates, see Template library.
Rule Name (Required)	A custom name for the rule. If you select a rule template from the Template Library, this field is automatically populated.
Rule DSL (Required)	Enter the Rule DSL. For more information about the DSL syntax, see the documentation. (This field is automatically populated if you select a rule template in the Template Library.)

Click Submit.
By default, the new rule is in the Disabled state. Find the rule and click Enable in the Actions column.
In the dialog box that appears, click OK.