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 how to configure security rules for operations in SQL Console. In this topic, ApsaraDB RDS for MySQL is used as an example.

Procedure

  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.
    Note For more information about how to create a security rule set, see Create security rules.
  4. On the Details page, click the SQL Console tab on the left.
  5. A security rule set is built in with a large number of configurations and rules. You can modify the configurations and change the status of rules based on your business requirements.
    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 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.

    The following flowchart shows how checkpoints work.

    Checkpoint flowchart - ApsaraDB RDS for MySQL.png
  6. If the built-in configurations and rules cannot meet your requirements, perform the following operations to create custom rules:
    1. Click Create Rule next to Actions.
    2. In the Create Rule - SQL Console dialog box, set the parameters and click Submit. The following table describes the parameters.
      Parameter Description
      Checkpoints The checkpoint for which you want to create the security rule.
      Template Database Optional. The rule template based on which you want to create the security rule. You can click Load from Template Database to load a template as required.
      Rule Name The name of the security rule.
      Rule DSL The domain-specific language (DSL) statement for the security rule. For more information about the DSL syntax, see DSL syntax for security rules.
      Note
      • You can use the factors, actions, functions, and operators that are provided on the right to write the DSL statement. For more information about factors and actions, see Factors and actions.
      • If you specify a rule template, you can modify the DSL statement provided in the template to generate a custom statement.
  7. Click the checkpoint that you specify for the created security rule. In the list that appears, find the security rule that you create and click Enable in the Actions column. In the Prompt message, click OK.
    Note By default, a rule is in the Disabled state after the rule is created.

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. Valid values:
    • true
    • false
    @fac.fulltable_update Indicates whether the current SQL statement updates a full table. 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