All Products
Search
Document Center

Data Management:SQL Console for relational databases

最終更新日:Jan 12, 2024

Data Management (DMS) allows you to configure security rules for operations in the SQL Console module. You can manage 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

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 allow the execution of unidentified SQL statements and reject the execution of statements that delete full tables.

SQL Permission Criteria

Allows you to set constraints on the execution of SQL statements based on permissions on databases, tables, sensitive columns, and rows.

SQL Execution Performance Criteria

Allows you to reject the execution of DDL statements if the size of tablespace involved exceeds the upper limit or reject the execution of DML statements if the number of rows affected exceeds the upper limit.

Exception Recognition Criteria of Database and Table Column Permissions

Allows you to specify whether to continue or reject the execution of SQL statements such as DQL, DML, DDL, or data control language (DCL) statements if parsing exceptions occur.

SQL Execution Criteria in Logical Databases

Allows you to set constraints on the execution of different types of SQL statements.

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 the Configure security rules section of the "Manage security rules" topic.

The following flowchart shows how checkpoints work.

image

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

    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 the execution of a specific subcategory of SQL statement. 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

DML

  • INSERT

  • INSERT_SELECT

  • SELECT_INTO

  • MULTI_INSERT

  • REPLACE

  • REPLACE_INTO

  • UPDATE

  • DELETE

  • MERGE

  • REMOVE

  • MSCK_REPAIR

  • REFRESH_MATERIALIZED_VIEW

  • BEGIN

  • START_TRANSACTION

  • COMMIT

  • ROLLBACK

  • SAVEPOINT

  • RELEASE_SAVEPOINT

  • SET

  • ADD_EDGE

  • ADD_VERTEX

  • SET_PROPERTY

  • GDB_DROP

  • INSERT_MANY

  • INSERT_ONE

  • DELETE_MANY

  • DELETE_ONE

  • UPDATE_MANY

  • UPDATE_ONE

  • WLM_ADD_RULE

DDL

  • CREATE

  • CREATE_SCHEMA

  • CREATE_INDEX

  • CREATE_VIEW

  • CREATE_SEQUENCE

  • CREATE_TABLE

  • CREATE_TABLEGROUP

  • CREATE_PACKAGE

  • CREATE_SELECT

  • TRUNCATE

  • DROP

  • DROP_SCHEMA

  • DROP_INDEX

  • DROP_VIEW

  • DROP_TABLE

  • DROP_TABLEGROUP

  • DROP_SEQUENCE

  • RENAME

  • ALTER_TABLE_DROP_COLUMN

  • ALTER_TABLE_CHARACTER_COLLATE

  • ALTER_TABLE_AUTOINCREMENT

  • ALTER

  • ALTER_SCHEMA

  • ALTER_INDEX

  • ALTER_VIEW

  • ALTER_TABLE

  • ALTER_TABLEGROUP

  • ALTER_SEQUENCE

  • CREATE_FUNCTION

  • ALTER_FUNCTION

  • DROP_FUNCTION

  • CREATE_PROCEDURE

  • ALTER_PROCEDURE

  • DROP_PROCEDURE

  • MONGO_CREATE_INDEX

  • MONGO_DROP_INDEX

  • CREATE_DATABASE

  • ALTER_DATABASE

  • DROP_DATABASE

  • CREATE_USER

  • ALTER_USER

  • DROP_USER

  • CREATE_ROLE

  • DROP_ROLE

  • EXEC

  • CALL_PROCEDURE

  • CREATE_TRIGGER

  • ALTER_TRIGGER

  • DROP_TRIGGER

  • CREATE_EVENT

  • ALTER_EVENT

  • DROP_EVENT

  • ROLLBACK_DDL

  • CANCEL_DDL

  • VACUUM

  • SUBMIT_JOB

  • CANCEL_JOB

  • BUILD_TABLE

  • IF

DQL

  • SELECT

  • DESC

  • EXPLAIN

  • SHOW_INDEX

  • SHOW

  • CHECK_TABLE

  • SHOW_DDL

  • LIST

  • GDB_QUERY

  • CHECKSUM

  • SELECT_PG_TERMINATION_BACKEND

  • WLM_LIST_RULE

DCL

  • GRANT

  • DECLARE

  • SET

  • ANALYZE

  • FLUSH

  • OPTIMIZE

  • KILL

  • RECOVER_DDL

  • REMOVE_DDL

  • SELECT_PG_TERMINATION_BACKEND