In Tableau Server v2018.2.3, all data asset permissions are found in the next_gen_permissions table. A few quirks lurk inside this table, by none appear to adversely affect permission query results. Below is a set of PostgreSQL queries which can be used narrowly to identify which Tableau users have View permissions to Tableau data assets.

Study data asset user/group permissions

The next_gen_permissions table contains permissions info for Project, Workbook, Data Source and View data assets. As a general rule, this table will contain all Project permissions. It will only contain Workbook and Data Source asset permissions where Project permissions are UNLOCKED, and it will only contain View permissions where Workbook permissions are UNLOCKED. There are exceptions. For example, the table can contain a small set of data assets that are LOCKED at the Project level, and it can contain assets that have been deleted.

The authorizable_id's column represents the id's of each Project, Workbook, Data Source and View table. These id's can overlap, so use the authorizable_type field to distinguish between the assets.

------------------------------------------------------------
-- Find asset permissions in next_gen_permissions 
------------------------------------------------------------
SELECT DISTINCT
	ngp.authorizable_id
FROM 
	public.next_gen_permissions ngp
WHERE
--	ngp.authorizable_type LIKE 'Datasource'
--	ngp.authorizable_type LIKE 'Project'
--	ngp.authorizable_type LIKE 'Workbook'
--	ngp.authorizable_type LIKE 'View'
ORDER BY
	ngp.authorizable_id
;

Quicklinks:

• Projects
• Workbooks
• Data Sources
• Views
• Users & Groups

Projects

next_gen_permissions shows all Project permissions, locked or unlocked, parent Project or child Project. It does NOT show Projects totally lacking permission assignments.

Useful queries

------------------------------------------------------------
-- 1. Show all Projects
------------------------------------------------------------
SELECT
	p.id
	,p.name
FROM 
	public.projects p
ORDER BY 
	p.id
;

------------------------------------------------------------
-- 2. Projects found in next_gen_permissions
------------------------------------------------------------
SELECT DISTINCT
	ngp.authorizable_id
	,ngp.authorizable_type
FROM 
	public.next_gen_permissions ngp
WHERE
	ngp.authorizable_type LIKE 'Project'
ORDER BY
	ngp.authorizable_id
;

------------------------------------------------------------
-- 3. Projects missing from next_gen_permissions - note: these
-- don't show any permissions in the UI either
------------------------------------------------------------
SELECT
    p.id
    ,p.name
    ,ngp.authorizable_id
FROM 
    public.projects p
LEFT OUTER JOIN
    public.next_gen_permissions ngp ON ngp.authorizable_id = p.id
WHERE
    ngp.authorizable_id IS NULL
ORDER BY 
    p.id
;

Workbooks

The next_gen_permissions table shows only Workbook assets from unlocked Projects. These Workbook assets typically have different permission assignments than the Project(s) they belong to.

Some of these Workbooks might belong to locked Projects, and a few of these Workbooks might be missing from the workbooks table.

Workbooks missing from the next_gen_permissions table typically have no direct permission assignments, because they belong to locked Projects. Permissions for these Workbooks can be gleaned from the Project(s) they belong to in next_gen_permissions.

Some Workbooks belonging to unlocked Projects may have no direct permission assignments. Permissions for these Workbooks can also be gleaned from the Project(s) they belong to in next_gen_permissions.

Useful queries

Show the set of Workbooks found in the workbooks table.

------------------------------------------------------------
-- 1. Show all Workbooks
------------------------------------------------------------
SELECT
	wb.id
	,wb.name
FROM
	public.workbooks wb
ORDER BY
	wb.id
;

Show the set of Workbooks found inside the next_gen_permissions table. Again, most of these will belong to UNLOCKED Projects. But a handful may belong to LOCKED Projects, and some may no longer exist in the workbooks table.

------------------------------------------------------------
-- 2. Show only the Workbooks found in next_gen_permissions 
-- that have direct permissions assignments
------------------------------------------------------------
SELECT DISTINCT
	ngp.authorizable_id
	,ngp.authorizable_type
FROM 
	public.next_gen_permissions ngp
WHERE
	ngp.authorizable_type LIKE 'Workbook'
ORDER BY
	ngp.authorizable_id
;

------------------------------------------------------------
-- Validate the total
------------------------------------------------------------
-- 2a. Set of Workbooks in LOCKED Projects
------------------------------------------------------------
SELECT DISTINCT
	wb.id AS id
	,wb.name
FROM 
	public.workbooks wb
JOIN
	public.projects p ON p.id = wb.project_id
FULL OUTER JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = wb.id
WHERE
	ngp.authorizable_id IS NOT NULL
	AND
	p.controlled_permissions_enabled = TRUE
	AND
	ngp.authorizable_type LIKE 'Workbook'

UNION

-- 2b. Set of Workbooks in UNLOCKED Projects
------------------------------------------------------------
SELECT DISTINCT
	wb.id AS id
	,wb.name
FROM 
	public.workbooks wb
JOIN
	public.projects p ON p.id = wb.project_id
FULL OUTER JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = wb.id
WHERE
	ngp.authorizable_id IS NOT NULL
	AND
	p.controlled_permissions_enabled = FALSE
	AND
	ngp.authorizable_type LIKE 'Workbook'
	
EXCEPT 

-- 2c. Find the correct the total of Workbooks found in next_gen_permissions 
-- by subtracting the Workbooks missing from the workbooks table
SELECT DISTINCT
	ngp.authorizable_id AS id
	,ngp.authorizable_type
FROM 
	public.workbooks wb
FULL OUTER JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = wb.id
WHERE
	wb.id IS NULL
	AND
	ngp.authorizable_type LIKE 'Workbook'

