SQL

Queries

Connection Permissions

This query gets all the current permission grants for individual users and groups to active data source Connections in a Workspace. The query can be modified to get permissions for a particular Connection as well.

WITH connection_access AS (
  SELECT
    entitlements.CONNECTION_ID,
    accessor_id,
    'Group' AS accessor_type,
    dug.group_name AS accessor_name,
    entitlements.action
  FROM
    MODE.ORGANIZATION_USAGE.CONNECTION_ENTITLEMENTS AS entitlements
    INNER JOIN MODE.ORGANIZATION_USAGE.GROUPS AS dug ON entitlements.accessor_id = dug.GROUP_ID
    AND entitlements.accessor_type = 'UserGroup'
  UNION
  ALL
  SELECT
    entitlements.CONNECTION_ID,
    accessor_id,
    'User' AS accessor_type,
    du.name AS accessor_name,
    entitlements.action
  FROM
    MODE.ORGANIZATION_USAGE.CONNECTION_ENTITLEMENTS AS entitlements
    INNER JOIN MODE.ORGANIZATION_USAGE.USERS du ON entitlements.accessor_id = du.id
    AND entitlements.accessor_type = 'Account'
)
SELECT
  connections.ID AS "Connection ID",
  connections.NAME AS "Connection Name",
  connections.DB_TYPE AS "Connection Type",
  connection_access.action AS "Access Action",
  connection_access.accessor_name AS "Accessor Name",
  connection_access.ACCESSOR_ID AS "Accessor ID",
  connection_access.ACCESSOR_TYPE as "Accessor Type"
FROM
  MODE.ORGANIZATION_USAGE.CONNECTIONS AS connections
  LEFT JOIN connection_access ON connections.id = connection_access.CONNECTION_ID