All Products
Search
Document Center

SQLConsole for relational databases

Last Updated: Jul 07, 2020

Data Management Service (DMS) provides the SQLConsole for you to manage relational databases and NoSQL databases. To control SQL execution for these two types of databases, you need to set different types of security rules that include different items. This topic describes security rules for controlling SQL execution in relational databases.

Default security rules

  • Constraints on SQL types: No constraints are imposed on SQL query statements. Data manipulation language (DML) statements, data definition language (DDL) statements, data control language (DCL) statements, and SQL statements that cannot be identified by DMS are all blocked by default. To execute DML, DDL, or DCL statements in the SQLConsole, you need to set and enable corresponding security rules.
  • Constraints on database, table, and column permissions: By default, users can perform operations on databases, tables, and columns without permission validation. To enable permission validation, you need to set and enable security rules under the SQL Permission Criteria checkpoint.

Basic configuration items

  • Maximum number of returned rows per query: the maximum number of rows that can be returned for a query.

This item replaces the Maximum number of rows returned for each SQL query configuration item on the Configuration page.

  • Maximum number of rows returned for a single query with sensitive column conditions: the maximum number of rows that can be returned for a query that contains sensitive field conditions.

This item replaces the Maximum number of rows returned for each SQL query with sensitive column conditions configuration item on the Configuration page.

  • Limit the maximum allowed SQL full table scan (MB): the maximum size of data that can be scanned. This item can only be configured for MySQL and Oracle databases. Before an SQL statement is executed, this item checks the execution plan. If the size of the table to be scanned exceeds this threshold, the SQL statement will not be executed.
  • Turn off the execution of change SQL validation affects the number of rows and prompts: specifies whether to check the number of rows to be affected and display a prompt before executing an SQL statement to change data. By default, this item is disabled, indicating that DMS will check the number of rows to be affected and display a prompt before executing an SQL statement to change data.
  • How many rows does result set page support: the maximum number of rows that can be returned in the query result set in the SQLConsole.
  • Does the result set support paging: specifies whether the query result set can be displayed on multiple pages in the SQLConsole.
  • Does the result set support editing: specifies whether the query result set can be edited in the SQLConsole.

Checkpoints

  • SQL Execution Quantity Criteria: Under this checkpoint, you can limit the number of SQL statements that can be submitted at a time.
  • DQL SQL Criteria: Under this checkpoint, you can set constraints on DQL statements.
  • Other SQL Criteria: Under this checkpoint, you can set constraints on multiple types of SQL statements.

For example, you can set constraints on high-risk SQL statements. Different enterprises may define different high-risk SQL statements, which may include certain types of DML, DCL, and DDL statements. You can also set constraints on SQL statements that cannot be identified by DMS.

  • SQL Permission Criteria: Under this checkpoint, you can set constraints on the execution of SQL statements from the aspect of permissions.

For example, this checkpoint checks whether a user has corresponding database, table, and column permissions.

  • SQL Execution Performance Criteria: Under this checkpoint, you can set constraints on the execution of SQL statements from the aspect of performance.

For example, you can specify that a DML statement will not be executed if the number of rows to be affected by the statement exceeds a threshold, or that a DDL statement will not be executed if the size of the corresponding table exceeds a threshold.

  • Exception Recognition Criteria of Database and Table Column Permissions: After a user submits SQL statements in the SQLConsole, DMS parses the SQL statements and checks whether the user has permissions on corresponding databases, tables, and columns. However, DMS cannot parse certain complex SQL statements. Therefore, you can set security rules under the Exception Recognition Criteria of Database and Table Column Permissions checkpoint to ensure that even if exceptions occur when DMS parses complex SQL statements, these statements can still be executed.

Note: If you set and enable security rules under the Exception Recognition Criteria of Database and Table Column Permissions checkpoint, security rules under the SQL Permission Criteria, SQL Criteria Validation, and SQL Execution Performance Criteria checkpoints will be automatically disabled.

  • SQL Execution Criteria in Logical Databases: This checkpoint is reserved for logical databases and not suitable for physical databases.

You can use the default rules provided by DMS, or set custom rules as required. For more information, see Procedure of creating a security rule.

How checkpoints work

How checkpoints work

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

