CloudDM 4.8.0 adds GreptimeDB as a DataSource
Skip to main content

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​

SyntaxPurposeExample
@domain.fieldRead 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.

FieldMeaning
@domain.sqlTypeSQL type, such as SELECT, CREATE_TABLE, UPDATE, or DELETE
@domain.dsTypeDataSource type
@domain.envNameEnvironment name
@domain.dsNameDataSource name
@domain.userNameCurrent username
@domain.userRoleCurrent 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.

FieldMeaning
@domain.catalogCatalog name
@domain.schemaSchema name
@domain.tableTable name
@domain.columnColumn name
@domain.dbTypeColumn data type
@domain.valueCell value as a string. A database NULL appears here as an empty string.
@domain.itIsNullWhether the database value is NULL
@domain.indexPosition of the column in the result set
@domain.allColumnsAll column names in the result set
@domain.hasRangeWhether 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"
PartRequiredDescription
#define "name"YesDeclare a parameter. Refer to it with #{name}.
as typeNoUse bool, int, integer, float, decimal, string, date, time, or datetime.
default "value"NoSet the default value.
enum ["value1", "value2"]NoLimit the choices.
hint "description"NoShow 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​

TypeOperatorsExample
Comparison==, !=, >, >=, <, <=@domain.sqlType == 'SELECT'
Logicand, or, not, or &&, ||, !@domain.hasWhere and not @domain.hasUnion
Membershipin, not in@domain.sqlType in ['UPDATE', 'DELETE']
Regular expressionmatches, 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​

FunctionPurpose
@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​

  1. Click Extract parameters and review the settings.
  2. Validate the script syntax in the rule editor.
  3. Save the template and apply it to the target security rule.
  4. Set the parameters and scope, then enable the rule.
  5. In Environment, assign the security rule to the target environment.
  6. Test both matching and non-matching examples in a non-production environment.