user_identity_attribute_values function
Applies to: Databricks SQL
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 constantSTRINGexpression. The only supported value issession_user().attributeName: ASTRINGexpression 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
principalis not the session user, Databricks raisesUNSUPPORTED_FEATURE.IDENTITY_ATTRIBUTES_NON_SESSION_USER. - If
principalis not a constant, Databricks raises NON_FOLDABLE_ARGUMENT. - If
attributeNameisNULLor doesn't name an existing identity attribute, Databricks raisesIDENTITY_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 nodepartmentvalue, so it denies access by default.NOT array_contains(..., 'contractor')shows rows to users with nouserTypevalue, 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);