Factors and actions

  • Factor: A factor is a system built-in variable that is used to obtain the context to be validated by security rules, such as the subcategories of SQL statements and the number of rows in which data is affected. A factor name starts with @fac., appended with the display name of the factor type. Each module of the Security Rules page offers different factors for different checkpoints. The following table describes the supported factors in the SQLConole module.

    Factor Description
    @fac.sql_count The number of SQL statements that are submitted at a time.
    @fac.select_sql_count The number of SELECT statements among the SQL statements that are submitted a time.
    @fac.dml_sql_count The number of DML statements among the SQL statements that are submitted a time.
    @fac.sql_type The category of the SQL statement. For more information about valid values, see SQL statements that can be executed in DMS.
    @fac.sql_sub_type The subcategory of the SQL statement. For more information about valid values, see SQL statements that can be executed in DMS.
    @fac.env_typeThe type of the environment. The value is the display name of the environment type, such as DEV and PRODUCT. For more information, see Change the environment type of an instance.
    @fac.fulltable_deleteA boolean value that indicates whether the current SQL statement will DELETE a full table. Valid values:
    • true
    • false
    @fac.fulltable_update A boolean value that indicates whether the current SQL statement will UPDATE a full table. Valid values:
    • true
    • false
    @fac.current_sql The current SQL statement.
    @fac.user_is_admin A boolean value that indicates whether the current user is a DMS administrator. Valid values:
    • true
    • false
    @fac.user_is_dba A boolean value that indicates whether the current user is a database administrator (DBA). Valid values:
    • true
    • false
    @fac.user_is_inst_dba A boolean value that indicates whether the current user is the DBA of the current instance. Valid values:
    • true
    • false
    @fac.user_is_sec_admin A boolean value that 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. This factor will trigger 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. This value is estimated based on the metadata obtained by DMS. It is not an actual value.
  • Action: An action is the operation that the system performs after the conditions specified in the if statement are met. For example, the system can perform the relevant action to forbid the submission of a ticket, select an approval process, approve a ticket, or reject a ticket. Actions show the purpose of setting security rules. An action name starts with @act., appended with the display name of the action type. Each module of the Security Rules page offers different actions for different checkpoints. The following table describes the supported actions in the SQLConsole module.

    Action Description
    @act.reject_execute Rejects the request to execute the current SQL statement.
    @act.allow_execute Allows the current SQL statement to be executed.
    @act.reject_sql_type_execute Rejects the requests to execute certain types of SQL statements. You need to specify an SQL subcategory after the action name. Example: @act.reject_sql_type_execute 'UPDATE'.
    @act.allow_sql_type_execute Allows certain types of SQL statements to be executed. You need to specify an SQL subcategory after the action name. Example: @act.allow_sql_type_execute 'UPDATE'.
    @act.check_dml_sec_column_permission Checks whether a user has corresponding permissions on sensitive fields. If the user does not have the permissions, the DML change statement will not be executed.
    @act.uncheck_dml_sec_column_permission Does not check whether a user has corresponding permissions on sensitive fields.
    @act.check_sql_access_permission Checks whether a user has corresponding permissions, such as query and change permissions, on the databases, tables, and columns involved in the SQL statements to be executed.
    @act.uncheck_sql_access_permission Does not check whether a user has corresponding permissions on the objects involved in the SQL statements to be executed.
    @act.enable_sec_column_mask De-identifies sensitive fields in query result sets returned for SQL statements submitted by users who do not have permissions on the sensitive fields.
    @act.disable_sec_column_mask Does not de-identify sensitive fields in query result sets returned for SQL statements submitted by users who do not have permissions on the sensitive fields.

Templates of security rules

DMS provides you with various system built-in templates of security rules. You can directly use the templates or modify the templates based on your business requirements. The following table describes the supported rule templates in the SQLConsole module.

Checkpoint Feature of template
SQL Execution Quantity Criteria Specifies the maximum number of SQL statements that can be executed at a time.
Specifies the maximum number of SELECT statements that can be executed at a time.
Specifies the maximum number of DML statements that can be executed at a time.
DQL SQL Criteria N/A
Other SQL Criteria Specifies that unidentified SQL statements can be executed.
Specifies that SQL statements for updating full tables cannot be executed.
Specifies that SQL statements for deleting full tables cannot be executed.
SQL Permission Criteria Determines whether to validate users’ permissions on databases and tables.
Determines whether to de-identify sensitive fields in query result sets returned for SQL statements submitted by users who do not have permissions for the sensitive fields.
Specifies that users’ permissions on sensitive fields must be validated before data changes are made to sensitive fields.
Specifies that users’ permissions on rows must be validated.
SQL Execution Performance Criteria Specifies that a DML statement cannot be executed if the number of rows to be affected by the statement exceeds a threshold.
Specifies that a DDL statement cannot be executed if the size of the table to be accessed by the statement exceeds a threshold.
Exception Recognition Criteria of Database and Table Column Permissions Specifies that DQL statements can continue to be executed after exceptions occur when the statements are parsed. This will disable the security rules under the SQL Permission Criteria checkpoint.
Specifies that DML statements can continue to be executed after exceptions occur when the statements are parsed. This will disable the security rules under the SQL Permission Criteria checkpoint.
Specifies that DDL statements can continue to be executed after exceptions occur when the statements are parsed. This will disable the security rules under the SQL Permission Criteria checkpoint.
Specifies that DCL statements can continue to be executed after exceptions occur when the statements are parsed. This will disable the security rules under the SQL Permission Criteria checkpoint.
Specifies that unidentified SQL statements can continue to be executed after exceptions occur when the statements are parsed. This will disable the security rules under the SQL Permission Criteria checkpoint.
SQL Execution Criteria in Logical Databases N/A

Procedure of creating a security rule

  1. Log on to the DMS console.

  2. In the top navigation bar, choose System Management > Security > Security Rules.
    Security rules3

  3. On the Security Rules page that appears, find the target rule set and click Edit in the Actions column.

  4. On the Details page that appears, click the SQLConsole tab.

  5. On the SQLConsole tab, click Create Rule next to Actions.
    Security rules-MySQL-1

  6. In the Create Rule - SQLConsole dialog box that appears, set the parameters as required. The following table describes the parameters.

    Parameter Description
    Checkpoints (Required)The checkpoint under which you want to create the security rule. For more information about checkpoints, see Checkpoints.
    Template Database (Optional) The template based on which you want to create the security rule. DMS provides you with various system built-in templates of security rules. After you select a checkpoint from the Checkpoints drop-down list, you can click Load from Template Database to select a template. For more information about the available templates, see Templates of security rules.
    Rule Name (Required) The name of the security rule. If you load a security rule from a template, the rule name is automatically filled in.
    Rule DSL (Required) The domain-specific language (DSL) statement used to set the security rule. For more information, see DSL syntax for security rules. If you load a security rule from a template, the statement is automatically filled in.
  7. Click Submit.

  8. Find the created security rule and click Enable in the Actions column. By default, the created security rule is in the Disabled state.

  9. In the message that appears, click OK.