Queries

Collection Permissions

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

WITH access AS (

SELECT entitlements.COLLECTION_ID, accessor_id, 'Group' AS accessor_type, dug.group_name AS accessor_name, entitlements.action
FROM MODE.ORGANIZATION_USAGE.COLLECTION_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.COLLECTION_ID, accessor_id, 'User' AS accessor_type, du.name AS accessor_name, entitlements.action
FROM MODE.ORGANIZATION_USAGE.COLLECTION_ENTITLEMENTS AS entitlements
INNER JOIN MODE.ORGANIZATION_USAGE.USERS du
  ON entitlements.accessor_id = du.id
  AND entitlements.accessor_type = 'Account'
)


SELECT
DISTINCT collections.name AS "Collection Name",
      collections.DEFAULT_ACCESS_OPTION AS "Default Access Option",
      access.accessor_type AS "Accessor Type",
      access.accessor_name AS "Accessor Name",
      access.ACCESSOR_ID AS "Accessor ID",
      access.action AS "Access Action"
FROM MODE.ORGANIZATION_USAGE.COLLECTIONS as collections
LEFT JOIN access
  ON collections.id = access.COLLECTION_ID
WHERE collections.type = 'WorkspaceCollection'
AND collections.state = 'active'