Data Management (DMS) allows you to configure security rules for operations in the SQL Console module. You can manage both relational databases and non-relational databases in SQL Console. This topic describes the security rules for operations in SQL Console. In this example, the security rules for ApsaraDB RDS for MySQL are used.

Checkpoints in SQL Console

Checkpoint Rule description
Basic Configuration Item Allows you to specify basic configurations such as the maximum number of rows that can be returned per query, whether result sets can be modified, and whether sensitive data can be calculated.
SQL Execution Quantity Criteria Allows you to limit the number of SQL statements that can be submitted at a time.
DQL SQL Criteria Allows you to set constraints on the execution of data query language (DQL) statements.
Other SQL Criteria Allows you to set constraints on the execution of multiple types of SQL statements. For example, you can enable the execution of unidentified SQL statements and disable the execution of statements that delete full tables.
Note DMS provides a large number of predefined configurations and rules for checkpoints. You can modify the configurations, change the status of rules, and customize security rules based on your business requirements. For more information, see Configure security rules.

The following flowchart shows how checkpoints work.

Checkpoint flowchart - ApsaraDB RDS for MySQL.png

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.
    • 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 Console tab
    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 SQL statements that can be executed in DMS.
    @fac.sql_sub_type The subcategory of an SQL statement. For more information, see SQL statements that can be executed in DMS.
    @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.fulltable_delete Indicates whether the current SQL statement deletes a full table.DELETE Valid values:
    • true
    • false
    @fac.fulltable_update Indicates whether the current SQL statement updates 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 This factor triggers COUNT operations. Use this factor 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.
  • 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, allow the execution of SQL statements, or reject the execution of SQL statements.
    • 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 Console tab
    Action Description
    @act.reject_execute Rejects the execution of the current SQL statement.
    @act.allow_execute Allows the current SQL statement to be executed.
    @act.reject_sql_type_execute Rejects the execution of a specific subcategory of SQL statement. You must specify a subcategory if you use this action. Example: @act.reject_sql_type_execute 'UPDATE'.
    @act.allow_sql_type_execute Allows a specific subcategory of SQL statement to be executed. You must specify a subcategory if you use this action. 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

Category Subcategory
DQL
  • SELECT
  • DESC
  • EXPLAIN
  • SHOW
DML
  • INSERT
  • INSERT_SELECT
  • REPLACE
  • REPLACE_INTO
  • UPDATE
  • DELETE
  • MERGE
DDL
  • DATABASE_OP
  • CREATE
  • CREATE_INDEX
  • CREATE_VIEW
  • CREATE_SEQUENCE
  • CREATE_TABLE
  • CREATE_SELECT
  • TRUNCATE
  • DROP_INDEX
  • DROP_VIEW
  • DROP_TABLE
  • RENAME
  • ALTER
  • ALTER_INDEX
  • ALTER_VIEW
  • ALTER_TABLE
  • ALTER_SEQUENCE
  • CREATE_FUNCTION
  • CREATE_PROCEDURE
  • ALTER_FUNCTION
  • ALTER_PROCEDURE
  • DROP_FUNCTION
  • DROP_PROCEDURE
DCL
  • GRANT
  • DECLARE
  • SET
  • ANALYZE
  • FLUSH
  • OPTIMIZE
  • KILL