All Products
Search
Document Center

SQL Correct

Last Updated: Jun 03, 2020

You can execute SQL statements for data changes. It is a method that requires a high level of security. Data Management Service (DMS) allows you to set security rules in the SQL Correct module for validating SQL statements during the submission and approval of data change tickets. A ticket can be submitted or approved only after the corresponding SQL statements for data changes are validated by the security rules, which are also called checkpoints in this topic.

Background

By applying new security rules based on a domain-specific language (DSL), you can define risk levels for tickets so that a ticket can be directed to the approval process that is designed for the specified risk level. For example, you can forbid specified SQL statements as required in a strict manner, or execute all SQL statements in a loose manner. For more information about the DSL syntax, see DSL syntax for security rules.

Prerequisites

You are a DMS administrator, database administrator (DBA), or security administrator.

Basic configuration items

You can view the following two basic configuration items on the SQL Correct tab:

  • Data change default approval Template: the default approval template that takes effect if you do not set different approval rules for data changes at different risk levels. You can also change the default approval template. For more information, see Procedure of changing the default approval template.
  • Data Change risk level list: defines different risk levels that are used in risk identification rules and risk approval rules to identify and classify risks in data changes and then process tickets accordingly. You can set risk levels based on the type and scenario of data changes. Data changes at different risk levels are directed to different approval processes. Currently, DMS offers the following four default risk levels:
    • LOW
    • MIDDLE
    • HIGH
    • HIGHEST

Checkpoints

When you submit a data change ticket, the system validates the ticket based on the configured checkpoints. A ticket can be submitted only after the corresponding SQL statements for data changes are validated by the checkpoints. DMS offers the following four checkpoints for validating SQL statements:

  • SQL execution rules: These rules are used to limit the categories and subcategories of SQL statements that can be submitted. If you do not set any SQL execution rule, all SQL statements used for data changes are prohibited. Assume that you want to use data manipulation language (DML) statements to change the data of a database in an online environment. You can create an SQL execution rule with the following configuration specified. For more information about how to create a security rule, see Procedure of creating a security rule.

    1. if
    2. @fac.env_type in ['product']
    3. and
    4. @fac.sql_type in [ 'UPDATE','DELETE','INSERT']
    5. then
    6. @act.allow_submit
    7. end

    The rule with the preceding configuration specifies that you can only submit a data change ticket to execute UPDATE, DELETE, and INSERT statements on a database deployed in an online environment.

  • Risk identification rules: If a ticket conforms to the preset SQL execution rules, DMS continues the validation based on the risk identification rules. These rules are used to identify and classify risks in data changes. You can create risk identification rules based on your database environment, the number of rows in which data is affected, and categories and subcategories of SQL statements.

    Note: Different risk identification rules apply to different check items. DMS automatically identifies the highest risk level for a data change. For example, if the risk level of a data change is identified as high, medium, and low by one, three, and five risk identification rules respectively, the system assumes that the data change is at high risk.

    Assume that you want to define an offline environment as a factor at low risk. You can create a risk identification rule with the following configuration specified. For more information about how to create a security rule, see Procedure of creating a security rule.

    1. if
    2. @fac.env_type not in ['product','pre']
    3. then
    4. @act.mark_risk 'low' 'low risk level: offline environment'
    5. end

    The rule with the preceding configuration specifies that if the target database is deployed in an offline environment, data changes are at low risk.

  • Risk approval rules: After the risk level of a data change is identified by the risk identification rules, the system processes the ticket based on the risk approval rules. You can set custom risk approval rules as required.

    Note:

    • If a data change does not hit any risk approval rule, DMS uses the default approval template specified under the Basic Configuration Item checkpoint to process the ticket. For more information, see Approval processes.
    • By default, an offline environment is identified as a factor at low risk and requires no approval.
  • Batch data import rules: These rules apply only to data change tickets of the Large Data Import type. You can use the default rules provided by DMS, or set custom rules as required. For more information, see Procedure of creating a security rule.

Factors and actions

  • Factor: A factor is a system built-in variable that is used to obtain the context to be validated by security rules, such as the subcategories of SQL statements and the number of rows in which data is affected. A factor name starts with @fac., appended with the display name of the factor type. Each module of the Security Rules page offers different factors for different checkpoints. The following table describes the supported factors in the SQL Correct module.

    Factor Description
    @fac.env_type The type of the environment. The value is the display name of the environment type, such as DEV and PRODUCT. For more information, see Change the environment type of an instance.
    @fac.sql_type The subcategory of the SQL statement, such as UPDATE and INSERT.
    @fac.detail_type The type of the data change. Valid values:
    • COMMON: common data change.
    • CHUNK_DML: data change without locking tables.
    • PROCEDURE: data change of stored procedures.
    • CRON_CLEAR_DATA: data change of cleaning tables periodically.
    • BIG_FILE: data change of importing a large amount of data at a time.
    @fac.is_logic Indicates whether the database is a logical database.
    @fac.extra_info The additional information about the data change. This factor is currently not in use.
    @fac.is_ignore_affect_rows Indicates whether to skip the validation.
    @fac.insert_rows The number of rows in which data is affected by the newly inserted data.
    @fac.update_delete_rows The number of rows in which data is affected by the newly updated data.
    @fac.max_alter_table_size The maximum size of the table whose data is changed.
    @fac.is_has_security_column Indicates whether the SQL script contains any sensitive field.
    @fac.security_column_list The sensitive fields contained in the SQL script.
    @fac.risk_level The identified risk level of the data change.
    @fac.risk_reason The reason for the identification of the risk level.
  • Action: An action is the operation that the system performs after the conditions specified in the if statement are met. For example, the system can perform the relevant action to forbid the submission of a ticket, select an approval process, approve a ticket, or reject a ticket. Actions show the purpose of setting security rules. An action name starts with @act., appended with the display name of the action type. Each module of the Security Rules page offers different actions for different checkpoints. The following table describes the supported actions in the SQL Correct module.

    Action Description
    @act.allow_submit Allows the SQL statements for changing data to be executed through a ticket.
    @act.allow_execute_direct Allows the SQL statements for changing data to be executed directly in the SQLConsole.
    @act.forbid_execute Forbids the SQL statements for changing data from being executed.
    @act.mark_risk Identifies the risk level of the data change. The statement is in the following format: @act.mark_risk 'middle' 'medium risk level: online environment'.
    @act.do_not_approve Specifies the approval template. For more information, see Approval processes.
    @act.choose_approve_template
    @act.choose_approve_template_with_reason