ORDER BY
	id

Show the set of Workbooks missing from next_gen_permissions that need to inherit permissions from the Project they belong to. Note, permissions for these Projects can be both LOCKED or UNLOCKED.

------------------------------------------------------------
-- 3. Show only the Workbooks missing from next_gen_permissions
-- that inherit permissions from Projects
------------------------------------------------------------
SELECT DISTINCT
	wb.id
FROM 
	public.workbooks wb

EXCEPT

SELECT DISTINCT
	ngp.authorizable_id
FROM 
	public.next_gen_permissions ngp
WHERE
	ngp.authorizable_type LIKE 'Workbook'

------------------------------------------------------------
-- Validate the total
------------------------------------------------------------
-- 3a. Set of Workbooks missing from ngp that belong to LOCKED p
------------------------------------------------------------
SELECT DISTINCT
	wb.id AS id
	,wb.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.workbooks wb ON wb.id = ngp.authorizable_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	wb.id IS NOT NULL
	AND
	ngp.authorizable_id IS Null
	AND
	p.controlled_permissions_enabled = TRUE

UNION

-- 3b Workbooks missing from ngp that belong to UNLOCKED p
------------------------------------------------------------
SELECT DISTINCT
	wb.id AS id
	,wb.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.workbooks wb ON wb.id = ngp.authorizable_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	wb.id IS NOT NULL
	AND
	ngp.authorizable_id IS Null
	AND
	p.controlled_permissions_enabled = FALSE

UNION

-- 3c. Workbooks found in ngp belonging to LOCKED p
------------------------------------------------------------
SELECT DISTINCT
	wb.id AS id
	,wb.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.workbooks wb ON wb.id = ngp.authorizable_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	wb.id IS NOT NULL
	AND
	ngp.authorizable_id IS NOT Null
	AND
	ngp.authorizable_type NOT LIKE 'View'
	AND
	p.controlled_permissions_enabled = TRUE

UNION

-- 3d. Set of Workbooks found in ngp belonging to UNLOCKED p
------------------------------------------------------------
SELECT DISTINCT
	wb.id AS id
	,wb.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.workbooks wb ON wb.id = ngp.authorizable_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	wb.id IS NOT NULL
	AND
	ngp.authorizable_id IS NOT Null
	AND
	ngp.authorizable_type NOT LIKE 'View'
	AND
	p.controlled_permissions_enabled = FALSE

ORDER BY
	id

App-specific queries

Show Tableau Groups Permitting All Users

