vw_user_groups

Creates, updates, deletes, gets or lists a vw_user_groups resource.

Overview

Name	vw_user_groups
Type	View
Id	databricks_workspace.iam.vw_user_groups

Fields

The following fields are returned by this view:

Name	Datatype	Description
deployment_name	string	Workspace deployment name used to scope the query.
id	string	Unique identifier for the workspace user.
userName	string	Username of the workspace user.
displayName	string	Human-readable display name of the workspace user.
group_id	string	Unique identifier of the group the user belongs to (one row per group membership).
group_name	string	Display name of the group.
group_ref	string	SCIM $ref URI for the group resource.
group_type	string	Type of the group resource (typically Group).

Required Parameters

The following parameters are required by this view:

Name	Datatype	Description
deployment_name	string	Workspace deployment name used to scope the query.

SELECT Examples

SELECT
  deployment_name,
  id,
  userName,
  displayName,
  group_id,
  group_name,
  group_ref,
  group_type
FROM databricks_workspace.iam.vw_user_groups
WHERE deployment_name = '{{ deployment_name }}';

SQL Definition

Sqlite3

SELECT
  u.deployment_name,
  u.id,
  u.userName,
  u.displayName,
  JSON_EXTRACT(g.value, '$.value') AS group_id,
  JSON_EXTRACT(g.value, '$.display') AS group_name,
  JSON_EXTRACT(g.value, '$.$ref') AS group_ref,
  JSON_EXTRACT(g.value, '$.type') AS group_type
FROM databricks_workspace.iam.users_v2 u,
     JSON_EACH(u.groups) g
WHERE u.deployment_name = '{{ deployment_name }}'

Postgres

SELECT
  u.deployment_name,
  u.id,
  u.userName,
  u.displayName,
  g.value->>'value' AS group_id,
  g.value->>'display' AS group_name,
  g.value->>'$ref' AS group_ref,
  g.value->>'type' AS group_type
FROM databricks_workspace.iam.users_v2 u,
     jsonb_array_elements(u.groups::jsonb) AS g
WHERE u.deployment_name = '{{ deployment_name }}'