Data Management (DMS) provides a domain-specific language (DSL) to describe security rules. You can use the DSL syntax to define security rules that serve as 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 basic format of a security rule is as follows:

 
    Condition 1
 
    Action 1
 
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.

 
    Condition 1
 
    Action 1
 
    Condition 2
 
    Action 2
[else Action 3]
 
Note If Condition 1 is met, DMS performs Action 1. If Condition 1 is not met and Condition 2 is met, DMS performs Action 2. If both 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. 
     
     
     
    Note The results of these sample conditional clauses are all true.
    • Connectors

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

      For example, if 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.

      OperatorDescriptionExample
      ==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
      inEvaluates whether a value belongs to an array of values.'a' in ['a', 'b', 'c']
      not inEvaluates whether a value does not belong to an array of values.'a' not in ['a', 'b', 'c']
      matchsEvaluates whether a string matches a regular expression.'idx_aa' matchs 'idx_\\w+'
      not matchsEvaluates whether a string does not match a regular expression.'idx_aa' not matchs 'idx_\\w+'
      isBlankEvaluates whether a value is empty.'' isBlank
      isNotBlankEvaluates 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 an expression based on your needs. If a conditional clause contains nested expressions, we recommend that you enclose the expression with higher priority in parentheses (). For example, if a conditional clause is 1 <= 2 == true, you can change the clause to (1 <= 2) == true to specify the priority. DMS first evaluates the result of the 1 <= 2 expression in 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 factor. DMS provides different factors for different checkpoints in each module. The following table lists some factors and their descriptions.

      FactorDescription
      @fac.env_typeThe 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 for an instance.
      @fac.sql_typeThe type of the SQL statement. The value is the subcategory of the SQL statement, such as UPDATE or INSERT.
      @fac.detail_typeThe 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 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_rowsIndicates whether to skip the validation.
      @fac.insert_rowsThe number of data rows to which you want to insert data.
      @fac.update_delete_rowsThe number of data rows to be updated.
      @fac.max_alter_table_sizeThe size of the largest tablespace in which the table to be modified is stored.
      @fac.is_has_security_columnIndicates whether the SQL statement to be executed involves sensitive fields.
      @fac.security_column_listThe sensitive fields involved in the SQL statement to be executed.
      @fac.risk_levelThe risk level of the operation to be performed by the SQL statement.
      @fac.risk_reasonThe reason for which the operation is marked with 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 action. DMS provides different actions for different checkpoints in each module. The following table lists some actions and their descriptions.

    ActionDescription
    @act.allow_submitRequires the submission of SQL statements to be executed in a ticket.
    @act.allow_execute_directAllows the execution of SQL statements in SQL Console.
    @act.forbid_executeForbids the execution of SQL statements.
    @act.mark_riskMarks an operation with a risk level. Example: @act.mark_risk 'middle' 'Medium risk: online environment'.
    @act.do_not_approveSpecifies the ID of an approval template. For more information, see Configure approval processes.
    @act.choose_approve_template
    @act.choose_approve_template_with_reason
  • 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.

    FunctionDescriptionExample
    @fun.concatConnects 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_lengthReturns 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_lowerEvaluates 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_upperEvaluates 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_sizeCounts the number of values in an array.

    Output: an integer.

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

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

    @fun.addAdds up multiple numeric values.

    Output: a numeric value.

    Input: multiple numeric values.
    @fun.add(1, 2, 3) // 6
    @fun.subDeducts a numeric value from another numeric value.

    Output: a numeric value.

    Input: two numeric values.
    @fun.sub(6, 1) // 5
    @fun.betweenEvaluates whether a value falls within a specific closed range. The supported data types are NUMERIC, DATE, and TIME.

    Output: true or false.

    Input: consists of 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 falls within [1, 3].

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

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

    @fun.between(@fac.export_rows, 2001, 100000) // If the number of exported rows falls within [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 fall within [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 falls within [2019-10-31, 2019-11-04], the output is true.

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

    Output: a string.

    Input: none.
    @fun.current_datetime() // The output is the current date and time, for example, 2019-10-31 00:00:00.
    @fun.current_dateReturns the current date in the format of yyyy-MM-dd.

    Output: a string.

    Input: none.
    @fun.current_date() // The output is the current date, for example, 2020-01-13.
    @fun.current_timeReturns the current time in the format of HH:mm:ss.

    Output: a string.

    Input: none.
    @fun.current_time() // The output is the current time, for example, 19:43:20.
    @fun.is_contain_strEvaluates whether the first string contains the second string.

    Output: true or false.

    Input: two strings.
    @fun.is_contain_str('abcd', 'ab') // The output is true because the first string abcd contains the second string ab.
    @fun.listEqualIgnoreOrderEvaluates 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 permission is 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.

Example

  • Limit the number of SQL statements in a ticket
     
         
     
        @act.reject_execute 'The number of SQL statements in a ticket cannot exceed 1,000.'
     
         
     
    Note If the number of SQL statements in a ticket does not exceed 1,000, DMS executes the SQL statements. Otherwise, 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.