Data Management:SQL Console for relational databases

Checkpoints in SQL Console

The following flowchart shows how checkpoints work.

Factors and actions

• 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.

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

  • Each module on the Details page of a security rule set provides different factors for different checkpoints.

  Table 1. Factors provided by the SQL Console module

  Factor	Description
  @fac.sql_count	The number of SQL statements that are submitted at a time.
  @fac.select_sql_count	The number of DQL statements among the SQL statements that are submitted at a time.
  @fac.dml_sql_count	The number of DML statements among the SQL statements that are submitted at a time.
  @fac.sql_type	The category of an SQL statement. For more information, see the SQL statements that can be executed in DMS section of this topic.
  @fac.sql_sub_type	The subcategory of an SQL statement. For more information, see the SQL statements that can be executed in DMS section of this topic.
  @fac.env_type	The environment type. The value is an environment identifier, such as DEV or PRODUCT. For more information, see Instance environment type.
  @fac.fulltable_delete	Indicates whether the current SQL statement is a full table DELETE. The valid values are: true false
  @fac.fulltable_update	Specifies whether the current SQL statement is a full table UPDATE. Valid values: true false
  @fac.current_sql	The current SQL statement.
  @fac.user_is_admin	Indicates whether the current user is a DMS administrator. Valid values: true false
  @fac.user_is_dba	Indicates whether the current user is a database administrator (DBA). Valid values: true false
  @fac.user_is_inst_dba	Indicates whether the current user is a DBA of the current database instance. Valid values: true false
  @fac.user_is_sec_admin	Indicates whether the current user is a security administrator. Valid values: true false
  @fac.sql_affected_rows	The number of rows to be affected by the current SQL statement. Note A COUNT operation will be performed, so please use with caution.
  @fac.sql_relate_table_store_size	The estimated total size of the table to be accessed by the current SQL statement. Unit: MB. Note This value is estimated based on the metadata that is obtained by DMS. The value is not an actual value.

• Action: The behavior that the system performs when an if condition is met. This action expresses the main purpose of a security rule, such as prohibiting ticket submission, selecting an approval process, allowing execution, or denying execution.

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

  • Each module on the Details page of a security rule set provides different actions for different checkpoints.

  Table 2. Actions provided by the SQL Console module

  Action	Description
  @act.reject_execute	Rejects the execution of the current SQL statement.
  @act.allow_execute	Allows the execution of the current SQL statement.
  @act.reject_sql_type_execute	To reject a specific type of SQL statement, you must specify the SQL subtype. For example: @act.reject_sql_type_execute 'UPDATE'.
  @act.allow_sql_type_execute	To allow the execution of a specific type of SQL, you must specify the exact SQL subtype. For example: @act.allow_sql_type_execute 'UPDATE'.
  @act.check_dml_sec_column_permission	Checks whether a user has permissions on sensitive fields. If the user does not have the required permissions, a DML statement for data change is not executed.
  @act.uncheck_dml_sec_column_permission	Does not check whether a user has permissions on sensitive fields.
  @act.check_sql_access_permission	Checks whether a user has specific permissions on the databases, tables, or fields that are involved in the SQL statements to be executed. For example, you can check whether a user has the permissions to query data or change data.
  @act.uncheck_sql_access_permission	Does not check whether a user has specific permissions on the databases, tables, or fields that are involved in the SQL statements to be executed.
  @act.enable_sec_column_mask	Masks sensitive fields in query result sets that are returned for SQL statements submitted by users who do not have permissions on the sensitive fields.
  @act.disable_sec_column_mask	Does not mask sensitive fields in query result sets that are returned for SQL statements submitted by users who do not have permissions on the sensitive fields.

SQL statements that can be executed in DMS

FAQ

• Q: Can I restrict the execution of SELECT * statements?

  A: Yes. This action requires an instance with security collaboration mode enabled.

  1. In the top navigation bar of the DMS console, choose Security and Disaster Recovery > Security Rules.

  2. On the Rule Sets tab, select the rule set that corresponds to your data engine type.

  3. Find the target rule set and click Edit in the Actions column to go to the Details page.

  4. In the left-side navigation pane, select SQL console. Under Checkpoints, select SQL execution count criteria.

  5. Click Add Rule and enter the following DSL script.

     if 
       @fac.sql_sub_type in ['SELECT'] 
       AND @fun.is_contain_str(@fac.current_sql,'*') 
     then 
       @act.reject_execute 'The SQL statement contains *. Please use an alternative method.' 
     end

• Q: How do I configure rules to restrict large queries in the SQL console?

  A: This action requires an instance with security collaboration mode enabled.

  1. In the top navigation bar of the DMS console, choose Security and Disaster Recovery > Security Rules.

  2. On the Rule Sets tab, select the rule set that corresponds to your data engine type.

  3. Find the target rule set and click Edit in the Actions column to go to the Details page.

  4. In the left-side navigation pane, select SQL console. In the search box, enter "full table scan" and click the Search button.

  5. Select the target rule from the list and click its Edit button to configure it.