All Products
Search
Document Center

Configurations of security rules

Last Updated: May 11, 2020

You can run SQL statements on the SQLConsole tab. DMS Enterprise allows you to set security rules to ensure that only SQL statements that are validated by these security rules can be run. By applying new security rules based on the domain-specific language (DSL), you can run SQL statements on the SQLConsole tab in the strict or loose mode. In the strict mode, you can forbid specified SQL statements as required. In the loose mode, which is similar to the terminal mode, you can run all SQL statements.

You can query and manage relational databases (NewSQL) and non-relational databases (NoSQL) on the SQLConsole tab. The definition and classification of security rules are different for relational and non-relational databases. NewSQL databases are regarded as relational databases because NewSQL is a class of systems for managing relational databases.

SQLConsole for relational databases

Configuration items

  • Maximum Number of Rows Returned for a Query: This configuration item specifies the maximum number of rows that can be returned for a query. It replaces the original item Maximum Number of SQL Statement Lines for a Query, which is discarded after you apply the new security rules.
  • Maximum Number of Rows that Contain Sensitive Fields Returned for a Query: This configuration item replaces the original item Maximum Number of SQL Statement Lines for a Query, which is discarded after you apply the new security rules.
  • Maximum Size for Full Table Scans by Using SQL Statements (in MB): This configuration item is valid only for MySQL and Oracle databases. Before running SQL statements for a full table scan, DMS Enterprise checks the size of the table to be scanned. If the size exceeds the value of this configuration item, DMS Enterprise terminates the SQL statements and prompts you to optimize them.
  • Disable Checking the Number of Rows to Be Affected by SQL Statements for Data Modification and Displaying the Confirm Prompt: This configuration item specifies whether DMS Enterprise evaluates the number of rows to be affected and displays a dialog box for you to confirm the number when you run SQL statements for data changes. By default, DMS Enterprise evaluates the number of rows and displays a confirmation dialog box.

Checkpoints

  • DMS Enterprise verifies all security rules, including configuration items and custom rules, before running SQL statements. DMS Enterprise sets the following checkpoints for verifying custom rules:

  • Checkpoint for the number of SQL statements: checks the number of SQL statements submitted at a time.

  • Statement criteria for DQL: checks the data query language (DQL) statements to be run.

  • Statement criteria for DML: checks the data management language (DML) statements to be run.

  • Statement criteria for DDL: checks the data definition language (DDL) statements to be run.

  • Statement criteria for DCL: checks the data control language (DCL) statements to be run.

  • Statement criteria for other SQL statements: checks other SQL statements to be run, such as high-risk SQL statements and SQL statements that cannot be recognized by DMS Enterprise. High-risk SQL statements may vary according to the enterprise and may contain some DML, DCL, and DDL statements.

  • Checkpoint for user permissions: checks the permissions for running SQL statements on the corresponding databases, tables, and columns.

  • Checkpoint for SQL statement performance: checks whether the performance of SQL statements can be guaranteed. For example, if the number of rows affected by DML statements or the statistical tablespace size affected by DDL statements exceeds the corresponding threshold, DMS Enterprise declines relevant SQL statements.

  • Checkpoint for SQL statements that cannot be recognized by DMS Enterprise: DMS Enterprise analyzes the syntax of SQL statements. For the SQL statements that can be recognized, DMS Enterprise checks whether the user has the permissions for running the SQL statements on the target database, table, and column. However, DMS Enterprise cannot recognize some complex SQL statements. This checkpoint allows users to customize rules to validate these SQL statements. Once validated, these SQL statements can be run. Note that this checkpoint invalidates other security rules, such as rules for SQL permissions, SQL statements of different categories, and SQL statement performance.

  • Checkpoint for SQL statements to be run on logical databases: checks the SQL statements to be run on logical databases. This checkpoint does not apply to physical databases.

Statement categories and sample statements

  • The following table lists the statement categories and sample statements that DMS Enterprise can recognize based on syntax analysis.
