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.
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
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
Condition 1 Action 1 Condition 2 Action 2 [else Action 3]
- Conditional clauses
DMS uses conditional clauses to evaluate whether to perform actions. The result of a conditional clause is
false. A conditional clause consists of one or more connectors, operators, and factors. Connectors are
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.
ANDconnector has a higher priority than the
ORconnector. 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 <= 0expression and then the result of the
1 == 1expression. After that, DMS evaluates the result of the
ORexpression based on the preceding results.
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 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) == trueto specify the priority. DMS first evaluates the result of the
1 <= 2expression in parentheses.
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.
Factor Description @fac.env_type The type of the environment. The value is the display name of the environment type, such as
PRODUCT. For more information, see Change the environment type for an instance.
@fac.sql_type The type of the SQL statement. The value is the subcategory of the SQL statement, such as
@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 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 Indicates whether to skip the validation. @fac.insert_rows The number of data rows to which you want to insert data. @fac.update_delete_rows The number of data rows 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 Indicates whether the SQL statement to be executed involves sensitive fields. @fac.security_column_list The sensitive fields involved in the SQL statement to be executed. @fac.risk_level The risk level of the operation to be performed by the SQL statement. @fac.risk_reason The 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
IFcondition 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.
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 SQL Console. @act.forbid_execute Forbids the execution of SQL statements. @act.mark_risk Marks an operation with a risk level. Example:
@act.mark_risk 'middle' 'Medium 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
- 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 Returns 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]) // 3
@fun.array_size(@fac.table_index_array) // The output is the number of indexes of the table.
@fun.add Adds up multiple numeric values.
Output: a numeric value.Input: multiple numeric values.
@fun.add(1, 2, 3) // 6 @fun.sub Deducts a numeric value from another numeric value.
Output: a numeric value.Input: two numeric values.
@fun.sub(6, 1) // 5 @fun.between Evaluates 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_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() // The output is the current date and time, for example, 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() // The output is the current date, for example, 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() // The output is the current time, for example, 19:43:20. @fun.is_contain_str Evaluates 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.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 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.
- 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 endNote If the SQL statements in a ticket are the
INSERT_SELECTstatements, DMS allows the execution of the statements.