# Custom WHERE Clause Functions

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')**](#the-columnreference-function) **\|** [**@columnTagged('Tag name')**](#the-columntagged-function) **\| SQL subquery** `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`.

{% code overflow="wrap" %}

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

{% endcode %}

{% columns %}
{% column %}
Rows visible to Frank **without** policy applied

| Terriotry       | Price | Volume |
| --------------- | ----- | ------ |
| US              | 1000  | 5000   |
| Canada          | 800   | 7000   |
| Mexico          | 600   | 9000   |
| Brasil          | 500   | 6000   |
| {% endcolumn %} |       |        |

{% column %}
Rows visible to Frank **with** policy applied

| Territory        | Price | Volume |
| ---------------- | ----- | ------ |
| US               | 1000  | 5000   |
| Canada           | 800   | 7000   |
| {% endcolumn %}  |       |        |
| {% endcolumns %} |       |        |

## The `@columnReference()` function

This function must be used in custom WHERE policies that reference a column name in the data source being protected.

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 should not be used to reference column names in external lookup tables. Instead, use the fully-qualified column name for external lookup tables.

### 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.
```

{% columns %}
{% column %}
Rows visible **without** policy applied

| Classification  | Site ID | Function   |
| --------------- | ------- | ---------- |
| Unrestricted    | Alpha   | Energy     |
| Unrestricted    | Beta    | Waste      |
| Secret          | Gamma   | Government |
| Top Secret      | Delta   | Military   |
| {% endcolumn %} |         |            |

{% column %}
Rows visible **with** policy applied

| Classification   | Site ID | Function |
| ---------------- | ------- | -------- |
| Unrestricted     | Alpha   | Energy   |
| Unrestricted     | Beta    | Waste    |
| {% endcolumn %}  |         |          |
| {% endcolumns %} |         |          |

## The `@columnTagged()` function

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

If this function is used in a global policy and the tag doesn't exist on a column, the policy will not be applied.

### 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.
```

{% columns %}
{% column %}
Rows visible **without** policy applied

| Country (Location) | Site ID | Function   |
| ------------------ | ------- | ---------- |
| CA                 | Alpha   | Energy     |
| CA                 | Beta    | Waste      |
| US                 | Gamma   | Government |
| US                 | Delta   | Military   |
| {% endcolumn %}    |         |            |

{% column %}
Rows visible **with** policy applied

| Country (Location) | Site ID | Function |
| ------------------ | ------- | -------- |
| CA                 | Alpha   | Energy   |
| CA                 | Beta    | Waste    |
| {% endcolumn %}    |         |          |
| {% endcolumns %}   |         |          |

## 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')**](#the-columnreference-function) **\|** [**@columnTagged('Tag name')**](#the-columntagged-function) **\| SQL subquery** `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.
```

{% columns %}
{% column %}
Rows visible to Amy **without** policy applied

| A\_01 (Department) | Employees | Budget |
| ------------------ | --------- | ------ |
| Marketing          | 1000      | 50000  |
| Finance            | 500       | 90000  |
| Product            | 10        | 10000  |
| Operations         | 4000      | 20000  |
| {% endcolumn %}    |           |        |

{% column %}
Rows visible to Amy **with** policy applied

| A\_01 (Department) | Employees | Budget |
| ------------------ | --------- | ------ |
| Marketing          | 1000      | 50000  |
| {% endcolumn %}    |           |        |
| {% endcolumns %}   |           |        |

## 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`.

{% code overflow="wrap" %}

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

{% endcode %}

{% columns %}
{% column %}
Columns visible to Ela **without** policy applied

| Client (sensitive) | Volume | Segment |
| ------------------ | ------ | ------- |
| PepsiCo            | 200    | Gold    |
| ColaCo             | 100    | Silver  |
| WaterCo            | 4000   | Bronze  |
| {% endcolumn %}    |        |         |

{% column %}
Columns visible to Ela **with** policy applied

| Client (sensitive) | Volume | Segment |
| ------------------ | ------ | ------- |
| NULL               | 200    | Gold    |
| NULL               | 100    | Silver  |
| NULL               | 4000   | Bronze  |
| {% endcolumn %}    |        |         |
| {% endcolumns %}   |        |         |

## 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, `groups('group_a', 'group_b', 'group_c')`. | Required             |

#### Example

User Theo is a member of group `Interns`.

{% code overflow="wrap" %}

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

{% endcode %}

{% columns %}
{% column %}
Columns visible to Theo **without** policy applied

| Client (sensitive) | Volume | Segment |
| ------------------ | ------ | ------- |
| PepsiCo            | 200    | Gold    |
| ColaCo             | 100    | Silver  |
| WaterCo            | 4000   | Bronze  |
| {% endcolumn %}    |        |         |

{% column %}
Columns visible to Theo **with** policy applied

| Client (sensitive) | Volume | Segment |
| ------------------ | ------ | ------- |
| NULL               | 200    | Gold    |
| NULL               | 100    | Silver  |
| NULL               | 4000   | Bronze  |
| {% endcolumn %}    |        |         |
| {% endcolumns %}   |        |         |

## 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`.

{% code overflow="wrap" %}

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

{% endcode %}

{% columns %}
{% column %}
Rows visible to Alf **without** policy applied

| Participant     | Age | Gender |
| --------------- | --- | ------ |
| Tom             | 50  | m      |
| Sandra          | 52  | f      |
| Harry           | 28  | m      |
| Sam             | 34  | f      |
| {% endcolumn %} |     |        |

{% column %}
Rows visible to Alf **with** policy applied
{% endcolumn %}
{% endcolumns %}

## 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')**](#the-columnreference-function) **\|** [**@columnTagged('Tag name')**](#the-columntagged-function) **\| SQL subquery** `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`.

{% code overflow="wrap" %}

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

{% endcode %}

{% columns %}
{% column %}
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         |
| {% endcolumn %}           |         |                |

{% column %}
Rows visible to Sam **with** policy applied

| Intent           | Site ID | Classification |
| ---------------- | ------- | -------------- |
| Fraud Detection  | Alpha   | Restricted     |
| {% endcolumn %}  |         |                |
| {% endcolumns %} |         |                |

## The `@username` function

This function returns the current user's username.

### Parameters

None.

#### Example

User Tom's Immuta username is `tom@abc.com`.

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

{% columns %}
{% column %}
Rows visible to Tom **without** policy applied

| User             | Age | Salary |
| ---------------- | --- | ------ |
| <tom@abc.com>    | 50  | 10000  |
| <sandra@abc.com> | 52  | 50000  |
| <harry@abc.com>  | 28  | 20000  |
| <sam@abc.com>    | 34  | 15000  |
| {% endcolumn %}  |     |        |

{% column %}
Rows visible to Tom **with** policy applied

| User             | Age | Salary |
| ---------------- | --- | ------ |
| <tom@abc.com>    | 50  | 10000  |
| {% endcolumn %}  |     |        |
| {% endcolumns %} |     |        |