Write query and masking rules
Use custom query rules and masking rules when your team needs checks or transformations beyond the built-in rules. CloudDM uses ANTLR4 to define its rule script grammar. You can adapt the examples on this page to write rules. For details about the lexer, grammar, and parser, see the ANTLR4 documentation.
Create a rule template
Open Data Access > Security Rules > Rule Templates. Choose Query rules or Desensitization rules, then create a template.
For a query rule, first choose the object type, such as table, column, query, update, or delete. The object type determines which @domain fields the script can read. Start from a built-in rule for the same object type to find the applicable fields.
A script can define parameters, check the context, and return a result.
// 1. Define a configurable parameter
#define "parameterName" as string
default "defaultValue"
hint "Parameter description"
// 2. Use the context to determine whether to run the check
if @domain.sqlType != 'TARGET_SQL_TYPE' then
return true
end
// 3. Return the check result
return true
Line breaks can separate statements. Use // for single-line comments or /* ... */ for block comments.
Read values
| Syntax | Purpose | Example |
|---|---|---|
@domain.field | Read details about the SQL under review or a result column | @domain.sqlType, @domain.column |
#{parameterName} | Read a value from the rule settings | #{maxCount} |
@func.category.function(...) | Call a built-in function | @func.string.isBlank(@domain.comment) |
Common fields for query rules include the following.
| Field | Meaning |
|---|---|
@domain.sqlType | SQL type, such as SELECT, CREATE_TABLE, UPDATE, or DELETE |
@domain.dsType | DataSource type |
@domain.envName | Environment name |
@domain.dsName | DataSource name |
@domain.userName | Current username |
@domain.userRole | Current user's role |
Each object type can expose additional fields. Table rules can use table, comment, columns, and hasPrimary. Column rules can use column, typeName, typeDesc, nullable, and length. Query, update, and delete rules can use fields such as hasWhere, whereColumns, hasUnion, and setColumns where applicable. Check the built-in rules for that object type in your CloudDM version.
Masking rules run on cells in a query result.
| Field | Meaning |
|---|---|
@domain.catalog | Catalog name |
@domain.schema | Schema name |
@domain.table | Table name |
@domain.column | Column name |
@domain.dbType | Column data type |
@domain.value | Cell value as a string. A database NULL appears here as an empty string. |
@domain.itIsNull | Whether the database value is NULL |
@domain.index | Position of the column in the result set |
@domain.allColumns | All column names in the result set |
@domain.hasRange | Whether the masking rule has a configured scope |
Define parameters
Use #define to let users configure values in the rule settings.
#define "maxCount" as int
default "50"
hint "Maximum number of columns allowed in a table"
#define "allow" as bool
default "false"
enum ["true", "false"]
hint "Allow statements without a WHERE clause"
| Part | Required | Description |
|---|---|---|
#define "name" | Yes | Declare a parameter. Refer to it with #{name}. |
as type | No | Use bool, int, integer, float, decimal, string, date, time, or datetime. |
default "value" | No | Set the default value. |
enum ["value1", "value2"] | No | Limit the choices. |
hint "description" | No | Show help text in the rule settings. |
Parameters arrive as strings. Use cast before boolean or numeric operations.
cast(#{allow} as bool)
cast(#{maxCount} as int)
cast(@domain.length as int)
After writing the script, click Extract parameters. Verify the names, types, defaults, allowed values, and help text.
Write conditions
Branches
Start a conditional block with if and finish it with end. Use elseif and else for additional branches.
if @domain.sqlType == 'CREATE_TABLE' then
checkName = @domain.table
elseif @domain.sqlType == 'ALTER_TABLE_RENAME' then
checkName = @domain.newName
else
return true
end
return @func.string.lowerCase(checkName) == checkName
The assignment checkName = ... creates a temporary variable. Refer to it by name elsewhere in the script.
Common operators
| Type | Operators | Example |
|---|---|---|
| Comparison | ==, !=, >, >=, <, <= | @domain.sqlType == 'SELECT' |
| Logic | and, or, not, or &&, ||, ! | @domain.hasWhere and not @domain.hasUnion |
| Membership | in, not in | @domain.sqlType in ['UPDATE', 'DELETE'] |
| Regular expression | matches, not matches | @domain.column matches '.*phone.*' |
| Arithmetic | +, -, *, /, % | cast(#{maxCount} as int) + 1 |
Use single or double quotes for strings and square brackets for lists, such as ['SELECT', 'UPDATE']. Use parentheses to make precedence clear in complex conditions.
Common functions
| Function | Purpose |
|---|---|
@func.string.isBlank(value) | Check whether a string is blank |
@func.string.isNotBlank(value) | Check whether a string contains non-whitespace characters |
@func.string.trim(value) | Remove leading and trailing whitespace |
@func.string.upperCase(value) | Convert to uppercase |
@func.string.lowerCase(value) | Convert to lowercase |
@func.string.contains(value, search) | Check whether a string contains a substring |
@func.string.containsIgnoreCase(value, search) | Check for a substring without regard to case |
@func.string.containsIgnoreCaseAny(value, list) | Check whether a string contains any value from a list, ignoring case |
@func.string.equalsIgnoreCaseAny(value, list) | Check whether a string equals any value from a list, ignoring case |
@func.string.startsWith(value, prefix) | Check whether a string starts with a prefix |
@func.string.endsWith(value, suffix) | Check whether a string ends with a suffix |
@func.string.split(value, separator) | Split a string on a separator |
@func.string.length(value) | Get the length of a string |
@func.string.substring(value, start, end) | Extract a substring using zero-based indexes |
@func.array.size(value) | Get the length of an array or list |
@func.array.containAny(list1, list2) | Check whether two lists share an element |
@func.number.isNumber(value) | Check whether a string represents a number |
Query rule examples
A query rule returns a boolean. Return true when the check passes and false when it fails. CloudDM handles failures according to the rule's risk level.
Return a result explicitly on every path. Return true early for statements outside the rule's scope to avoid false positives.
This rule limits the number of columns in a new table.
#define "maxCount" as int
default "50"
hint "Maximum number of columns allowed in a table"
if @domain.sqlType != 'CREATE_TABLE' then
return true
end
return @func.array.size(@domain.columns) <= cast(#{maxCount} as int)
This rule requires a WHERE clause for UPDATE. An administrator can use the rule setting to allow updates without a WHERE clause.
#define "allow" as bool
default "false"
enum ["true", "false"]
hint "Allow UPDATE statements without a WHERE clause"
if @domain.sqlType != 'UPDATE' then
return true
end
return cast(#{allow} as bool) or @domain.hasWhere
Masking rule examples
A masking rule returns the name of a masking algorithm.
"algorithm::FULL_MASK"displays the matching value as******."algorithm::ORIGINAL"keeps the original value.
This rule fully masks mainland China mobile numbers in specified columns.
#define "columns" as string
default "phone,mobile"
hint "Names of columns to match, separated by commas"
if @domain.itIsNull then
return "algorithm::ORIGINAL"
end
columnMatched = @func.string.equalsIgnoreCaseAny(
@domain.column,
@func.string.split(#{columns}, ',')
)
if columnMatched and @domain.value matches '^1[3-9][0-9]{9}$' then
return "algorithm::FULL_MASK"
else
return "algorithm::ORIGINAL"
end
This rule masks values based on the configured scope.
if @domain.hasRange then
return "algorithm::FULL_MASK"
else
return "algorithm::ORIGINAL"
end
Validate and enable the rule
- Click Extract parameters and review the settings.
- Validate the script syntax in the rule editor.
- Save the template and apply it to the target security rule.
- Set the parameters and scope, then enable the rule.
- In Environment, assign the security rule to the target environment.
- Test both matching and non-matching examples in a non-production environment.