Query:
DECLARE @PersonGUID AS varchar(36) = @Person
DECLARE @PersonID AS int
DECLARE @SiteID AS int = @Website
IF @PersonGUID = '' BEGIN SET @PersonGUID = '{{ CurrentPerson.PrimaryAlias.Guid }}' END
SELECT @PersonID = PersonID FROM PersonAlias WHERE GUID = @PersonGUID
DECLARE @GlobalEntityTypeID AS int
DECLARE @SiteEntityTypeID AS int
DECLARE @PageEntityTypeID AS int
DECLARE @BlockEntityTypeID AS int
SELECT @GlobalEntityTypeID = ID FROM EntityType WHERE [Name] = 'Rock.Security.GlobalDefault'
SELECT @SiteEntityTypeID = ID FROM EntityType WHERE [Name] = 'Rock.Model.Site'
SELECT @PageEntityTypeID = ID FROM EntityType WHERE [Name] = 'Rock.Model.Page'
SELECT @BlockEntityTypeID = ID FROM EntityType WHERE [Name] = 'Rock.Model.Block'
DROP TABLE IF EXISTS #PAGETREE
DROP TABLE IF EXISTS #PERMISSIONS
DROP TABLE IF EXISTS #PAGEPERMITS
DROP TABLE IF EXISTS #BLOCKS
DROP TABLE IF EXISTS #BLOCKPERMITS
-- get details for every page of the selected site, including tree depth
;WITH PageTree_CTE
AS
(
-- initialization
SELECT S.ID AS SiteID, S.[Name] AS SiteName,
P.ID AS PageID, ISNULL(P.ParentPageID, 0) AS ParentPageID, P.InternalName AS PageName, P.PageTitle, P.IconCssClass,
P.ID AS PermitPageID, P.InternalName AS PermitPageName, P.PageTitle AS PermitPageTitle,
P.ParentPageID AS NextParentPageID,
CAST(0 AS bit) AS IsAncestor,
CAST(P.[Order] AS int) AS PageSort, CAST(0 AS int) AS Depth
FROM [Page] P
INNER JOIN Layout L ON L.ID = P.LayoutID
INNER JOIN [Site] S ON S.ID = L.SiteID
WHERE S.ID = @SiteID
UNION ALL
-- recursive execution
SELECT CP.SiteID, CP.SiteName,
CP.PageID, CP.ParentPageID, CP.PageName, CP.PageTitle, CP.IconCssClass,
P.ID AS PermitPageID, P.InternalName AS PermitPageName, P.PageTitle AS PermitPageTitle,
P.ParentPageID AS NextParentPageID,
CAST(1 AS bit) AS IsAncestor,
CAST(CP.PageSort AS int) AS PageSort, CAST(CP.Depth + 1 AS int) AS Depth
FROM PageTree_CTE CP
INNER JOIN [Page] P ON P.ID = CP.NextParentPageID
)
-- all page ancestors
SELECT *, @PageEntityTypeID AS PermitEntityTypeID
INTO #PAGETREE
FROM PageTree_CTE
UNION ALL
-- include site ancestor
SELECT SiteID, SiteName,
PageID, ParentPageID, PageName, PageTitle, IconCssClass,
SiteID AS PermitPageID, SiteName + ' (Site)' AS PermitPageName, SiteName + ' (Site)' AS PermitPageTitle,
NULL AS NextParentPageID, 1 AS IsAncestor,
PageSort, MAX(Depth) + 1 AS Depth,
@SiteEntityTypeID AS PermitEntityTypeID
FROM PageTree_CTE
GROUP BY SiteID, SiteName, PageID, PageName, PageTitle, IconCssClass, ParentPageID, PageSort
UNION ALL
-- include global default ancestor
SELECT SiteID, SiteName,
PageID, ParentPageID, PageName, PageTitle, IconCssClass,
0 AS PermitPageID, '(Global Default)' AS PermitPageName, '(Global Default)' AS PermitPageTitle,
NULL AS NextParentPageID, 1 AS IsAncestor,
PageSort, MAX(Depth) + 2 AS Depth,
@GlobalEntityTypeID AS PermitEntityTypeID
FROM PageTree_CTE
GROUP BY SiteID, SiteName, PageID, PageName, PageTitle, IconCssClass, ParentPageID, PageSort
-- get all auth records related to the selected person and action verb for each page and any anscestors with auth records defined
SELECT P.SiteID, P.SiteName,
P.PageID, ParentPageID, P.PageName, P.PageTitle, P.IconCssClass,
A.ID AS AuthID, A.EntityID AS AuthEntityID, A.[Action], A.AllowOrDeny,
CAST(CASE WHEN A.EntityTypeID = @PageEntityTypeID AND P.IsAncestor = 0 THEN 0 ELSE 1 END AS bit) AS Inherited,
CASE
WHEN A.EntityTypeID = @PageEntityTypeID AND P.IsAncestor = 0 THEN NULL
WHEN A.EntityTypeID = @PageEntityTypeID THEN P.PermitPageTitle + ' (Page)'
ELSE P.PermitPageTitle
END AS InheritedFrom,
CAST(CASE WHEN A.EntityTypeID = @GlobalEntityTypeID THEN 1 ELSE 0 END AS bit) AS IsGlobal,
A.SpecialRole, A.GroupId, A.PersonAliasID,
CASE
WHEN A.SpecialRole = 1 THEN '[All Users]'
WHEN A.SpecialRole = 2 THEN '[Authenticated Users]'
WHEN PP.ID IS NOT NULL THEN PP.NickName + ' ' + PP.LastName
ELSE G.[Name]
END AS SecurityRole,
CAST(CASE
WHEN A.SpecialRole IN (1,2) OR PA.PersonID = @PersonID THEN 1
WHEN M.ID IS NULL THEN 0
ELSE 1
END AS bit) AS IsMatch,
P.PageSort, P.Depth, A.[Order] AS AuthSort
INTO #PERMISSIONS
FROM #PAGETREE P
INNER JOIN Auth A ON A.EntityID = P.PermitPageID AND A.EntityTypeID = P.PermitEntityTypeID AND A.[Action] = @Action
LEFT JOIN [Group] G ON G.ID = A.GroupID
LEFT JOIN GroupMember M ON M.GroupID = G.ID AND M.PersonID = @PersonID AND M.IsArchived = 0 AND M.GroupMemberStatus = 1
LEFT JOIN PersonAlias PA ON PA.ID = A.PersonAliasID
LEFT JOIN Person PP ON PP.ID = PA.PersonID
-- calculate effective permissions for each page
SELECT X.SiteID, X.PageID, X.ParentPageID, X.PageName, X.IconCssClass, X.PageTitle, X.AllowOrDeny, X.InheritedFrom, X.SecurityRole, P.PageSort
INTO #PAGEPERMITS
FROM #PAGETREE P
CROSS APPLY (SELECT TOP 1 * FROM #PERMISSIONS
WHERE IsMatch = 1
AND PageID = P.PageID
ORDER BY SiteName, PageSort, PageName, PageID, IsGlobal, Inherited, Depth, AuthSort) X
WHERE P.IsAncestor = 0
-- get details for the blocks on each page
SELECT P.PageID, P.PageName, B.ID AS BlockID, B.Zone, B.Name AS Block, T.Name AS BlockType, B.[Order] AS BlockSort
INTO #BLOCKS
FROM #PAGEPERMITS P
INNER JOIN Block B ON B.PageID = P.PageID
INNER JOIN BlockType T ON T.ID = B.BlockTypeID
-- get all auth records related to the selected person for each block
SELECT B.PageID, B.PageName, B.BlockID, B.Zone, B.Block, B.BlockType,
A.AllowOrDeny, NULL AS InheritedFrom,
CASE
WHEN A.SpecialRole = 1 THEN '[All Users]'
WHEN A.SpecialRole = 2 THEN '[Authenticated Users]'
WHEN PP.ID IS NOT NULL THEN PP.NickName + ' ' + PP.LastName
ELSE G.[Name]
END AS SecurityRole,
CAST(CASE
WHEN A.SpecialRole IN (1,2) OR PA.PersonID = @PersonID THEN 1
WHEN M.ID IS NULL THEN 0
ELSE 1
END AS bit) AS IsMatch,
A.[Order] AS AuthSort
INTO #BLOCKPERMITS
FROM #BLOCKS B
INNER JOIN Auth A ON A.EntityID = B.BlockID AND A.EntityTypeID = @BlockEntityTypeID AND A.[Action] = @Action
LEFT JOIN [Group] G ON G.ID = A.GroupID
LEFT JOIN GroupMember M ON M.GroupID = G.ID AND M.PersonID = @PersonID AND M.IsArchived = 0 AND M.GroupMemberStatus = 1
LEFT JOIN PersonAlias PA ON PA.ID = A.PersonAliasID
LEFT JOIN Person PP ON PP.ID = PA.PersonID
UNION ALL
SELECT B.PageID, B.PageName, B.BlockID, B.Zone, B.Block, B.BlockType,
P.AllowOrDeny, P.InheritedFrom, P.SecurityRole,
CAST(1 AS bit) AS IsMatch, 999 AS AuthSort
FROM #BLOCKS B
INNER JOIN #PAGEPERMITS P ON P.PageID = B.PageID
-- return the site name
SELECT Name FROM Site WHERE ID = @SiteID
-- return the effective page permissions (inheritance not taken into account yet)
SELECT * FROM #PAGEPERMITS ORDER BY PageSort, PageName, PageID
-- return the effective block permissions (inheritance not taken into account yet)
SELECT B.PageID, B.BlockID, B.Block, B.BlockType, B.Zone, X.AllowOrDeny, X.InheritedFrom, X.SecurityRole
FROM #BLOCKS B
CROSS APPLY (SELECT TOP 1 * FROM #BLOCKPERMITS WHERE BlockID = B.BlockID AND IsMatch = 1 ORDER BY AuthSort) X
ORDER BY B.PageID, B.Zone, B.BlockSort, B.Block
-- clean up
DROP TABLE IF EXISTS #PAGETREE
DROP TABLE IF EXISTS #PERMISSIONS
DROP TABLE IF EXISTS #PAGEPERMITS
DROP TABLE IF EXISTS #BLOCKS
DROP TABLE IF EXISTS #BLOCKPERMITS