Data Management (DMS) provides a domain-specific language (DSL) to describe security rules. You can use the DSL syntax to define security rules. This allows you to define database development standards based on your business requirements.

Overview

The DSL syntax for a security rule is an IF-THEN or IF-THEN-ELSE statement that consists of one or more conditions and actions. The following format is the basic format of a security rule:

if
    Condition 1
then
    Action 1
end
Note If Condition 1 is met, DMS performs Action 1.

A security rule can contain more than one condition. After you specify an IF condition, you can use ELSEIF to specify more conditions. A security rule must contain an IF condition, and can contain zero or more ELSEIF conditions and zero or one ELSE clause.

if
    Condition 1
then
    Action 1
elseif
    Condition 2
then
    Action 2
[else Action 3]
end
Note If Condition 1 is met, DMS performs Action 1. If Condition 2 is met, DMS performs Action 2. If Condition 1 and Condition 2 are not met, DMS performs Action 3. If the `ELSE Action 3` clause is omitted and Condition 1 and Condition 2 are not met, DMS performs no action.

DSL syntax

  • Conditional clauses

    DMS uses conditional clauses to evaluate whether to perform actions. The result of a conditional clause is true or false. A conditional clause consists of one or more connectors, operators, and factors. Connectors are AND and OR. Factors are predefined system variables. The following examples are valid conditional clauses:

    true                    // This is the simplest conditional clause. The result is true. 
    1 > 0
    1 > 0 and 2 > 1
    1 <= 0 or 1 == 1
    Note The results of these sample conditional clauses are all true.
    • Connectors

      Connectors are AND and OR. The AND connector has higher priority than the OR connector. Both the connectors have lower priority than operators.

      For example, a conditional clause is 1 <= 0 or 1 == 1. DMS evaluates the result of the 1 <= 0 expression and then the result of the 1 == 1 expression. After that, DMS evaluates the result of the OR expression based on the preceding results.

    • Operators

      Operators are used to connect factors and constants to perform logical operations. The following table describes the operators that are supported by DMS.

      Operator Description Example
      == Evaluates whether a value is equal to another value. 1 == 1
      != Evaluates whether a value is not equal to another value. 1 != 2
      > Evaluates whether a value is greater than another value. 1 > 2
      >= Evaluates whether a value is greater than or equal to another value. 1 >= 2
      < Evaluates whether a value is less than another value. 1 < 2
      <= Evaluates whether a value is less than or equal to another value. 1 <= 2
      in Evaluates whether a value belongs to an array of values. 'a' in ['a', 'b', 'c']
      not in Evaluates whether a value does not belong to an array of values. 'a' not in ['a', 'b', 'c']
      matchs Evaluates whether a string matches a regular expression. 'idx_aa' matchs 'idx_\\w+'
      not matchs Evaluates whether a string does not match a regular expression. 'idx_aa' not matchs 'idx_\\w+'
      isBlank Evaluates whether a value is empty. '' isBlank
      isNotBlank Evaluates whether a value is not empty. '' isNotBlank

      If you need to use a backslash (\) in a regular expression, you must add another backslash (\) as an escape character before the backslash that you want to use. For example, if you want to write the idx_\w+ expression, you must enter idx_\\w+.

      Note Operators have default priorities. However, you can specify the priority of expressions based on your needs. If a conditional clause contains nested expressions, we recommend that you enclose the expressions with higher priority in parentheses (). For example, a conditional clause is 1 <= 2 == true. To specify the priority, you can change the clause to (1 <= 2) == true. DMS first evaluates the result of the 1 <= 2 expression in the parentheses.
    • Factors

      A factor is a predefined variable in DMS. You can use factors to obtain the context to be validated by security rules. The context includes SQL statement categories and the number of rows to be affected. A factor name consists of the prefix @fac.and the display name of the variable. Each tab of the Security Rules tab provides different factors for different checkpoints. The following table describes the factors that are supported by DMS.

      Factor Description
      @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.sql_type The type of the SQL statement. The value is the subcategory of the SQL statement, such as UPDATE or INSERT. For more information, see the SQL subcategories that are described in the SQLConsole for relational databases topic.
      @fac.detail_type The type of the data change. Valid values:
      • COMMON: a Normal Data Modify ticket
      • CHUNK_DML: a Lockless change ticket
      • PROCEDURE: a Programmable Object ticket
      • CRON_CLEAR_DATA: a History Data Clean ticket
      • BIG_FILE: a Large Data Import ticket
      @fac.is_logic A Boolean value that indicates whether the database to be affected is a logical database.
      @fac.extra_info Other information about the ticket. This factor is not in use.
      @fac.is_ignore_affect_rows A Boolean value that indicates whether to skip the validation.
      @fac.insert_rows The number of rows of data to be inserted.
      @fac.update_delete_rows The number of rows of data to be updated.
      @fac.max_alter_table_size The size of the largest tablespace in which the table to be modified is stored.
      @fac.is_has_security_column A Boolean value that indicates whether the SQL statement to be executed involves sensitive fields.
      @fac.security_column_list A list of sensitive fields that the SQL statement to be executed involves.
      @fac.risk_level The risk level of the operation to be performed by the SQL statement.
      @fac.risk_reason The reason based on which the operation is identified as this risk level.

      You can use factors in conditional clauses. For example, you can write @fac.sql_type == 'DML' to evaluate whether an SQL statement is a DML statement.

  • Action clauses

    An action in a security rule is an operation that DMS performs when the IF condition in the rule is met. For example, DMS can forbid the submission of a ticket, select an approval process, approve a ticket, or reject a ticket. An action in a security rule shows the purpose of the security rule. An action name consists of the prefix @act.and the display name of the operation. Each tab of the Security Rules tab provides different actions for different checkpoints. The following table describes the actions that are supported by DMS.

    Action Description
    @act.allow_submit Requires the submission of SQL statements to be executed in a ticket.
    @act.allow_execute_direct Allows the execution of SQL statements in the SQLConsole.
    @act.forbid_execute Forbids the execution of SQL statements.
    @act.mark_risk Marks the risk level of an operation. Example: @act.mark_risk 'middle' 'Medium-level risk: online environment'.
    @act.do_not_approve Specifies the ID of an approval template. For more information, see Configure approval processes.
    @act.choose_approve_template
    @act.choose_approve_template_with_reason
    Note Among all the supported actions, the actions that specify approval templates are the most frequently used. For more information, see Configure approval processes.
  • Predefined functions

    DMS provides predefined functions that can be used in both conditional clauses and action clauses. A function name consists of the prefix @fun. and the display name of the function.

    Function Description Example
    @fun.concat Connects strings to form a single string.

    Output: a string.

    Input: multiple strings.
    @fun.concat('d', 'm', 's') // The output is the string 'dms'.

    @fun.concat('[Development standards] You must enter comments for the [', @fac.column_name, '] field.') // The output is a message that reminds the user who submits the ticket to enter comments for the field.

    @fun.char_length Calculates the length of a string.

    Output: an integer.

    Input: a string.
    @fun.char_length('dms') // The output is 3.

    @fun.char_length(@fac.table_name) // The output is the length of the table name.

    @fun.is_char_lower Evaluates whether all the letters in a string are lowercase letters.

    Output: true or false.

    Input: a string.
    @fun.is_char_lower('dms') // The output is true.

    @fun.is_char_lower(@fac.table_name) // If all the letters in the table name are lowercase letters, the output is true.

    @fun.is_char_upper Evaluates whether all the letters in a string are uppercase letters.

    Output: true or false.

    Input: a string.
    @fun.is_char_upper('dms') // The output is false.

    @fun.is_char_upper(@fac.table_name) // If all the letters in the table name are uppercase letters, the output is true.

    @fun.array_size Counts the number of values in an array.

    Output: an integer.

    Input: an array of values.
    @fun.array_size([1, 2, 3]) // The output is 3.

    @fun.array_size(@fac.table_index_array) // The output is the number of indexes of the table.

    @fun.add Adds multiple numeric values.

    Output: a numeric value.

    Input: multiple numeric values.
    @fun.add(1, 2, 3) // The output is 6.
    @fun.sub Deducts a numeric value from another numeric value.

    Output: a numeric value.

    Input: two numeric values.
    @fun.sub(6, 1) // The output is 5.
    @fun.between Evaluates whether a value belongs to a specific closed range. The supported data types are NUMERIC, DATE, and TIME.

    Output: true or false.

    Input: three values. The first value is the value to be evaluated. The second value indicates the lower limit. The third value indicates the upper limit.
    @fun.between(1, 1, 3) // The output is true because the value 1 belongs to [1, 3].

    @fun.between(2, 1, 3) // The output is true because the value 2 belongs to [1, 3].

    @fun.between(7, 1, 3) // The output is false because the value 7 does not belong to [1, 3].

    @fun.between(@fac.export_rows, 2001, 100000) // If the number of exported rows belongs to [2001, 100000], the output is true.

    @fun.between(@fun.current_datetime(), '2019-10-31 00:00:00', '2019-11-04 00:00:00') // If the current date and time belong to [2019-10-31 00:00:00, 2019-11-04 00:00:00], the output is true.

    @fun.between(@fun.current_date(), '2019-10-31', '2019-11-04') // If the current date belongs to [2019-10-31, 2019-11-04], the output is true.

    @fun.current_datetime Returns the current date and time, in the format of yyyy-MM-dd HH:mm:ss.

    Output: a string.

    Input: none.
    @fun.current_datetime() // For example, the output is 2019-10-31 00:00:00.
    @fun.current_date Returns the current date, in the format of yyyy-MM-dd.

    Output: a string.

    Input: none.
    @fun.current_date() // For example, the output is 2020-01-13.
    @fun.current_time Returns the current time, in the format of HH:mm:ss.

    Output: a string.

    Input: none.
    @fun.current_time() // For example, the output is 19:43:20.
    @fun.is_contain_str Evaluates whether the first string contains the second string.

    Output: true or false.

    Input: two strings. The first is the string to be evaluated. The second is the string to be contained.
    @fun.is_contain_str('abcd', 'ab') // The output is true because the first string "abcd" contains the second string "ab".
    @fun.listEqualIgnoreOrder Evaluates whether two string lists contain the same strings, regardless of the sequence and case sensitivity of strings in the lists.

    Output: true or false.

    Input: two string lists.
    @fun.listEqualIgnoreOrder(['ab','cd'], ['Cd','ab']) // The output is true because the two string lists contain the same strings.

    @fun.listEqualIgnoreOrder(@fac.perm_type, ['QUERY']) // If only the query permissions are requested, the output is true.

    @fun.listEqualIgnoreOrder(@fac.perm_type, ['CORRECT','EXPORT']) // If both the change and export permissions are requested, the output is true.

Examples

  • Limit the number of SQL statements in a ticket
    if
        @fac.sql_count > 1000
    then
        @act.reject_execute 'The number of SQL statements in a ticket cannot exceed 1,000.'
    else
        @act.allow_execute
    end
    Note If the number of SQL statements in a ticket does not exceed 1,000, DMS executes the SQL statements. If the number of SQL statements in the ticket exceeds 1,000, DMS rejects the ticket and displays the specified message.
  • Allow the submission of only DML statements
    if
        @fac.sql_type in [ 'UPDATE','DELETE','INSERT','INSERT_SELECT']
    then
        @act.allow_submit
    end
    Note If the SQL statements in a ticket are the UPDATE, DELETE, INSERT, and INSERT_SELECT statements, DMS allows the execution of the statements.