Templates of security rules

DMS provides you with various system built-in templates of security rules. You can directly use the templates or modify the templates based on your business requirements. The following table describes the supported rule templates in the SQL Correct module.

Checkpoint Feature of template
SQL execution rules Allows all DML statements to be executed directly in the SQLConsole.
Specifies that all DML statements must be executed through a ticket.
Allows all data definition language (DDL) statements to be executed directly in the SQLConsole.
Specifies that all DDL statements must be executed through a ticket.
Allows all data control language (DCL) statements to be executed directly in the SQLConsole.
Specifies that all DCL statements must be executed through a ticket.
Allows all programmable objects to be executed directly in the SQLConsole.
Specifies that all programmable objects must be executed through a ticket.
Allows the UPDATE statements to be executed directly in the SQLConsole.
Specifies that the UPDATE statements must be executed through a ticket.
Allows the DELETE statements to be executed directly in the SQLConsole.
Specifies that the DELETE statements must be executed through a ticket.
Allows the INSERT statements to be executed directly in the SQLConsole.
Specifies that the INSERT statements must be executed through a ticket.
Allows the TRUNCATE statements to be executed directly in the SQLConsole.
Specifies that the TRUNCATE statements must be executed through a ticket.
Allows the CREATE TABLE statements to be executed directly in the SQLConsole.
Specifies that the CREATE TABLE statements must be executed through a ticket.
Allows the DROP TABLE statements to be executed directly in the SQLConsole.
Specifies that the DROP TABLE statements must be executed through a ticket.
Allows the ALTER TABLE statements to be executed directly in the SQLConsole.
Specifies that the ALTER TABLE statements must be executed through a ticket.
Allows the KILL statements to be executed directly in the SQLConsole.
Specifies that the KILL statements must be executed through a ticket.
Risk identification rules Specifies that an offline environment is a factor at low risk.
Specifies that an online environment is a factor at medium risk.
Specifies that a data change is at high risk if the data is in an online environment and the validation for data rows is skipped.
Specifies that a data change is at high risk if the data is in an online environment and the number of newly inserted rows exceeds 100,000.
Specifies that a data change is at high risk if the data is in an online environment and the number of newly updated or deleted rows exceeds 100,000.
Specifies that a data change is at high risk if the data is in an online environment and sensitive fields are involved.
Specifies that a data change is at high risk if the data is in an online environment and the data is changed by using non-DML statements.
Specifies that a data change is at medium risk if a large amount of data is imported to an online database at a time.
Risk approval rules Specifies that data changes at low risk require no approval.
Specifies an approval process for data changes at medium risk.
Specifies an approval process for data changes at high risk.
Batch data import rules Allows you to load SQL statements from local files to import a large amount of data at a time.
Allows you to load UPDATE statements from specified tickets to import a large amount of data at a time to improve efficiency.

Procedure of changing the default approval template

  1. Log on to the DMS console.
  2. In the top navigation bar, choose System Management > Security > Security Rules.
  3. On the Security Rules page that appears, find the target rule set and click Edit in the Actions column.
  4. On the Details page that appears, click the SQL Correct tab.
  5. On the SQL Correct tab, the basic configuration items appear by default.
  6. Find the Data change default approval Template configuration item and click Edit in the Actions column.
    Change the default approval template
  7. In the Change Configuration Item dialog box that appears, click Switch Approval Template.
  8. In the Switch Approval Template dialog box that appears, find the target template and click Select in the Actions column.

Note: You can also click Reset to Free of Approval to skip the approval for tickets.

  1. Click Submit.

Procedure of creating a security rule

  1. Log on to the DMS console.
  2. In the top navigation bar, choose System Management > Security > Security Rules.
  3. On the Security Rules page that appears, find the target rule set and click Edit in the Actions column.
  4. On the Details page that appears, click the SQL Correct tab.
  5. On the SQL Correct tab, click Create Rule next to Actions.
    Create a security rule 1
  6. In the Create Rule - SQL Correct dialog box that appears, set the parameter as required. The following table describes the parameters.

    Parameter Description
    Checkpoints (Required) The checkpoint under which you want to create the security rule. The SQL Correct module offers the following four checkpoints:
    • SQL execution rules
    • Risk Identification Rules
    • Risk Approval Rules
    • Batch Data import rules
    Template Database (Optional) The template based on which you want to create the security rule. DMS provides you with various system built-in templates of security rules. After you select a checkpoint from the Checkpoints drop-down list, you can click Load from Template Database to select a template. For more information about the available templates, see Templates of security rules.
    Rule Name (Required) The custom name of the security rule. If you load a security rule from a template, the rule name is automatically filled in.
    Rule DSL (Required) The DSL statement used to set the security rule. For more information, see DSL syntax for security rules. If you load a security rule from a template, the statement is automatically filled in.
  7. Click Submit.

  8. Find the created security rule and click Enable in the Actions column. By default, the created security rule is in the Disabled state.
  9. In the message that appears, click OK.