I wrote this snippet thinking if I look for the user's friendly name and authorizable_type like 'datasource' I can find a user that has view access to a data source.
What am I missing below? I can see from the server web ui that the user has view access to the DS but it doesn't show up in this query
SELECT
ngp.id as ngp_id
,ngp.authorizable_id
,ngp.authorizable_type
,ngp.capability_id
,cap.name AS capability_field
,cap.display_name AS capability
,ngp.permission
,ngp.grantee_id
,ngp.grantee_type
,g.name as group_name
,g.system as group_system
,g.owner_id as group_owner
,g.minimum_site_role
,gu.user_id
,su.name as user_name
,su.email as user_email
,su.friendly_name
,su.activated_at
,su.state as user_state
FROM next_gen_permissions as ngp
LEFT JOIN capabilities as cap ON ngp.capability_id = cap.id
LEFT JOIN groups as g ON ngp.grantee_id = g.id
LEFT JOIN group_users as gu ON ngp.grantee_id = gu.group_id
--LEFT JOIN users AS u ON gu.user_id = u.id
LEFT JOIN system_users AS su ON gu.user_id = su.id
WHERE (1=1)
AND su.friendly_name ilike 'user%'
AND ngp.authorizable_type ilike 'data%'
#Tableau #Tableau Server
I think the main issue is the join path.
"group_users.user_id" points to "
users.id", not directly to "system_users.id". So this join will miss users:
LEFT JOIN system_users AS su ON gu.user_id =
su.idTry joining through "users" first:
LEFT JOIN users u ON gu.user_id =
u.idLEFT JOIN system_users su ON u.system_user_id =
su.idAlso, your current query only finds access granted through groups. If the user has direct permission on the data source, "ngp.grantee_type" will be "User", and "ngp.grantee_id" should be joined to "
users.id", not "groups.id".
One more thing: the permissions screen shows effective access. "next_gen_permissions" shows permission rules, but it does not represent every reason a user can access content. Owners, admins, project-level rules, locked project permissions, group membership, and denies can all affect the final result.
For data sources, make sure you are checking the right capability too. “View” generally maps to "Read", but depending on what you mean by access, you may also need to look at "Connect".
So I would split the query into two parts: one for group-based grants and one for direct user grants, then layer in denies/project inheritance if you need true effective access.