Show a comma delimited string of organization-wide groups that have at least read access to the Workbook, like the ALL_USERS or ALL_INTERNAL_USERS groups (group_id's 2 and 558 respectively from the groups table). Add all Workbooks with permissions found in next_gen_permissions to the set of Workbooks inheriting from Projects. Exclude some Projects and Workbooks from the results.

Tips

• Some workbooks contain both of these groups, so a WHERE clause that tries to filter them with an OR clause will be inaccurate. Instead, return DISTINCT results with a GROUP BY on the workbook key.
• To display the group names as a single value in each result, aggregate them into a single string (or array) of delimited values. As these values can have duplicates, return only DISTINCT items to the aggregation.

------------------------------------------------------------
-- What Workbooks do "All User" type groups have access to?
------------------------------------------------------------
-- 1. Get Workbook permissions from Project
------------------------------------------------------------
SELECT DISTINCT
	wbid AS id
	,STRING_AGG(DISTINCT grp.name, ',') AS permitted_group_all	
FROM 
(
	-- set of Workbooks that can inherit permissions from Project
	SELECT DISTINCT
		wb.id AS wbid
		,p.id AS pid
	FROM 
	(
		-- subset of workbooks missing from ngp
		SELECT DISTINCT
			wb.id AS wbid
		FROM 
			public.workbooks wb
		EXCEPT
		SELECT DISTINCT
			ngp.authorizable_id AS vid
		FROM 
			public.next_gen_permissions ngp
		WHERE
			ngp.authorizable_type LIKE 'Workbook'
	) subset1
	JOIN
		public.workbooks wb ON wb.id = subset1.wbid
	JOIN
		public.projects p ON p.id = wb.project_id
)	subset2
JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.pid
LEFT JOIN
	public.groups grp ON grp.id = ngp.grantee_id
WHERE
	ngp.authorizable_type LIKE 'Project'
	AND
	ngp.grantee_id IN (2,558)
	AND
	pid NOT IN (1,28,115,121,122,123,132,136,140,171,172,176,187,644,699)
	AND
	wbid NOT IN (39131,39361)
GROUP BY
	wbid
	
UNION

-- 2. Get Workbook permissions from next_gen_permissions
------------------------------------------------------------
SELECT DISTINCT
	ngp.authorizable_id AS id
 	,STRING_AGG(DISTINCT grp.name, ',') AS permitted_group_all	
FROM
	public.next_gen_permissions ngp
LEFT JOIN
	public.workbooks wb ON wb.id = ngp.authorizable_id
LEFT JOIN
	public.projects p ON p.id = wb.project_id
LEFT JOIN
	public.groups grp ON ngp.grantee_id  = grp.id
WHERE
	wb.id IS NOT NULL
	AND
	ngp.authorizable_type LIKE 'Workbook'
	AND
	authorizable_id IS NOT NULL
	AND
	ngp.grantee_id IN (2,558)
	AND
	p.id NOT IN (1,28,115,121,122,123,132,136,140,171,172,176,187,644,699)
	AND
	wb.id NOT IN (39131,39361)
GROUP BY
	ngp.authorizable_id

ORDER BY
	id
;

------------------------------------------------------------
-- NOTE: To confirm the above counts match the total 
-- number of Workbooks from the views table, just comment
-- out the STRING_AGG, ngp.grantee_id and GROUP BY
-- elements
------------------------------------------------------------

Show Permitted Group User Names

Return a a comma delimited string of  unique user names that inherit permissions from a Group with at least read access to the Workbook by adding the set Workbooks with permissions found in next_gen_permissions to the set of Workbooks inheriting from Projects.

------------------------------------------------------------
-- What Workbooks do Users in Groups have read access to?
------------------------------------------------------------
-- 1. Get Workbook permissions from Project
------------------------------------------------------------
SELECT DISTINCT
    wbid AS id
    ,STRING_AGG(DISTINCT su.name, ',') AS permitted_group_user_name    
FROM 
(
    -- set of Workbooks inheriting permissions from Projects
    SELECT DISTINCT
        wb.id AS wbid
        ,p.id AS pid
    FROM 
    (
        -- subset of workbooks missing from ngp
        SELECT DISTINCT
            wb.id AS wbid
        FROM 
            public.workbooks wb
        EXCEPT
        SELECT DISTINCT
            ngp.authorizable_id AS wbid
        FROM 
            public.next_gen_permissions ngp
        WHERE
            ngp.authorizable_type LIKE 'Workbook'
    )    subset1
    JOIN
        public.workbooks wb ON wb.id = subset1.wbid
    JOIN
        public.projects p ON p.id = wb.project_id
)    subset2
LEFT JOIN
    public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.pid
LEFT JOIN
    public.groups g ON g.id = ngp.grantee_id
LEFT JOIN
    public.group_users gu ON gu.group_id = g.id
LEFT JOIN
    public.users u ON u.id = gu.user_id
LEFT JOIN
    public.system_users su ON su.id = u.system_user_id
WHERE
    wbid IS NOT NULL
    AND
    ngp.capability_id = 1
	AND
	ngp.permission NOT IN (2,4)
    AND 
    g.name NOT LIKE 'role%'
    AND
    ngp.grantee_id NOT IN (2,558)
    AND
    su.name IS NOT NULL
    AND
    pid NOT IN (1,28,115,121,122,123,132,136,140,171,172,176,187,644,699)
    AND
    wbid NOT IN (39131,39361)
GROUP BY
    wbid
    
UNION
-- 2. Get Workbook permissions from next_gen_permissions
------------------------------------------------------------
SELECT DISTINCT
    ngp.authorizable_id AS id
    ,STRING_AGG(DISTINCT su.name, ',') AS permitted_group_user_name
FROM 
    public.next_gen_permissions ngp
JOIN
    public.workbooks wb ON wb.id = ngp.authorizable_id
LEFT JOIN
    public.projects p ON p.id = wb.project_id
LEFT JOIN
    public.groups g ON g.id = ngp.grantee_id
LEFT JOIN
    public.group_users gu ON gu.group_id = g.id
LEFT JOIN
    public.users u ON u.id = gu.user_id
LEFT JOIN
    public.system_users su ON su.id = u.system_user_id
WHERE
    wb.id IS NOT NULL
    AND
    ngp.authorizable_type LIKE 'Workbook'
    AND
    authorizable_id IS NOT NULL
    AND
    ngp.capability_id = 1
    AND 
	ngp.permission NOT IN (2,4)
    AND 
    g.name NOT LIKE 'role%'
    AND
    ngp.grantee_id NOT IN (2,558)        
    AND
    su.name IS NOT NULL
    AND
    p.id NOT IN (1,28,115,121,122,123,132,136,140,171,172,176,187,644,699)
    AND
    wb.id NOT IN (39131,39361)
GROUP BY
    ngp.authorizable_id
ORDER BY
    id
;
------------------------------------------------------------
-- NOTE: To confirm the above counts match the total 
-- number of Workbooks from the views table, just comment
-- out the STRING_AGG, ngp.grantee_id and GROUP BY
-- elements
------------------------------------------------------------

Show Permitted User Names

Show a comma delimited string of unique user names with permissions assigned directly to the Workbook by adding the set Workbooks with permissions found in next_gen_permissions to the set of Workbooks inheriting from Projects. Exclude a few Projects and Workbooks from the set.

------------------------------------------------------------
-- What Workbooks do Users have read access to?
------------------------------------------------------------
-- 1. Get Workbook permissions from Project
------------------------------------------------------------
SELECT DISTINCT
	wbid AS id
	,STRING_AGG(DISTINCT su.name, ',') AS permitted_user_name	
FROM 
(
	-- set of Workbooks that can inherit permissions from Project
	SELECT DISTINCT
		wb.id AS wbid
		,p.id AS pid
	FROM 
	(
		-- subset of Workbooks missing from ngp
		SELECT DISTINCT
			wb.id AS wbid
		FROM 
			public.workbooks wb
		EXCEPT
		SELECT DISTINCT
			ngp.authorizable_id AS vid
		FROM 
			public.next_gen_permissions ngp
		WHERE
			ngp.authorizable_type LIKE 'Workbook'
	) subset1
	JOIN
		public.workbooks wb ON wb.id = subset1.wbid
	JOIN
		public.projects p ON p.id = wb.project_id
)	subset2
JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.pid
LEFT JOIN
	public.users u ON u.id = ngp.grantee_id
LEFT JOIN
	public.system_users su ON su.id = u.system_user_id
LEFT JOIN
	public.groups g ON g.id = u.system_user_id
WHERE
	ngp.authorizable_type LIKE 'Project'
	AND
	ngp.capability_id = 1
	AND 
	ngp.permission NOT IN (2,4)
	AND
	su.name IS NOT NULL
	AND
	pid NOT IN (1,28,115,121,122,123,132,136,140,171,172,176,187,644,699)
	AND
	wbid NOT IN (39131,39361)
GROUP BY
	wbid
	
UNION

-- 2. Get Workbook permissions from next_gen_permissions
------------------------------------------------------------
SELECT DISTINCT
	ngp.authorizable_id AS id
	,STRING_AGG(DISTINCT su.name, ',') AS permitted_user_name	
FROM 
	public.next_gen_permissions ngp
LEFT JOIN
	public.workbooks wb ON wb.id = ngp.authorizable_id
LEFT JOIN
	public.projects p ON p.id = wb.project_id
LEFT JOIN
	public.users u ON u.id = ngp.grantee_id
LEFT JOIN
	public.system_users su ON su.id = u.system_user_id
WHERE
	wb.id IS NOT NULL
	AND
	ngp.authorizable_type LIKE 'Workbook'
	AND
	authorizable_id IS NOT NULL
	AND
	ngp.capability_id = 1
	AND 
	ngp.permission NOT IN (2,4)
	AND
	su.name IS NOT NULL
	AND
	p.id NOT IN (1,28,115,121,122,123,132,136,140,171,172,176,187,644,699)
	AND
	wb.id NOT IN (39131,39361)
GROUP BY
	ngp.authorizable_id

ORDER BY
	id
;

------------------------------------------------------------
-- NOTE: To confirm the above counts match the total 
-- number of Workbooks from the views table, just comment
-- out the STRING_AGG, ngp.grantee_id and GROUP BY
-- elements
------------------------------------------------------------

Data Sources

------------------------------------------------------------
-- Show all Data Sources
------------------------------------------------------------
SELECT
	d.id
	,d.name
	,d.*
FROM 
	public.datasources d
ORDER BY 
	d.id
;

Views

next_gen_permissions typically shows Views that are "controlled independently" from Workbooks.

Show the set of Views found in the views table.

----------------------------------------------------------
-- 1. Show all Views
----------------------------------------------------------
SELECT
	v.id
	,v.name
FROM 
	public.views v
ORDER BY
	v.id
;

Show the set of Views found inside the next_gen_permissions table. These are typically all the "independently controlled" Views that could have unique permission assignments, and some of them may be missing from the views table, though it's not clear why.

------------------------------------------------------------
-- 2. Show only the Views found in next_gen_permissions 
-- that have direct permissions assignments
------------------------------------------------------------
SELECT DISTINCT
	ngp.authorizable_id
	,ngp.authorizable_type
FROM 
	public.next_gen_permissions ngp
WHERE
	ngp.authorizable_type LIKE 'View'
ORDER BY
	ngp.authorizable_id
;

------------------------------------------------------------
-- Breakdown
------------------------------------------------------------
-- 2a. Set of views in UNLOCKED Workbooks / LOCKED Projects
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.views v
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
FULL OUTER JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = v.id
WHERE
	ngp.authorizable_id IS NOT NULL
	AND
	wb.display_tabs = FALSE
	AND
	p.controlled_permissions_enabled = TRUE
	AND
	ngp.authorizable_type LIKE 'View'

UNION

-- 2b. Set of views in LOCKED Workbooks / UNLOCKED Projects
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.views v
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
FULL OUTER JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = v.id
WHERE
	ngp.authorizable_id IS NOT NULL
	AND
	wb.display_tabs = TRUE
	AND
	p.controlled_permissions_enabled = FALSE
	AND
	ngp.authorizable_type LIKE 'View'

UNION

-- 2c. Set of views in LOCKED Workbooks / LOCKED Projects
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.views v
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
FULL OUTER JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = v.id
WHERE
	ngp.authorizable_id IS NOT NULL
	AND
	wb.display_tabs = TRUE
	AND
	p.controlled_permissions_enabled = TRUE
	AND
	ngp.authorizable_type LIKE 'View'

UNION

-- 2d. Set of views in UNLOCKED Workbooks / UNLOCKED Projects
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.views v
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
FULL OUTER JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = v.id
WHERE
	ngp.authorizable_id IS NOT NULL
	AND
	wb.display_tabs = FALSE
	AND
	p.controlled_permissions_enabled = FALSE
	AND
	ngp.authorizable_type LIKE 'View'
	
EXCEPT 

-- 2e. Subtract the Views found in next_gen_permissions 
-- that are missing from the views table
------------------------------------------------------------
SELECT DISTINCT
	ngp.authorizable_id AS id
	,ngp.authorizable_type
FROM 
	public.views v
FULL OUTER JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = v.id
WHERE
	v.id IS NULL
	AND
	ngp.authorizable_type LIKE 'View'

ORDER BY
	id

Show the set of Views missing from the next_gen_permissions table that will need to inherit from Workbooks and Projects.

------------------------------------------------------------
-- 3. Show only the Views missing from next_gen_permissions
-- that inherit permissions from Workbooks and Projects
------------------------------------------------------------
SELECT DISTINCT
	v.id
FROM 
	public.views v

EXCEPT

SELECT DISTINCT
	ngp.authorizable_id
FROM 
	public.next_gen_permissions ngp
WHERE
	ngp.authorizable_type LIKE 'View'

------------------------------------------------------------
-- Breakdown
------------------------------------------------------------
-- 3a. Views missing from ngp that belong to UNLOCKED wb and LOCKED p
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.views v ON v.id = ngp.authorizable_id
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_id IS Null
	AND
	wb.display_tabs = FALSE
	AND
	p.controlled_permissions_enabled = TRUE

UNION

-- 3b. Views missing from ngp that belong to LOCKED wb and UNLOCKED p
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.views v ON v.id = ngp.authorizable_id
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_id IS Null
	AND
	wb.display_tabs = TRUE
	AND
	p.controlled_permissions_enabled = FALSE

UNION

-- 3c. Views missing from ngp that belong to LOCKED wb and LOCKED p
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.views v ON v.id = ngp.authorizable_id
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_id IS Null
	AND
	wb.display_tabs = TRUE
	AND
	p.controlled_permissions_enabled = TRUE

UNION

-- 3d. Views missing from ngp that belong to UNLOCKED wb and UNLOCKED p
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.views v ON v.id = ngp.authorizable_id
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_id IS Null
	AND
	wb.display_tabs = FALSE
	AND
	p.controlled_permissions_enabled = FALSE

UNION

-- 3e. Views found in ngp that belong to UNLOCKED wb and LOCKED p
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.views v ON v.id = ngp.authorizable_id
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_id IS NOT null
	AND
	ngp.authorizable_type NOT LIKE 'View'
	AND
	wb.display_tabs = FALSE
	AND
	p.controlled_permissions_enabled = TRUE

UNION

-- 3f. Views found in ngp that belong to LOCKED wb and UNLOCKED p
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.views v ON v.id = ngp.authorizable_id
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_id IS NOT null
	AND
	ngp.authorizable_type NOT LIKE 'View'
	AND
	wb.display_tabs = TRUE
	AND
	p.controlled_permissions_enabled = FALSE
	
UNION

-- 3g. Views found in ngp that belong to LOCKED wb and LOCKED p
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.views v ON v.id = ngp.authorizable_id
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_id IS NOT null
	AND
	ngp.authorizable_type NOT LIKE 'View'
	AND
	wb.display_tabs = TRUE
	AND
	p.controlled_permissions_enabled = TRUE

UNION

-- 3h. Views found in ngp that belong to UNLOCKED wb and UNLOCKED p
------------------------------------------------------------
SELECT DISTINCT
	v.id AS id
	,v.name
FROM 
	public.next_gen_permissions ngp
FULL OUTER JOIN
	public.views v ON v.id = ngp.authorizable_id
JOIN
	public.workbooks wb ON wb.id = v.workbook_id
JOIN
	public.projects p ON p.id = wb.project_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_id IS NOT null
	AND
	ngp.authorizable_type NOT LIKE 'View'
	AND
	wb.display_tabs = FALSE
	AND
	p.controlled_permissions_enabled = FALSE

ORDER BY
	id

App-specific queries

Show Tableau Groups Permitting All Users

Show a comma delimited string of organization-wide groups that have at least read access to the View, like the ALL_USERS or ALL_INTERNAL_USERS groups (group_id's 2 and 558 respectively from the groups table). Add all Views with permissions found in next_gen_permissions to the set of Views inheriting permissions from Projects and/or Workbooks. Exclude some Projects and Workbooks from the results.

Tips

• Some workbooks contain both of these groups, so a WHERE clause that tries to filter them with an OR clause will be inaccurate. Instead, return DISTINCT results with a GROUP BY on the workbook key.
• To display the group names as a single value in each result, aggregate them into a single string (or array) of delimited values. As these values can have duplicates, return only DISTINCT items to the aggregation.

------------------------------------------------------------
-- What Views do "All User" type groups have access to?
------------------------------------------------------------
-- 1. Get View permissions from Workbook
------------------------------------------------------------
SELECT DISTINCT
	vid AS id
	,STRING_AGG(DISTINCT grp.name, ',') AS groups_permitting_all_users
	
FROM 
(
	-- set of Views that can inherit permissions from Workbooks
	SELECT DISTINCT
		REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS vid
		,wb.id AS wbid
	FROM 
	(
		-- subset of views missing from ngp
		SELECT DISTINCT
			v.id AS vid
		FROM 
			public.views v
		EXCEPT
		SELECT DISTINCT
			ngp.authorizable_id AS vid
		FROM 
			public.next_gen_permissions ngp
		WHERE
			ngp.authorizable_type LIKE 'View'
	)	subset1
	JOIN
		public.views v ON v.id = subset1.vid
	JOIN
		public.workbooks wb ON wb.id = v.workbook_id
) subset2
JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.wbid
LEFT JOIN
	public.groups grp ON grp.id = ngp.grantee_id
WHERE
	ngp.authorizable_type LIKE 'Workbook'
	AND
	ngp.grantee_id IN (2,558)
GROUP BY
	vid
	
UNION

-- 2. Get View permissions from Project
----------------------------------------------------------
(
SELECT DISTINCT
	vid AS id
	,STRING_AGG(DISTINCT grp.name, ',') AS groups_permitting_all_users
	
FROM 
(	
	-- 	subset of Views that inherit permissions from Project
	SELECT DISTINCT
		vid
		,pid
	FROM 
	(
		-- subset of PROJECT ids related to views
		SELECT DISTINCT
			REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS vid
			,p.id AS pid
		FROM 
		(
			-- subset of all missing views
			SELECT DISTINCT
				v.id AS vid
			FROM 
				public.views v
			EXCEPT
			SELECT DISTINCT
				ngp.authorizable_id AS vid
			FROM 
				public.next_gen_permissions ngp
			WHERE
				ngp.authorizable_type LIKE 'View'
		) 	subset1
		JOIN
			public.views v ON v.id = subset1.vid
		JOIN
			public.workbooks wb ON wb.id = v.workbook_id
		JOIN
			public.projects p ON p.id = wb.project_id
	) 	subset2
	JOIN
		public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.pid
	LEFT JOIN
		public.groups grp ON grp.id = ngp.grantee_id
	WHERE
		ngp.authorizable_type LIKE 'Project'
	
	EXCEPT

	-- Views that could inherit permissions from Project, but
	-- will inherit from Workbooks
	SELECT DISTINCT
		vid
		,pid
	FROM 
	(
		-- subset of Views that can inherit permissions from Workbooks
		SELECT DISTINCT
			vid
			,pid
		FROM 
		(
			-- subset of WORKBOOK and PROJECT ids related to views
			SELECT DISTINCT
				REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS vid
				,wb.id AS wbid
				,p.id AS pid
			FROM 
			(
				-- subset of workbooks missing from ngp
				SELECT DISTINCT
					v.id AS vid
				FROM 
					public.views v
				EXCEPT
				SELECT DISTINCT
					ngp.authorizable_id AS vid
				FROM 
					public.next_gen_permissions ngp
				WHERE
					ngp.authorizable_type LIKE 'View'					
			) 	subset1
			JOIN
				public.views v ON v.id = subset1.vid
			JOIN
				public.workbooks wb ON wb.id = v.workbook_id
			JOIN
				public.projects p ON p.id = wb.project_id
		) 	subset2
		JOIN
			public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.wbid
		LEFT JOIN
			public.groups grp ON grp.id = ngp.grantee_id
		WHERE
			ngp.authorizable_type LIKE 'Workbook'
	) 	subset3
	JOIN
		public.next_gen_permissions ngp ON ngp.authorizable_id = subset3.pid
	LEFT JOIN
		public.groups grp ON grp.id = ngp.grantee_id
	WHERE
		ngp.authorizable_type LIKE 'Project'
) 	main
LEFT JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = main.pid
LEFT JOIN
	public.groups grp ON grp.id = ngp.grantee_id
WHERE
	ngp.authorizable_type LIKE 'Project'
	AND
	ngp.grantee_id IN (2,558)
GROUP BY
	vid
)

UNION

-- 3. Get View permissions from next_gen_permissions
----------------------------------------------------------
SELECT DISTINCT
	REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS id
 	,STRING_AGG(DISTINCT grp.name, ',') AS groups_permitting_all_users
	
FROM
	public.next_gen_permissions ngp
LEFT JOIN
	public.views v ON v.id = ngp.authorizable_id
LEFT JOIN
	public.groups grp ON ngp.grantee_id  = grp.id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_type LIKE 'View'
	AND
	authorizable_id IS NOT NULL
	AND
	ngp.grantee_id IN (2,558)
GROUP BY
	v.repository_url

ORDER BY
	id
;

------------------------------------------------------------
-- NOTE: To confirm the above counts match the total 
-- number of Views from the views table, just comment
-- out the STRING_AGG, ngp.grantee_id and GROUP BY
-- elements
------------------------------------------------------------

Permitted Group User Names

Return a comma delimited string of  unique user names that inherit permissions from a Group with at least read access to the View by adding the set Views with permissions found in next_gen_permissions to the set of Views inheriting from Workbooks and Projects.

------------------------------------------------------------
-- What Views do Users in Groups have read access to?
------------------------------------------------------------
-- 1. Get View permissions from Workbook
------------------------------------------------------------
SELECT DISTINCT
	vid AS id
	,STRING_AGG(DISTINCT su.name, ',') AS permitted_group_user_name	
FROM 
(
	-- set of Views that can inherit permissions from Workbooks
	SELECT DISTINCT
		REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS vid
		,wb.id AS wbid
	FROM 
	(
		-- subset of views missing from ngp
		SELECT DISTINCT
			v.id AS vid
		FROM 
			public.views v
		EXCEPT
		SELECT DISTINCT
			ngp.authorizable_id AS vid
		FROM 
			public.next_gen_permissions ngp
		WHERE
			ngp.authorizable_type LIKE 'View'
			AND
			ngp.grantee_id NOT IN (2,558)
	)	subset1
	JOIN
		public.views v ON v.id = subset1.vid
	JOIN
		public.workbooks wb ON wb.id = v.workbook_id
) subset2
LEFT JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.wbid
LEFT JOIN
	public.groups grp ON grp.id = ngp.grantee_id
LEFT JOIN
	public.group_users ugrp ON ugrp.group_id = grp.id
LEFT JOIN
	public.users u ON u.id = ugrp.user_id
LEFT JOIN
	public.system_users su ON su.id = u.system_user_id
WHERE
	ngp.authorizable_type LIKE 'Workbook'
	AND
	ngp.capability_id = 1
	AND 
	ngp.permission NOT IN (2,4)
	AND
	ngp.grantee_id NOT IN (2,558)
	AND
	su.name IS NOT NULL
GROUP BY
	vid

UNION

-- 2. Get View permissions from Project
----------------------------------------------------------
(
SELECT DISTINCT
	vid AS id
	,STRING_AGG(DISTINCT su.name, ',') AS permitted_group_user_name
FROM 
(	
	-- 	subset of Views that inherit permissions from Project
	SELECT DISTINCT
		vid
		,pid
	FROM 
	(
		-- subset of PROJECT ids related to views 
		SELECT DISTINCT
			REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS vid
			,p.id AS pid
		FROM 
		(
			-- subset of all missing views
			SELECT DISTINCT
				v.id AS vid
			FROM 
				public.views v
			EXCEPT
			SELECT DISTINCT
				ngp.authorizable_id AS vid
			FROM 
				public.next_gen_permissions ngp
			WHERE
				ngp.authorizable_type LIKE 'View'
				AND
				ngp.grantee_id NOT IN (2,558)
		) 	subset1
		JOIN
			public.views v ON v.id = subset1.vid
		JOIN
			public.workbooks wb ON wb.id = v.workbook_id
		JOIN
			public.projects p ON p.id = wb.project_id
	) 	subset2	
	LEFT JOIN
		public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.pid
	LEFT JOIN
		public.groups grp ON grp.id = ngp.grantee_id
	WHERE
		ngp.grantee_id NOT IN (2,558)
		AND
		ngp.authorizable_type LIKE 'Project'
		OR
		ngp.authorizable_type IS NULL
	
	EXCEPT

	-- Views that could inherit permissions from Project, but
	-- will inherit from Workbooks
	SELECT DISTINCT
		vid
		,pid
	FROM 
	(
		-- subset of Views that can inherit permissions from Workbooks
		SELECT DISTINCT
			vid
			,pid
		FROM 
		(
			-- subset of WORKBOOK and PROJECT ids related to views
			SELECT DISTINCT
				REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS vid
				,wb.id AS wbid
				,p.id AS pid
			FROM 
			(
				-- subset of workbooks missing from ngp
				SELECT DISTINCT
					v.id AS vid
				FROM 
					public.views v
				EXCEPT
				SELECT DISTINCT
					ngp.authorizable_id AS vid
				FROM 
					public.next_gen_permissions ngp
				WHERE
					ngp.authorizable_type LIKE 'View'					
					AND
					ngp.grantee_id NOT IN (2,558)
			) 	subset1
			JOIN
				public.views v ON v.id = subset1.vid
			JOIN
				public.workbooks wb ON wb.id = v.workbook_id
			JOIN
				public.projects p ON p.id = wb.project_id
		) 	subset2
		JOIN
			public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.wbid
		LEFT JOIN
			public.groups grp ON grp.id = ngp.grantee_id
		WHERE
			ngp.authorizable_type LIKE 'Workbook'
	) 	subset3
	LEFT JOIN
		public.next_gen_permissions ngp ON ngp.authorizable_id = subset3.pid
	LEFT JOIN
		public.groups grp ON grp.id = ngp.grantee_id
	WHERE
		ngp.grantee_id NOT IN (2,558)
		AND
		ngp.authorizable_type LIKE 'Project'
		OR
		ngp.authorizable_type IS NULL	
) 	main	
LEFT JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = main.pid
LEFT JOIN
	public.groups grp ON ngp.grantee_id  = grp.id
LEFT JOIN
	public.group_users ugrp ON ugrp.group_id = grp.id
LEFT JOIN
	public.users u ON u.id = ugrp.user_id
LEFT JOIN
	public.system_users su ON su.id = u.system_user_id
WHERE
	main.vid IS NOT NULL
	AND
	ngp.capability_id = 1
	AND 
	ngp.permission NOT IN (2,4)
	AND
	ngp.grantee_id NOT IN (2,558)
	AND
	su.name IS NOT NULL
GROUP BY
	vid
)
	
UNION

-- 3. Get View permissions from next_gen_permissions
------------------------------------------------------------
SELECT DISTINCT
	REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS id
 	,STRING_AGG(DISTINCT su.name, ',') AS permitted_group_user_name
FROM 
	public.next_gen_permissions ngp
JOIN
	public.views v ON v.id = ngp.authorizable_id
LEFT JOIN
	public.groups grp ON ngp.grantee_id  = grp.id
LEFT JOIN
	public.group_users ugrp ON ugrp.group_id = grp.id
LEFT JOIN
	public.users u ON u.id = ugrp.user_id
LEFT JOIN
	public.system_users su ON su.id = u.system_user_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_type LIKE 'View'
	AND
	authorizable_id IS NOT NULL
	AND
	ngp.capability_id = 1
	AND 
	ngp.permission NOT IN (2,4)
	AND
	ngp.grantee_id NOT IN (2,558)
	AND
	su.name IS NOT NULL
GROUP BY
	v.repository_url

ORDER BY
	id
;

------------------------------------------------------------
-- NOTE: To confirm the above counts match the total 
-- number of Workbooks from the views table, just comment
-- out the STRING_AGG, ngp.grantee_id and GROUP BY
-- elements
------------------------------------------------------------

Permitted User Names

Show a comma delimited string of unique user names with permissions assigned directly to Views by adding the set Views with permissions found in next_gen_permissions to the set of Views inheriting from Workbooks and Projects.

------------------------------------------------------------
-- What Views do Users have read access to?
------------------------------------------------------------
-- 1. Get View permissions from Workbook
------------------------------------------------------------
SELECT DISTINCT
	vid AS id
	,STRING_AGG(DISTINCT su.name, ',') AS permitted_user_name
FROM 
(
	-- set of Views that can inherit permissions from Workbooks
	SELECT DISTINCT
		REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS vid
		,wb.id AS wbid
	FROM 
	(
		-- subset of Views missing from ngp
		SELECT DISTINCT
			v.id AS vid
		FROM 
			public.views v
		EXCEPT
		SELECT DISTINCT
			ngp.authorizable_id AS vid
		FROM 
			public.next_gen_permissions ngp
		WHERE
			ngp.authorizable_type LIKE 'View'
	) subset1
	JOIN
		public.views v ON v.id = subset1.vid
	JOIN
		public.workbooks wb ON wb.id = v.workbook_id
) subset2
JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.wbid
LEFT JOIN
	public.users u ON u.id = ngp.grantee_id
LEFT JOIN
	public.system_users su ON su.id = u.system_user_id
LEFT JOIN
	public.groups g ON g.id = u.system_user_id
WHERE
	ngp.authorizable_type LIKE 'Workbook'
	AND
	ngp.capability_id = 1
	AND 
	ngp.permission NOT IN (2,4)
	AND
	ngp.grantee_id NOT IN (2,558)
GROUP BY
	vid

UNION

-- 2. Get View permissions from Project
----------------------------------------------------------
(
SELECT DISTINCT
	vid AS id
 	,STRING_AGG(DISTINCT su.name, ',') AS permitted_user_name
FROM 
(	
	-- 	subset of Views that inherit permissions from Project
	SELECT DISTINCT
		vid
		,pid
	FROM 
	(
		-- subset of PROJECT ids related to views
		SELECT DISTINCT
			REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS vid
			,p.id AS pid
		FROM 
		(
			-- subset of all missing views
			SELECT DISTINCT
				v.id AS vid
			FROM 
				public.views v
			EXCEPT
			SELECT DISTINCT
				ngp.authorizable_id AS vid
			FROM 
				public.next_gen_permissions ngp
			WHERE
				ngp.authorizable_type LIKE 'View'
		) 	subset1
		JOIN
			public.views v ON v.id = subset1.vid
		JOIN
			public.workbooks wb ON wb.id = v.workbook_id
		JOIN
			public.projects p ON p.id = wb.project_id
	) 	subset2
	JOIN
		public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.pid
	LEFT JOIN
		public.users u ON u.id = ngp.grantee_id
	LEFT JOIN
		public.system_users su ON su.id = u.system_user_id
	WHERE
		ngp.authorizable_type LIKE 'Project'
	
	EXCEPT

	-- Views that could inherit permissions from Project, but
	-- will inherit from Workbooks
	SELECT DISTINCT
		vid
		,pid
	FROM 
	(
		-- subset of Views that can inherit permissions from Workbooks
		SELECT DISTINCT
			vid
			,pid
		FROM 
		(
			-- subset of WORKBOOK and PROJECT ids related to views
			SELECT DISTINCT
				REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS vid
				,wb.id AS wbid
				,p.id AS pid
			FROM 
			(
				-- subset of workbooks missing from ngp
				SELECT DISTINCT
					v.id AS vid
				FROM 
					public.views v
				EXCEPT
				SELECT DISTINCT
					ngp.authorizable_id AS vid
				FROM 
					public.next_gen_permissions ngp
				WHERE
					ngp.authorizable_type LIKE 'View'					
			) 	subset1
			JOIN
				public.views v ON v.id = subset1.vid
			JOIN
				public.workbooks wb ON wb.id = v.workbook_id
			JOIN
				public.projects p ON p.id = wb.project_id
		) 	subset2
		JOIN
			public.next_gen_permissions ngp ON ngp.authorizable_id = subset2.wbid
		LEFT JOIN
			public.groups grp ON grp.id = ngp.grantee_id
		WHERE
			ngp.authorizable_type LIKE 'Workbook'
	) 	subset3
	JOIN
		public.next_gen_permissions ngp ON ngp.authorizable_id = subset3.pid
	LEFT JOIN
		public.users u ON u.id = ngp.grantee_id
	LEFT JOIN
		public.system_users su ON su.id = u.system_user_id
	WHERE
		ngp.authorizable_type LIKE 'Project'
) 	main
LEFT JOIN
	public.next_gen_permissions ngp ON ngp.authorizable_id = main.pid
LEFT JOIN
	public.users u ON u.id = ngp.grantee_id
LEFT JOIN
	public.system_users su ON su.id = u.system_user_id
LEFT JOIN
	public.groups g ON g.id = u.system_user_id
WHERE
	ngp.authorizable_type LIKE 'Project'
	AND
	authorizable_id IS NOT NULL
	AND
	ngp.capability_id = 1
	AND 
	ngp.permission NOT IN (2,4)
	AND 
	ngp.grantee_id NOT IN (2,558)
GROUP BY
	vid
)
 
UNION

-- 3. Get View permissions from next_gen_permissions
----------------------------------------------------------
SELECT DISTINCT
	REPLACE(REGEXP_REPLACE(SUBSTRING(v.repository_url, '[^.*views].*'),'sheets/',''),'/','.') AS id
 	,STRING_AGG(DISTINCT su.name, ',') AS permitted_user_name	
FROM 
	public.next_gen_permissions ngp
LEFT JOIN
	public.views v ON v.id = ngp.authorizable_id
LEFT JOIN
	public.users u ON u.id = ngp.grantee_id
LEFT JOIN
	public.system_users su ON su.id = u.system_user_id
LEFT JOIN
	public.groups g ON g.id = u.system_user_id
WHERE
	v.id IS NOT NULL
	AND
	ngp.authorizable_type LIKE 'View'
	AND
	authorizable_id IS NOT NULL
	AND
	ngp.capability_id = 1
	AND 
	ngp.permission NOT IN (2,4)
	AND 
	ngp.grantee_id NOT IN (2,558)
GROUP BY
	v.repository_url

ORDER BY
	id
;

------------------------------------------------------------
-- NOTE: To confirm the above counts match the total 
-- number of Views from the views table, just comment
-- out the STRING_AGG, ngp.grantee_id and GROUP BY
-- elements
------------------------------------------------------------

Users & Groups

Show all the users in the All Users or All Internal Users groups

SELECT
	su.name AS username
	,g.name AS group
FROM
	public.users u
JOIN
	public.group_users gu ON gu.user_id = u.id
JOIN
	public.groups g ON g.id = gu.group_id
JOIN
	public.system_users su ON su.id = u.system_user_id
WHERE
	gu.group_id IN (2,558)
ORDER BY
	su.name
;