Custom WHERE Clause Functions

Learn about the custom WHERE clause functions Immuta supports

The policy builder allows you to use custom functions that reference important Immuta metadata from within your where clause. These custom functions can be seen as utilities that help you create policies easier. Using the policy builder, you can include these functions in your masking or row-level policies by choosing where in the sub-action menu or using the custom function in the masking type dropdown menu.

The @attributeValuesContains() function

This function returns true for a given row if the provided column evaluates to an attribute value for which the querying user has a corresponding attribute value. This function requires two arguments and accepts no more than three arguments.

Parameters

Parameter	Description	Required or optional
Attribute name string	The name of the attribute to retrieve values for.	Required
@columnReference('Column name') | @columnTagged('Tag name') string	The column that contains the value to match the attribute key against.	Required
Placeholder string	A placeholder in case the list of values is empty.	Optional

Example

User Frank possesses attribute values sales_region:US and sales_region:Canada.

Only show rows where @attributeValuesContains('sales_region', @columnReference('Territory')) for everyone.

Rows visible to Frank without policy applied

Territory	Price	Volume
US	1000	5000
Canada	800	7000
Mexico	600	9000
Brazil	500	6000

Rows visible to Frank with policy applied

Territory	Price	Volume
US	1000	5000
Canada	800	7000

The @columnReference() function

If this function is used in a global policy to target a data source where the column doesn't exist, a lockout policy will be applied.

This function returns the column with the specified name.

When using this function, match the casing of the column name you specify with the column name in the remote platform and escape the following characters with a backslash: \, ', ", ` .

