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.
PrerequisitesYou are a DMS administrator, a database administrator (DBA), or a security administrator.
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
|Basic Configuration Item|
By default, the following three rules are provided:
|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.
For example, if the following two rules are both enabled, the second rule prevails.
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:
The preceding code indicates that
|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:
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
- Log on to the DMS console V5.0.
- In the top navigation bar, choose .
- On the Security Rules tab, find the security rule set that you want to manage and click Edit in the Actions column.
- On the Details page, click the SQL Correct tab on the left.
- On the SQL Correct tab, click Basic Configuration Item.
- Find the Data change default approval Template rule and click Edit in the Actions column.
- In the Change Configuration Item dialog box, click Switch Approval Template.
- 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.
- Click Submit.
Factors and actions provided on the SQL Correct tab
- 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.
|@fac.env_type||The type of the environment. The value is the display name of the environment type, such as |
|@fac.sql_type||The type of the SQL statement. The value is the subcategory of the SQL statement, such as |
|@fac.detail_type||The type of the data change ticket. Valid values: |
|@fac.is_logic||A Boolean value that 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||A Boolean value that indicates whether to skip the validation.|
|@fac.insert_rows||The number of data rows to be inserted.|
|@fac.update_delete_rows||The number of rows of data 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||A Boolean value that 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.|
|@fac.table_name_list||The name of tables involved in the data change. Example:|
The proceeding code indicates that if the name of the table involved in the data change is tb_order or tb1_order, the data change is marked with high risk.
ifstatement 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.
|@act.allow_submit||Allows the submission of SQL statements to be executed in a ticket.|
|@act.allow_execute_direct||Allows the execution of SQL statements in the SQLConsole.|
|@act.forbid_execute||Forbids the execution of SQL statements.|
|@act.mark_risk||Marks a data change with a risk level. Example: |
|@act.do_not_approve||Specifies the ID of an approval template. For more information, see Configure approval processes.|