In Data Management (DMS), you can execute SQL statements for data changes. However, the execution of such SQL statements requires a high level of security. DMS allows you to configure security rules on the SQL Correct tab to validate the submission and approval of tickets for data changes. Only the SQL statements that are validated by the security rules can be executed.

Prerequisites

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

Background information

DMS allows you to write domain-specific language (DSL) statements to define security rules. You can specify risk levels for data changes based on your business requirements. Then, you can specify different approval processes for data change tickets at different risk levels. For example, you can specify a strict approval process to control the execution of SQL statements or a loose approval process to allow the execution of all SQL statements.

Checkpoints provided on the SQL Correct tab

Checkpoint Description Example
Basic Configuration Item

By default, the following three rules are provided:

  • Data change default approval Template: By default, the ID of the approval template used for data change tickets is 853, and the approver is the DBA of the database involved.
    Note For more information about how to change the default approval template, see Change the default approval template.
  • Data Change risk level list: Data changes in tickets can be marked with different risk levels. You can specify different risk levels and approval processes for different scenarios. This risk level list is primarily used to create rules for the Risk Identification Rules and Risk Approval Rules checkpoints.

    By default, the following four risk levels are supported:

    • LOW: low risk
    • MIDDLE: medium risk
    • High: high risk
    • HIGHEST: major risk
  • Whether data import supports selecting speed mode: Specifies whether data can be imported in speed mode into a database. For more information, see Import data.
-
SQL execution rules SQL execution rules can be used to specify the SQL statements that are allowed to be executed in the SQL Console module.
Note If two conflicting rules are enabled, the rule that is more strict prevails.

In the following example, the two rules are both enabled, and the second rule prevails.

  • All DML can execute directly in SQLConsole
  • All DML must execute by ticket

If you want to allow the execution of specific DML statements to change the data of a database in an online environment, you can create the following SQL execution rule:

if
  @fac.env_type not in ['product']
  and
  @fac.sql_type in [ 'UPDATE','DELETE','INSERT']
then
  @act.allow_submit
end
The preceding code indicates that UPDATE, DELETE, and INSERT statements are allowed to be executed on a database if the database is in an offline environment.
Risk Identification Rules Risk identification 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 the categories and subcategories of SQL statements.
Note A data change can be marked with multiple risk levels by multiple security rules. The highest risk level prevails.

For example, a data change is marked with low risk by five rules, medium risk by three rules, and high risk by one rule. In this case, the data change is identified as a high-risk operation.

If you want to mark data changes on a database that is in an offline environment with low risk, you can create the following risk identification rule:

if
  @fac.env_type not in ['product','pre']
then
  @act.mark_risk 'low 'Low risk level: offline environment'
end
The preceding code indicates that a data change is marked with low risk if the database involved is in an offline environment.
Risk Approval Rules Risk approval rules are used to specify built-in or custom approval rules for tickets that involve data changes marked with different risk levels.

If a data change is checked by all rules configured for the Risk Identification Rules checkpoint but fails to be marked with a risk level, the approval process specified for the Data change default approval Template rule of the Basic Configuration Item checkpoint is used.

By default, an offline environment is identified as a factor at low risk and requires no approval.
Batch Data import rules These rules apply to only the validation of data import tickets. Allow bulk import of insert statements

Change the default approval template

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, click Security and Specifications. In the left-side navigation pane, click Security Rules.
    Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose System > Security > Security Rules.
  3. On the Security Rules tab, find the security rule set that you want to manage and click Edit in the Actions column.
  4. On the Details page, click the SQL Correct tab on the left.
  5. On the SQL Correct tab, click Basic Configuration Item.
  6. Find the Data change default approval Template rule and click Edit in the Actions column.
  7. In the Change Configuration Item dialog box, click Switch Approval Template.
  8. In the Switch Approval Template dialog box, find the template that you want to use and click Select in the Actions column.
    Note If you do not require approval for tickets by default, you can click Reset to Free of Approval to skip approval processes.
  9. Click Submit.

Factors and actions provided on the SQL Correct tab

  • A factor is a predefined variable that is provided by DMS. You can use factors to obtain the context to be validated by security rules, such as the categories of SQL statements and the number of data rows to be affected.
    • A factor name consists of the prefix @fac. and the display name of the factor.
    • Each tab on the Security Rules tab displays different factors for different checkpoints.
    Table 1. Factors provided on the SQL Correct tab
    @fac.env_type The type of the environment. The value is the display name of the environment type, such as DEV or PRODUCT. For more information, see Change the environment type of an instance.
    @fac.sql_type The type of the SQL statement. The value is the subcategory of the SQL statement, such as UPDATE or INSERT. For more information, see the SQL subcategories that are described in SQL Console for relational databases.
    @fac.detail_type The type of the data change ticket. Valid values:
    • COMMON: a Normal Data Modify ticket
    • CHUNK_DML: a Lockless change ticket
    • PROCEDURE: a Programmable Object ticket
    • CRON_CLEAR_DATA: a History Data Clean ticket
    • BIG_FILE: a Large Data Import ticket
    @fac.is_logic Indicates whether the database to be affected is a logical database.
    @fac.extra_info Other information about the ticket. This factor is not in use.
    @fac.is_ignore_affect_rows Indicates whether to skip the validation.
    @fac.insert_rows The number of data rows to be inserted.
    @fac.update_delete_rows The number of data rows to be updated.
    @fac.max_alter_table_size The size of the largest tablespace in which the table to be modified is stored.
    @fac.is_has_security_column Indicates whether the SQL statement to be executed involves sensitive fields.
    @fac.security_column_list The sensitive fields that the SQL statement to be executed involves.
    @fac.risk_level The risk level of the data change to be performed by the SQL statement.
    @fac.risk_reason The reason based on which the data change is marked with this risk level.
  • An action is an operation that the system performs if the conditions specified in the if statement are met. The action that you specify for a security rule shows the purpose of the security rule. For example, you can forbid the submission of a ticket, select an approval process, approve a ticket, or reject a ticket.
    • An action name consists of the prefix @act. and the display name of the action.
    • Each tab on the Security Rules tab displays different actions for different checkpoints.
    Table 2. Actions provided on the SQL Correct tab
    @act.allow_submit Requires the submission of SQL statements to be executed in a ticket.
    @act.allow_execute_direct Allows the execution of SQL statements in SQL Console.
    @act.forbid_execute Forbids the execution of SQL statements.
    @act.mark_risk Marks a data change with a risk level. Example: @act.mark_risk 'middle' 'Medium-level risk: online environment'.
    @act.do_not_approve Specifies the ID of an approval template. For more information, see Configure approval processes.
    @act.choose_approve_template
    @act.choose_approve_template_with_reason