Category Statement
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
- 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

SQLConsole for MongoDB

Configuration items

  • Maximum Number of Rows Returned for a Query: This configuration item specifies the maximum number of rows that can be returned for a query. It replaces the original item Maximum Number of SQL Statement Lines for a Query, which is discarded after you apply the new security rules.

Checkpoints

  • DMS Enterprise verifies all security rules, including configuration items and custom rules, before running SQL statements. DMS Enterprise sets the following checkpoints for verifying custom rules:

  • Checkpoint for user permissions: checks the permissions for running SQL statements on the corresponding databases and collections.

  • Collection statement criteria: checks the statements to be run on MongoDB collections.

  • DB statement criteria: checks the statements to be run on MongoDB databases.

  • Cache query statement criteria: checks the statements to be run on the query cache.

  • User management statement criteria: checks the statements for managing users.

  • Role management statement criteria: checks the statements for managing roles.

  • Replication set statement criteria: checks the statements to be run on replication sets.

  • Sharding statement criteria: checks the statements to be run on MongoDB shards.

Statement categories and sample statements

  • The following table lists the statement categories and sample statements that DMS Enterprise can recognize based on syntax analysis.
Category Statement
Collection statements Query statements:
- aggregate, find, findOne, count, distinct, getIndexes, getShardDistribution, isCapped, stats, dataSize, storageSize, totalIndexSize, and totalSize
Data update statements:
- insert, save, findAndModify, remove, and update
Collection modification statements:
drop and renameCollection
Index modification statements:
- createIndex, createIndexes, dropIndexes, and reIndex
Other statement:
- validate
Database statements Read statements:
- commandHelp, currentOp, getCollectionInfos, getCollectionNames, getLastError, getLastErrorObj, getLogComponents, getPrevError, getProfilingStatus, getReplicationInfo, getSiblingDB, help, isMaster, listCommands, printCollectionStats, printReplicationInfo, version, serverBuildInfo, serverStatus, and stats
Statement for creating a collection:
- createCollection
High-risk statements:
- dropDatabase, fsyncLock, fsyncUnlock, killOp, repairDatabase, resetError, and runCommand
Query cache statements Read statements:
- getPlanCache, getPlansByQuery, and listQueryShapes
Write statement:
- clearPlansByQuery
User management statements Read statements:
- getUser and getUsers
Modification statements:
- createUser, changeUserPassword, dropUser, dropAllUsers, grantRolesToUser, revokeRolesFromUser, and updateUser
Role management statements
Read statements:
- getRole and getRoles
Modification statements:
- createRole, dropRole, dropAllRoles, grantPrivilegesToRole, revokePrivilegesFromRole, revokeRolesFromRole, and updateRole
Replication set statements
- help, printReplicationInfo, status, and conf
Sharding statements - getBalancerState and isBalancerRunning

SQLConsole for Redis

Checkpoints

  • DMS Enterprise verifies all security rules, including configuration items and custom rules, before running SQL statements. DMS Enterprise sets the following checkpoints for verifying custom rules:

  • Checkpoint for user permissions: checks the permissions for running SQL statements on the corresponding databases and collections.

  • Statement criteria for KEYS: checks the statements to be run on keys.

  • Statement criteria for STRINGS: checks the statements to be run on string values stored at keys.

  • Statement criteria for LISTS: checks the statements to be run on lists.

  • Statement criteria for SETS: checks the statements to be run on sets.

  • Statement criteria for SORTED SETS: checks the statements to be run on sorted sets.

  • Statement criteria for HASHES: checks the statements to be run on hashes.

  • Statement criteria for other Redis statements: checks Redis statements other than the above.

Statement categories and sample statements

  • The following table lists the statement categories and sample statements that DMS Enterprise can recognize based on syntax analysis.