This function cannot be used to reference column names in external lookup tables inside of a SQL subquery, as lookup tables may not be registered in Immuta (and therefore the function won't be able to resolve to a valid result within Immuta). Instead, use the fully-qualified column name for the external lookup table scenario.

Parameter

Parameter	Description	Required or optional
Column name string	The name of the column to use in the policy.	Required

Example

Only show rows where @columnReference('Classification')='Unrestricted' for everyone.

Rows visible without policy applied

Classification	Site ID	Function
Unrestricted	Alpha	Energy
Unrestricted	Beta	Waste
Secret	Gamma	Government
Top Secret	Delta	Military

Rows visible with policy applied

Classification	Site ID	Function
Unrestricted	Alpha	Energy
Unrestricted	Beta	Waste

The @columnTagged() function

If this function is used in a global policy to target a data source where the tag doesn't exist on any column, a lockout policy will be applied.

This function returns the column name(s) with the specified tag.

This function cannot be used to reference column names in external lookup tables inside of a SQL subquery, as lookup tables may not be registered in Immuta (and therefore the function won't be able to resolve to a valid result within Immuta). Instead, use the fully-qualified column name for the external lookup table scenario.

Parameters

Parameter	Description	Required or optional
Tag name string	The name of the tag.	Required

Example

Only show rows where @columnTagged('Location')='CA' for everyone.

Rows visible without policy applied

Country (Location)	Site ID	Function
CA	Alpha	Energy
CA	Beta	Waste
US	Gamma	Government
US	Delta	Military

Rows visible with policy applied

Country (Location)	Site ID	Function
CA	Alpha	Energy
CA	Beta	Waste

The @groupsContains() function

This function returns true for a given row if the provided column evaluates to a group to which the querying user belongs. This function requires at least one argument.

Parameters

Parameter	Description	Required or optional
@columnReference('Column name') | @columnTagged('Tag name') string	The column that contains the value to match the group against.	Required
Placeholder string	A placeholder in case the list of values is empty.	Optional

Example

User Amy is a member of group Marketing.

Only show rows where @groupsContains(@columnTagged('Department')) for everyone.

Rows visible to Amy without policy applied

A_01 (Department)	Employees	Budget
Marketing	1000	50000
Finance	500	90000
Product	10	10000
Operations	4000	20000

Rows visible to Amy with policy applied

A_01 (Department)	Employees	Budget
Marketing	1000	50000

The @hasAttribute() function

This function returns a boolean indicating if the current user has the specified attribute name and value combination. If the specified attribute name or attribute value has a single quote, you will need to escape it using a \'\' expression within a custom WHERE policy.

Parameters

Parameter	Description	Required or optional
Attribute name string	The name of the attribute.	Required
Attribute value string	The value to correspond with the attribute name.	Required

Example

User Ela possesses attribute Employment.External.

Mask using hashing the value in columns tagged `sensitive` where @hasAttribute('Employment', 'External') for everyone.

Columns visible to Ela without policy applied

Client (sensitive)	Volume	Segment
PepsiCo	200	Gold
ColaCo	100	Silver
WaterCo	4000	Bronze

Columns visible to Ela with policy applied

Client (sensitive)	Volume	Segment
8250209f40430be51eeb25d167f73752	200	Gold
da5bee2b8f051361aea21abfee3dabda	100	Silver
148da326d49fdf1353288e6ac13ed98b	4000	Bronze

The @isInGroups() function

This function returns a boolean indicating if the current user is a member of all of the specified groups. If any of the specified groups has a single quote, you will need to escape it using a \'\' expression within a custom WHERE policy.

Parameter

Parameter	Description	Required or optional
Group names array[string]	A list of group names. For example, isInGroups('group_a', 'group_b', 'group_c').	Required

Example

User Theo is a member of group Interns.

Mask using NULL the value in columns tagged 'sensitive' where @isInGroups('Interns') for everyone.

Columns visible to Theo without policy applied

Client (sensitive)	Volume	Segment
PepsiCo	200	Gold
ColaCo	100	Silver
WaterCo	4000	Bronze

Columns visible to Theo with policy applied

Client (sensitive)	Volume	Segment
NULL	200	Gold
NULL	100	Silver
NULL	4000	Bronze

The @isUsingPurpose() function

This function returns a boolean indicating if the current user is using the specified purpose. If the specified purpose has a single quote, you will need to escape it using a \'\' expression within a custom WHERE policy.

Parameter

Parameter	Description	Required or optional
Purpose string	The name of the purpose to check the user against.	Required

Example

User Alf is currently not acting under purpose Training.

Only show rows where @isUsingPurpose('Training') for everyone.

Rows visible to Alf without policy applied

Participant	Age	Gender
Tom	50	m
Sandra	52	f
Harry	28	m
Sam	34	f

Rows visible to Alf with policy applied

Participant	Age	Gender

The @purposesContains() function

This function returns true for a given row if the provided column evaluates to a purpose under which the querying user is currently acting. This function requires at least one argument and accepts no more than two arguments.

Parameters

Parameter	Description	Required or optional
@columnReference('Column name') | @columnTagged('Tag name') string	The column that contains the value to match the purpose against.	Required
Placeholder string	A placeholder in case the list of values is empty.	Optional

Example

User Sam is currently acting under purpose Fraud Detection.

Only show rows where @purposesContains(@columnReference('Intent')) for everyone.

Rows visible to Sam without policy applied

Intent	Site ID	Classification
Fraud Detection	Alpha	Restricted
Supply Chain Optimization	Beta	Public
Patient Analysis	Gamma	Secret
Patient Analysis	Delta	Secret

Rows visible to Sam with policy applied

Intent	Site ID	Classification
Fraud Detection	Alpha	Restricted

The @username function

This function returns the current user's username.

Parameters

None.

Example

User Tom's Immuta username is [e-mail].

Only show rows where @columnReference('User') = @username for everyone.

Rows visible to Tom without policy applied

User	Age	Salary
[e-mail]	50	10000
[e-mail]	52	50000
[e-mail]	28	20000
[e-mail]	34	15000

Rows visible to Tom with policy applied

User	Age	Salary
[e-mail]	50	10000

Legacy functions

Support for the functions below has been deprecated. Use the listed alternative functions instead.

• @attributes(): This function accepts an attribute key and returns the attribute values associated with that key. Use the @attributeValuesContains() function instead.

  • The example custom WHERE policy below only shows rows that contain a value in the department column that matches one of the querying user's department attribute values:

    Only show rows where @columnReference('department') = ANY(ARRAY[@attributes('department')]::varchar[]) for everyone

• @groups(): This function returns a list of groups the user belongs to. Use the @groupsContains() function instead.

  • The example custom WHERE policy below only shows rows that contain a value in the department column that matches one of the querying user's groups:

    Only show rows where @columnReference('department') = ANY(ARRAY[@groups()]::varchar[]) for everyone

• @purposes(): This function returns a list of purposes the user is currently acting under. Use the @purposesContains() function instead.

  • The example custom WHERE policy below only shows rows that contain a value in the employment status column that matches the purpose the querying user is acting under:

    Only show rows where @columnReference('employment status') = ANY(ARRAY[@purposes()]::varchar[]) for everyone