All Products
Search
Document Center

SQLConsole for MongoDB

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 command execution in MongoDB databases.

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.

Checkpoints

  • User Permission Validation: Under this checkpoint, you can specify whether to check specific users’ permissions when they submit commands.

For example, this checkpoint checks whether a user has corresponding collection permissions.

  • Collection Statement Criteria: Under this checkpoint, you can set constraints on collection commands.
  • DB Statement Criteria: Under this checkpoint, you can set constraints on database commands.
  • Cache Query Statement Criteria: Under this checkpoint, you can set constraints on commands related to the query plan cache.
  • User Management Statement Criteria: Under this checkpoint, you can set constraints on user management commands.
  • Role Management Statement Criteria: Under this checkpoint, you can set constraints on role management commands.
  • Replication Set Statement Criteria: Under this checkpoint, you can set constraints on replica set commands.
  • Sharding Statement Criteria: Under this checkpoint, you can set constraints on sharding commands.

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

2

Commands that can be run in DMS

Category Subcategory and enumerated value
Collection commands Query commands:
  • aggregate
  • find
  • findOne
  • count
  • distinct
  • getIndexes
  • getShardDistribution
  • isCapped
  • stats
  • dataSize
  • storageSize
  • totalIndexSize
  • totalSize
Data update commands:
  • insert
  • save
  • findAndModify
  • remove
  • update
Collection modification commands:
  • drop
  • renameCollection
Index modification commands:
  • createIndex
  • createIndexes
  • dropIndexes
  • reIndex
Other command:
  • validate
Database commands Database query commands:
  • commandHelp
  • currentOp
  • getCollectionInfos
  • getCollectionNames
  • getLastError
  • getLastErrorObj
  • getLogComponents
  • getPrevError
  • getProfilingStatus
  • getReplicationInfo
  • getSiblingDB
  • help
  • isMaster
  • listCommands
  • printCollectionStats
  • printReplicationInfo
  • version
  • serverBuildInfo
  • serverStatus,stats
Collection creation command:
  • createCollection
High-risk commands:
  • dropDatabase
  • fsyncLock
  • fsyncUnlock
  • killOp
  • repairDatabase
  • resetError
  • runCommand
Commands related to the query plan cache Read commands:
  • getPlanCache
  • getPlansByQuery
  • listQueryShapes
Write command:
  • clearPlansByQuery
User management commands User query commands:
  • getUser
  • getUsers
User modification commands:
  • createUser
  • changeUserPassword
  • dropUser
  • dropAllUsers
  • grantRolesToUser
  • revokeRolesFromUser
  • updateUser
Role management commands Role query commands:
  • getRole
  • getRoles
Role modification commands:
  • createRole
  • dropRole
  • dropAllRoles
  • grantPrivilegesToRole
  • revokePrivilegesFromRole
  • revokeRolesFromRole
  • updateRole
Replica set commands
  • help
  • printReplicationInfo
  • status
  • conf
Sharding commands
  • getBalancerState
  • isBalancerRunning

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 commands 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_sub_type The subcategory of the command. For more information about valid values, see Commands that can be run in DMS.
    @fac.env_type The 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.current_sql The current command.
    @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

    • 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 run the current command.
      @act.allow_execute Allows the current command to be run.
      @act.reject_sql_type_execute Rejects the requests to run certain types of commands. You need to specify a command subcategory after the action name. Example: @act.reject_sql_type_execute 'UPDATE'.
      @act.allow_sql_type_execute Allows certain types of commands to be run. You need to specify a command category after the action name.

      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
      User Permission Validation
      Specifies that DMS must validate common users’ permissions when they submit commands.
      Collection Statement Criteria
      Specified that data update commands can be run in the production environment.
      Specified that collection modification commands can be run in the production environment.
      Specified that index modification commands can be run in the production environment.
      Specified that other high-risk commands to can run in the production environment.
      Specified that all collection query commands can be run.
      DB Statement Criteria
      Specifies that collections can be created in the production environment.
      Specifies that other high-risk database commands can be run in the development environment.
      Specifies that other high-risk database commands can be run in the production environment.
      Specifies that all database query commands can be run.
      Cache Query Statement Criteria
      Specifies that all write commands related to the query plan cache can be run.
      Specifies that all read commands related to the query plan cache can be run.
      User Management Statement Criteria
      Specifies that user query commands can be run.
      Specifies that user modification commands can be run.
      Role Management Statement Criteria
      Specifies that role query commands can be run.
      Specifies that role modification commands can be run in the development environment.
      Specifies that role modification commands can be run in the production environment.
      Replication Set Statement Criteria
      Specifies that replica set query commands can be run.
      Sharding Statement Criteria
      Specifies that sharding query commands can be run.

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

      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.