Skip to main content

user_identity_attribute_values function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19 and above

Beta

This function reads identity attributes, which are in Beta. Before you can use it, an account admin must enable the Identity Attribute Control List preview from the account console Previews page and provision identity attributes for your users. See Identity attributes.

Returns the values of an identity attribute of the session user.

Use this function in row filter UDFs to filter rows based on attributes of the querying user, such as their department or country. To apply the filter across tables, reference the UDF in an ABAC row filter policy.

Syntax​

user_identity_attribute_values(principal, attributeName)

Arguments​

  • principal: A constant STRING expression. The only supported value is session_user().
  • attributeName: A STRING expression that names an identity attribute. The name doesn't have to be a constant, so it can be a column or a SQL UDF parameter. Names are case-sensitive. For the available attributes, see identity attributes.

Returns​

A non-nullable ARRAY<STRING>.

If the user has no value for attributeName, the function returns an empty array.

Error conditions​

  • If principal is not the session user, Databricks raises UNSUPPORTED_FEATURE.IDENTITY_ATTRIBUTES_NON_SESSION_USER.
  • If principal is not a constant, Databricks raises NON_FOLDABLE_ARGUMENT.
  • If attributeName is NULL or doesn't name an existing identity attribute, Databricks raises IDENTITY_ATTRIBUTE_NOT_FOUND. This includes names that differ only in case, such as 'Department'.

Notes​

  • The function returns the attributes of the user running the query. Inside a view or a SQL UDF, it returns the attributes of the user querying the view or calling the UDF, not the owner.
  • If you use the function in a row filter UDF, write the filter condition so that an empty array denies access. For example, array_contains(..., 'HR') hides rows from users with no department value, so it denies access by default. NOT array_contains(..., 'contractor') shows rows to users with no userType value, so it grants access by default.
  • Use array functions such as array_contains or arrays_overlap to test the result, rather than assuming the array has exactly one element.
  • You can't use the function in a view shared through OpenSharing.
  • To check which attribute values the function returns for you, call it directly in a query, for example SELECT user_identity_attribute_values(session_user(), 'department').

Examples​

SQL
> SELECT user_identity_attribute_values(session_user(), 'department');
["Finance"]

-- The user has no value for the attribute
> SELECT user_identity_attribute_values(session_user(), 'costCenter');
[]

-- Attribute names are case-sensitive
> SELECT user_identity_attribute_values(session_user(), 'Department');
Error: IDENTITY_ATTRIBUTE_NOT_FOUND

-- The function only works for the session user's attributes
> SELECT user_identity_attribute_values('alice@example.com', 'department');
Error: UNSUPPORTED_FEATURE.IDENTITY_ATTRIBUTES_NON_SESSION_USER

-- Row filter UDF that returns only rows for the user's country
> CREATE FUNCTION main.sales.country_filter(country STRING)
RETURN array_contains(user_identity_attribute_values(session_user(), 'country'), country);

-- Apply the UDF to every table in the schema that has a column tagged 'country'
> CREATE POLICY filter_by_user_country
ON SCHEMA main.sales
ROW FILTER main.sales.country_filter
TO `account users`
FOR TABLES
MATCH COLUMNS has_tag('country') AS country_col
USING COLUMNS (country_col);