All Products
Search

This Product

    Data Management:SQL changes

    Document Center

    Data Management:SQL changes

    Last Updated:Apr 02, 2026

    The SQL Correct feature is the entry point for executing SQL changes and requires a high level of security. When you submit a ticket for approval, a series of security rules verifies the request. Only SQL statements that pass this verification can be executed.

    Prerequisites

    You are an Administrator, a DBA, or a Security Administrator.

    Background information

    The security rules in Data Management (DMS) provide flexible domain-specific language (DSL) capabilities. This lets you customize risk levels as needed and use different approval workflows for changes that have different risks. For example, you can strictly limit SQL execution or leniently allow all SQL statements.

    SQL Change module checkpoints

    Checkpoint

    Description

    Example

    Basic Configuration Item

    This section contains five default rules:

    • Default approval template for data changes: The default approval template ID for data change tickets is 853, and the approver is the DBA.

      Note

      To change the default approval template, see Change the default approval template.

    • Risk level list for data changes: Risk levels are used to assess the risks of changes in tickets. You can set different risk levels and approval workflows for different scenarios. This is mainly used for the Risk Identification Rules and Risk Approval Rules checkpoints.

      Four default risk levels are available:

      • LOW: Low risk

      • MIDDLE: Medium risk

      • HIGH: High risk

      • HIGHEST: Major risk

    • Support for simple mode in data import: Specifies whether simple mode is supported when you create data import tickets for the databases that use this security rule. For more information, see Data Import.

    • Allow Skipping The Affected Rows Check For Standard Data Changes: If you enable this rule, DMS prohibits skipping the affected rows check for standard data change tickets during the precheck stage. Otherwise, you can skip this check.

    • Prohibit submitting normal data change tickets with inconsistent row count checks: If this rule is enabled, normal data change tickets with an inconsistent row count check cannot be submitted. Otherwise, they can be submitted.

    -

    SQL execution rules

    Restricts the SQL statements that can be executed in the SQL window. Only allowed SQL statements can be executed.

    Note

    If two conflicting rules are enabled, the stricter rule is enforced.

    For example, if the following two rules are both enabled, the system enforces the second rule.

    • Allow all DML statements to be directly executed in the SQL console.

    • Prohibit all DML statements from being directly executed in the SQL console. They must be executed through tickets.

    To allow only DML statements to modify the online product database, add the following SQL execution rule:

    Assumptions

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

    Meaning: If the database environment is not a production environment and the SQL statement is of the UPDATE, DELETE, or INSERT type, the SQL statement is allowed to be executed.

    Risk Identification Rules

    Identifies the risk level of submitted tickets. You can customize risk levels based on conditions such as the database environment, the number of affected rows, and the SQL type.

    Note

    Different security rules mark a change task with different risk levels. The final risk level is the highest one among all marked levels.

    For example, a SQL change task that has 5 low risks, 3 medium risks, and 1 high risk is classified as high-risk.

    To define offline environments as low risk, add the following risk identification rule:

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

    Meaning: If the database environment is not a production environment, the risk level is low.

    Risk Approval Rules

    Configure or customize approval rules for different risk levels.

    If a database change does not hit any risk level at the Risk Identification Rules checkpoint, it uses the approval workflow specified by the Default approval template for data changes in the Basic Configuration Item section.

    By default, a staging environment is considered low risk and does not require approval.

    Batch data import rules

    Validation rules that apply to Data Import tickets.

    Allows batch import of insert statements.

    Change the default approval template

    1. Log in to DMS 5.0.

    2. Move the pointer over the 2023-01-28_15-57-17.png 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.

    3. On the Security Rules tab, find the target rule set and click Edit in the Actions column.

    4. In the left navigation bar on the Details page, click SQL Changes.

    5. In SQL Change, click Basic Configuration Item.

    6. In the Default approval template for data changes row, click Edit in the Actions column.

    7. In the Edit Configuration Item dialog box, click Switch Approval Template.

    8. In the Switch Approval Template dialog box, find the target template and click Select in the Actions column.

      Note

      If tickets do not require approval, you can click Reset to Approval-Free to bypass the approval step.

    9. Click Submit.

    Factors and actions for the SQL Correct module

    Factors

    Factors are built-in system variables. You can use them to obtain context information for security rule verification, such as the SQL type and the number of affected rows.

    • All factors start with @fac. followed by the factor name.

    • Different checkpoints in each module provide different factors.

    Table 1. Factor list

    Factor

    Description

    @fac.env_type

    The environment type. The value is the environment identifier, such as DEV or PRODUCT. For more information, see Instance environments.

    @fac.sql_type

    The type of the SQL script, such as UPDATE or INSERT. For more information, see the description of SQL child classes in SQLConsole.

    @fac.detail_type

    The type of data change:

    • COMMON: Normal data change

    • CHUNK_DML: Lock-free data change

    • PROCEDURE: Stored procedure

    • CRON_CLEAR_DATA: Scheduled table cleanup

    • BIG_FILE: Batch data import

    @fac.is_logic

    Indicates whether the database is a logical database.

    @fac.extra_info

    Other change information. This factor is not in use.

    @fac.is_ignore_affect_rows

    Indicates whether to skip the check.

    @fac.insert_rows

    The number of affected rows for an insert operation.

    @fac.update_delete_rows

    The number of affected rows.

    @fac.max_alter_table_size

    The maximum tablespace size of the modified table.

    @fac.is_has_security_column

    Indicates whether the SQL script contains sensitive columns.

    @fac.security_column_list

    A list of sensitive columns that the SQL script contains.

    @fac.risk_level

    The detected risk level.

    @fac.risk_reason

    The reason why this risk level was detected.

    @fac.table_name_list

    A list of table names involved in the SQL change. Example:

    if    
   'tb_order' in @fac.table_name_list or 'tb1_order' in @fac.table_name_list
then    
    @act.mark_risk 'high' 'High risk: Involves changes to the order table'
end

    Meaning: If the SQL change involves the tb_order or tb1_order table, the risk level is high.

    Actions

    An action is a behavior that the system performs when the if condition is met. Examples include prohibiting ticket submission, selecting a workflow, allowing execution, and rejecting execution.

    • All actions start with @act. followed by the action name.

    • Different checkpoints in each module provide different actions.

    Table 2. Action list

    Action

    Description

    @act.allow_submit

    Allows the ticket to be submitted for execution.

    @act.allow_execute_direct

    Allows direct execution in the SQL console.

    @act.forbid_execute

    Prohibits execution.

    @act.mark_risk

    Marks a risk. Usage: @act.mark_risk 'middle' 'Medium risk: production environment'.

    @act.do_not_approve

    Specifies the ID of an approval template. For more information, see Set approval workflows.

    @act.choose_approve_template

    @act.choose_approve_template_with_reason