SQL Console Security Rules Overview for Relational Databases - Data Management

The SQL Console in Data Management (DMS) evaluates each SQL statement against a series of security checkpoints before execution. These checkpoints control which SQL statements run, who can run them, and under what conditions—giving you fine-grained control over database access and operations. This topic uses ApsaraDB RDS for MySQL as an example.

How it works

When you submit SQL statements in SQL Console, DMS evaluates each statement sequentially against predefined checkpoints. At each checkpoint, DMS reads factors (prefixed @fac.) to assess the runtime context—such as the SQL type, the number of affected rows, or the current user's role—and then applies an action (prefixed @act.) based on the configured rule conditions.

Checkpoints

DMS evaluates SQL statements against the following checkpoints in order. Each checkpoint has its own configurable rules.

Checkpoint	What it controls
Basic Configuration Item	Maximum rows returned per query, whether result sets can be modified, and whether sensitive data can be included in calculations
SQL Execution Quantity Criteria	Maximum number of SQL statements that can be submitted at a time
DQL SQL Criteria	Execution constraints for data query language (DQL) statements
Other SQL Criteria	Execution constraints for multiple SQL types—for example, allowing unrecognized SQL and blocking full-table deletions
SQL Permission Criteria	Execution constraints based on permissions on databases, tables, sensitive columns, and rows
SQL Execution Performance Criteria	Blocks DDL statements when the affected tablespace exceeds a size limit, or DML statements when the number of affected rows exceeds a limit
Exception Recognition Criteria of Database and Table Column Permissions	Determines whether DQL, DML, DDL, or data control language (DCL) statements continue or are rejected when parsing exceptions occur
SQL Execution Criteria in Logical Databases	Execution constraints for SQL statements run against logical databases

DMS provides predefined configurations and rules for each checkpoint. To customize them, see the Configure security rules section of the Manage security rules topic.

Factors and actions

Factors

A factor is a predefined variable that DMS exposes for use in security rule conditions. Factors capture runtime context—such as whether a statement affects a full table, or whether the current user is a DBA.

Factor names use the format @fac.<display-name>.

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

Factors provided by SQL Console

Factor	Description
`@fac.sql_count`	Number of SQL statements submitted at a time
`@fac.select_sql_count`	Number of DQL statements among the submitted SQL statements
`@fac.dml_sql_count`	Number of data manipulation language (DML) statements among the submitted SQL statements
`@fac.sql_type`	Category of the SQL statement. For valid values, see SQL statement types.
`@fac.sql_sub_type`	Subcategory of the SQL statement. For valid values, see SQL statement types.
`@fac.env_type`	Environment type of the instance, displayed as the environment name such as `DEV` or `PRODUCT`. For details, see Change the environment type of an instance.
`@fac.fulltable_delete`	Whether the statement deletes a full table. Valid values: `true`, `false`
`@fac.fulltable_update`	Whether the statement updates a full table. Valid values: `true`, `false`
`@fac.current_sql`	The current SQL statement
`@fac.user_is_admin`	Whether the current user is a DMS administrator. Valid values: `true`, `false`
`@fac.user_is_dba`	Whether the current user is a database administrator (DBA). Valid values: `true`, `false`
`@fac.user_is_inst_dba`	Whether the current user is a DBA of the current database instance. Valid values: `true`, `false`
`@fac.user_is_sec_admin`	Whether the current user is a security administrator. Valid values: `true`, `false`
`@fac.sql_affected_rows`	Number of rows the current statement will affect. Note This factor triggers `COUNT` operations. Use this factor with caution.
`@fac.sql_relate_table_store_size`	Estimated total size of the tables accessed by the current statement, in MB. This value is estimated based on DMS metadata and is not an actual value.

Actions

An action defines what DMS does when a rule's IF condition is met—for example, allowing or rejecting a statement, or checking whether the user has permissions on sensitive columns.

Action names use the format @act.<display-name>.

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

Actions provided by SQL Console

Action	Description
`@act.reject_execute`	Rejects the current SQL statement
`@act.allow_execute`	Allows the current SQL statement
`@act.reject_sql_type_execute`	Rejects a specific subcategory of SQL statement. Specify the subcategory as an argument. Example: `@act.reject_sql_type_execute 'UPDATE'`
`@act.allow_sql_type_execute`	Allows a specific subcategory of SQL statement. Specify the subcategory as an argument. Example: `@act.allow_sql_type_execute 'UPDATE'`
`@act.check_dml_sec_column_permission`	Checks whether the user has permissions on sensitive columns. If not, the DML statement is not executed.
`@act.uncheck_dml_sec_column_permission`	Skips the sensitive column permission check for DML statements
`@act.check_sql_access_permission`	Checks whether the user has the required permissions on the databases, tables, or columns involved in the statement—for example, read or write permissions
`@act.uncheck_sql_access_permission`	Skips the access permission check
`@act.enable_sec_column_mask`	Masks sensitive columns in query result sets for users who lack permissions on those columns
`@act.disable_sec_column_mask`	Does not mask sensitive columns in query result sets for users who do not have permissions on those columns

SQL statement types

DMS classifies SQL statements into four categories. The @fac.sql_type factor returns the category, and @fac.sql_sub_type returns the specific statement type listed below.

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

Data query language (DQL) statements retrieve data without modifying it.

SELECT, DESC, EXPLAIN, SHOW_INDEX, SHOW, CHECK_TABLE, SHOW_DDL, LIST, GDB_QUERY, CHECKSUM, SELECT_PG_TERMINATION_BACKEND, WLM_LIST_RULE

DCL

Data control language (DCL) statements manage database access and session state.

GRANT, DECLARE, SET, ANALYZE, FLUSH, OPTIMIZE, KILL, RECOVER_DDL, REMOVE_DDL, SELECT_PG_TERMINATION_BACKEND

FAQ

Q: Can I restrict the execution of 'select *' SQL statements?
A: This applies only to instances that have the Security Collaboration pattern enabled.
1. In the top menu bar of DMS, choose Security and Disaster Recovery (DBS) > Security Rules.
2. On the Specification Management tab, select the ruleset that corresponds to your data engine type.
3. Find the destination rule, and in the Actions column, click the Edit button to open the Details page.
4. In the navigation pane on the left, choose SQL Console. In the Checkpoint section, select SQL execution quantity specifications.
5. Click Add Rule and enter the DSL script for the configuration.
```
if 
  @fac.sql_sub_type in ['SELECT'] 
  AND @fun.is_contain_str(@fac.current_sql,'*') 
then 
  @act.reject_execute 'The SQL statement contains *. Use an alternative method.' 
end
```
Q: How do I set limits for large queries in the DMS SQL Console?
A: This action requires an instance with Security Collaboration mode enabled.
1. In the top menu bar of DMS, choose Security and Disaster Recovery (DBS) > Security Rules.
2. On the Specification Management tab, select the ruleset that corresponds to your data engine type.
3. Find the destination rule, click Edit in the Operation column to go to the Details page.
4. In the navigation pane on the left, choose SQL Console. In the search bar, enter "full table scan" and click the Search button.
5. In the list that appears, select the destination rule and click the edit button to configure it.