> For the complete documentation index, see [llms.txt](https://documentation.immuta.com/2024.2/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://documentation.immuta.com/2024.2/secure-your-data/authoring-policies-in-secure/data-policies/reference-guides/custom-where-clause-functions.md).

# Custom WHERE Clause Functions

## Overview

The Immuta 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 Immuta Policy Builder, you can include these functions in your policy queries by choosing **where** in the sub-action drop-down menu.

## Custom Functions

### 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                  | Type   | Required | Description                                                           |
| - | -------------------------- | ------ | -------- | --------------------------------------------------------------------- |
| 1 | Attribute Name             | String | Required | The name of the attribute to retrieve values for                      |
| 2 | Column Name/SQL Expression | String | Required | The column that contains the value to match the attribute key against |
| 3 | Placeholder                | String | Optional | A placeholder in case the list of values is empty                     |

#### Example

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

{% code overflow="wrap" %}

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

{% endcode %}

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

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

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

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

### The `@columnTagged()` Function

This function returns the column name with the specified tag.

If this function is used in a Global Policy and the tag doesn't exist on a data source, the policy will not be applied.

#### Parameters

| # | Parameter | Type   | Required | Description         |
| - | --------- | ------ | -------- | ------------------- |
| 1 | Tag Name  | String | Required | The name of the tag |

#### 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                  | Type   | Required | Description                                                   |
| - | -------------------------- | ------ | -------- | ------------------------------------------------------------- |
| 1 | Column Name/SQL Expression | String | Required | The column that contains the value to match the group against |
| 2 | Placeholder                | String | Optional | A placeholder in case the list of values is empty             |

#### 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       | Type   | Required | Description                                     |
| - | --------------- | ------ | -------- | ----------------------------------------------- |
| 1 | Attribute Name  | String | Required | The name of the attribute                       |
| 2 | Attribute Value | String | Required | The value to correspond with the attribute name |

#### 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 |
| -------------------------------- | ------ | ------- |
| 8250209f40430be51eeb25d167f73752 | 200    | Gold    |
| da5bee2b8f051361aea21abfee3dabda | 100    | Silver  |
| 148da326d49fdf1353288e6ac13ed98b | 4000   | Bronze  |
| {% endcolumn %}                  |        |         |
| {% endcolumns %}                 |        |         |

### The @iam Function

This function returns the IAM ID for the current user.

#### Parameters

None.

#### Example

User Tom's IAM ID is `bim`.

```
Only show rows where 'Provider'=@iam for everyone.
```

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

| Provider        | Age | Salary |
| --------------- | --- | ------ |
| bim             | 50  | 10000  |
| okta            | 52  | 50000  |
| openid          | 28  | 20000  |
| bim             | 34  | 15000  |
| {% endcolumn %} |     |        |

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

| User             | Age | Salary |
| ---------------- | --- | ------ |
| bim              | 50  | 10000  |
| bim              | 34  | 15000  |
| {% 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.

#### Parameters

| # | Parameter   | Type           | Required | Description                                                           |
| - | ----------- | -------------- | -------- | --------------------------------------------------------------------- |
| 1 | Group names | Array (String) | Required | A list of group names, e.g. `groups('group_a', 'group_b', 'group_c')` |

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

#### Parameters

| # | Parameter | Type   | Required | Description                                       |
| - | --------- | ------ | -------- | ------------------------------------------------- |
| 1 | Purpose   | String | Required | The name of the purpose to check the user against |

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

| Participant      | Age | Gender |
| ---------------- | --- | ------ |
|                  |     |        |
| {% 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                  | Type              | Required | Description                                                     |
| - | -------------------------- | ----------------- | -------- | --------------------------------------------------------------- |
| 1 | Column Name/SQL Expression | String/Expression | Required | The column that contains the value to match the purpose against |
| 2 | Placeholder                | String            | Optional | A placeholder in case the list of values is empty               |

#### Example

User Sam is currently acting under purpose `Fraud Detection`.

{% code overflow="wrap" %}

```
Only show rows where @purposesContains('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 user name.

#### Parameters

None.

#### Example

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

```
Only show rows where '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 %} |     |        |


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://documentation.immuta.com/2024.2/secure-your-data/authoring-policies-in-secure/data-policies/reference-guides/custom-where-clause-functions.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.