Category Statement
KEYS Read statements:
- EXISTS, TTL, PTTL, RANDOMKEY, TYPE, SCAN, and OBJECTS
Write statements:
- DEL, DUMP, EXPIRE, EXPIREART, MOVE, PERSIST, PEXPIRE, PEXPIREAT, RENAME, RENAMENX, RESTORE, SORT, TOUCH, UNLIMK, WAIT, and MIGRATE
STRINGS
Read statements:
- GET, GETRANGE, BITCOUNT, GETBIT, MGET, STRLEN, and BITOPS
Write statements:
- APPEND, BITFIELD, BITOP, DECR, DECRBY, GETSET, INCR, INCRBY, INCRBYFLOAT, MSET, MSETNX, PSETEX, SET, and SETNX
LISTS Read statements:
- LINDEX, LLEN, and LRANGE
Write statements:
- BLPOP, BRPOP, BRPOPLPUSH, LINSERT, LPOP, LPUSH, LPUSHX, LREM, LSET, LTRIM, RTOP, RPOPLPUSH, RPUSH, and RPUSHX
SETS
Read statements:
- SCARD, SISMEMBER, SRANDMEMBER, and SSCAN
Write statements:
- SADD, SMOVE, SPOP, and SREM
SORTED SETS Read statements:
- ZCARD, ZCOUNT, ZLEXCOUNT, ZRANGE, ZRANGEBYLEX, ZRANGEBYSCORE, ZRANK, ZREVRNGE, ZREVRANGEBYLEX, ZREVRANGEBYSCORE, ZREVRANK, ZSCAN, and ZSCORE
Write statements:
- ZADD, ZINCRBY, ZINTERSTORE, ZPOPMAX, ZPOPMIN, ZREM, ZUNIONSTORE, BZPOPMIN, and BZPOPMAX
HASHES
Read statements:
- HEXISTS, HGET, HLEN, HMGET, HSCAN, and HSTRLEN
Write statements:
- HDEL, HINCRBY, HINCRBYFLOAT, HMESET, HSET, and HSETNX
Other statements To be supplemented

Security rules for data changes

You can run SQL statements for data changes on the Data Changes tab. DMS Enterprise allows you to set security rules when you submit and approve tickets for changing data. A ticket can be submitted only after the corresponding SQL statements for data changes are validated by the security rules. At the same time, you can specify the risk level for a ticket so that the ticket is directed to the approval process that is designed for the specified risk level.

By applying new security rules based on DSL, you can run SQL statements on the Data Changes tab in the strict or loose mode. In the strict mode, you can forbid specified SQL statements as required. In the loose mode, which is similar to the terminal mode, you can run all SQL statements.

Configuration items

Risk levels

  • You can set risk levels based on the type and scenario of data changes. Data changes of different risk levels are directed to different approval processes.
    Currently, DMS Enterprise offers four default risk levels: low, medium, high, and critical.
    Risk levels are used in risk recognition and ticket approval.

Checkpoints

  • DMS Enterprise verifies all security rules, including configuration items and custom rules, before running SQL statements. DMS Enterprise sets the following checkpoints for verifying custom rules:

Rules for submitting SQL statements

  • These rules are used to restrict the category of SQL statements. For example, you can specify that only DML statements are allowed. For more information about SQL statement categories, see preceding sections about SQLConsole.

Rules for risk recognition

  • Tickets that conform to the submission rules can be submitted for approval. Before directing a ticket to the approval process, DMS Enterprise recognizes the risk level for running the SQL statements by using preset risk recognition rules. When you set risk levels, you can take the database environment, number of rows that may be affected by the SQL statements, and SQL statement category into consideration.
    For example, you can set the action of running DML statements in the production environment as a medium risk, and the action of deleting fields in the production environment as a high risk.

Rules for ticket approval

  • After recognizing the risk level for running the SQL statements for data changes, DMS Enterprise directs the ticket based on the rules for ticket approval. These rules specify the approval process for tickets of different risk levels.
    Note that if a ticket triggers risks of multiple levels, DMS Enterprise applies the approval process for the highest risk level to this ticket.