Description

This recipe creates a new page where you can see all of the entity permissions assigned to any given security role. The list of permissions can also be filtered by entity type, name, or status, as well as the permission action and status.

NOTE: This is simply a list of the Auth records that exist for any given role and entity. It does not take permission inheritance into account.

The Content Pagination Shortcode recipe is used in this recipe, but isn't technically required if you have a different way of implementing content pagination.

The only reason this recipe is marked "Advanced" is that it requires you to create a new SQL Table-Value Function in the database. Not difficult, but a little sketchy for anyone not comfortable with working directly with the database. Please don't mess with this if you're not sure what you're doing.

How To

  1. Run the following SQL code on the database to create the ufnWell_GetEntityDetails table-value function. This function allows you to pass in an entity type ID and an entity ID and get back some very basic details for the matching entity. Currently the function only supports the entity types that most frequently had Auth records attached to them in our Rock instance, but it wouldn't be too difficult to add additional entities if you need them. The function is basically just a series of else if statements with a specific query for each entity type.

    /****** Object:  UserDefinedFunction [dbo].[ufnWell_GetEntityDetails]    Script Date: 6/24/2021 12:52:06 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE FUNCTION [dbo].[ufnWell_GetEntityDetails](@EntityTypeID int, @EntityID int)
    RETURNS @Entity TABLE 
        (
            [Name] varchar(500), LongName varchar(1000),
            Parent1ID int, Parent1Name varchar(500),
            Parent2ID int, Parent2Name varchar(500),
            Parent3ID int, Parent3Name varchar(500),
            TypeID int, TypeName varchar(500), 
            EntityIconCssClass varchar(100),
            IconCssClass varchar(100), IsActive bit
        )
    AS
    BEGIN
        DECLARE @Name AS varchar(1000)
        DECLARE @EntityType AS varchar(100)
        
        SELECT @EntityType = [Name] FROM EntityType WHERE ID = @EntityTypeID
    
        IF @EntityType = 'Rock.Model.Page' BEGIN
            INSERT INTO @Entity
            SELECT ISNULL(P.InternalName, P.PageTitle) AS [Name],
                CASE WHEN S.ID IS NOT NULL THEN S.[Name] + ' > ' ELSE '' END + ISNULL(P.InternalName, P.PageTitle) AS LongName,
                PP.ID AS Parent1ID, ISNULL(PP.InternalName, PP.PageTitle) AS Parent1Name,
                L.ID AS Parent2ID, L.[Name] AS Parent2Name,
                S.ID AS Parent3ID, S.[Name] AS Parent3Name,
                NULL AS TypeID, NULL AS TypeName, 'far fa-file' AS EntityIconCssClass,
                ISNULL(P.IconCssClass, PP.IconCssClass) AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM [Page] P LEFT JOIN
                [Page] PP ON PP.ID = P.ParentPageId LEFT JOIN
                Layout L ON L.ID = P.LayoutID LEFT JOIN
                [Site] S ON S.ID = L.SiteID 
            WHERE P.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Site' BEGIN
            INSERT INTO @Entity
            SELECT [Name], [Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName, 
                'fas fa-desktop' AS EntityIconCssClass,
                'fas fa-desktop' AS IconCssClass, IsActive
            FROM [Site]
            WHERE ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Block' BEGIN
            INSERT INTO @Entity
            SELECT B.[Name], 
                CASE WHEN S.ID IS NOT NULL THEN S.[Name] + ' > ' ELSE '' END 
                    + CASE WHEN P.ID IS NOT NULL THEN ISNULL(P.InternalName, P.PageTitle) + ' > ' ELSE '' END + B.[Name] 
                    + CASE WHEN T.[Name] <> B.[Name] THEN ' [' + T.[Name] + ']' ELSE '' END AS LongName,
                L.ID AS Parent1ID, L.[Name] AS Parent1Name, 
                P.ID AS Parent2ID, ISNULL(P.InternalName, P.PageTitle) AS Parent2Name, 
                S.ID AS Parent3ID, S.[Name] AS Parent3Name,
                T.ID AS TypeID, T.[Name] AS TypeName, 'fas fa-cube' AS EntityIconCssClass,
                'fas fa-cube' AS IconCssClass, CAST(1 AS bit) AS IsActive
            FROM [Block] B INNER JOIN
                BlockType T ON T.ID = B.BlockTypeID LEFT JOIN
                [Page] P ON P.ID = B.PageID LEFT JOIN
                Layout L ON L.ID = P.LayoutID LEFT JOIN
                [Site] S ON S.ID = L.SiteID 
            WHERE B.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Group' BEGIN
            INSERT INTO @Entity
            SELECT G.[Name], T.[Name] + ' > ' + G.[Name] AS LongName,
                PG.ID AS Parent1ID, PG.[Name] AS Parent1Name, 
                GPG.ID AS Parent2ID, GPG.[Name] AS Parent2Name, 
                GGPG.ID AS Parent3ID, GGPG.[Name] AS Parent3Name, 
                T.ID AS TypeID, T.[Name] AS TypeName, 
                'fas fa-users' AS EntityIconCssClass, T.IconCssClass, 
                CAST(CASE WHEN G.IsActive = 1 AND G.IsArchived = 0 THEN 1 ELSE 0 END AS bit) AS IsActive
            FROM [Group] G INNER JOIN
                GroupType T ON T.ID = G.GroupTypeID LEFT JOIN
                [Group] PG ON PG.ID = G.ParentGroupId LEFT JOIN
                [Group] GPG ON GPG.ID = PG.ParentGroupId LEFT JOIN
                [Group] GGPG ON GGPG.ID = GPG.ParentGroupId
            WHERE G.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.WorkflowType' BEGIN
            INSERT INTO @Entity
            SELECT W.[Name], W.[Name] AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName, 'fas fa-random' AS EntityIconCssClass,
                ISNULL(W.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass,
                W.IsActive
            FROM WorkflowType W INNER JOIN
                Category C ON C.ID = W.CategoryId LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryId LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryId
            WHERE W.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.DataView' BEGIN
            INSERT INTO @Entity
            SELECT V.[Name], V.[Name] AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName, 'fas fa-filter' AS EntityIconCssClass,
                ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM DataView V INNER JOIN
                Category C ON C.ID = V.CategoryId LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryId LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryId
            WHERE V.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Attribute' BEGIN
            INSERT INTO @Entity
            SELECT TOP 1 A.[Name], T.FriendlyName + ' > ' + A.[Name] AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                T.ID AS TypeID, T.FriendlyName AS TypeName, 'fas fa-list' AS EntityIconCssClass,
                ISNULL(A.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass,
                A.IsActive
            FROM Attribute A INNER JOIN
                EntityType T ON T.ID = A.EntityTypeID LEFT JOIN
                AttributeCategory AC ON AC.AttributeID = A.ID LEFT JOIN
                Category C ON C.ID = AC.CategoryID LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryID
            WHERE A.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.BinaryFileType' BEGIN
            INSERT INTO @Entity
            SELECT T.[Name], ST.FriendlyName + ' > ' + T.[Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                ST.ID AS TypeID, ST.FriendlyName AS TypeName, 
                'far fa-file-alt' AS EntityIconCssClass,
                T.IconCssClass, CAST(1 AS bit) AS IsActive
            FROM BinaryFileType T INNER JOIN
                EntityType ST ON ST.ID = T.StorageEntityTypeID
            WHERE T.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Category' BEGIN
            INSERT INTO @Entity
            SELECT C.[Name], T.FriendlyName + ' > ' + C.[Name] AS LongName,
                PC.ID AS Parent1ID, PC.[Name] AS Parent1Name, 
                GPC.ID AS Parent2ID, GPC.[Name] AS Parent2Name, 
                GGPC.ID AS Parent3ID, GGPC.[Name] AS Parent3Name, 
                T.ID AS TypeID, T.FriendlyName AS TypeName, 'fas fa-folder' AS EntityIconCssClass,
                ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, ISNULL(GPC.IconCssClass, GGPC.IconCssClass))) AS IconCssClass,
                CAST(1 AS bit) AS IsActive
            FROM Category C INNER JOIN
                EntityType T ON T.ID = C.EntityTypeID LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryID LEFT JOIN
                Category GGPC ON GGPC.ID = GPC.ParentCategoryID
            WHERE C.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.DefinedType' BEGIN
            INSERT INTO @Entity
            SELECT T.[Name], CASE WHEN C.ID IS NOT NULL THEN C.[Name] + ' > ' ELSE '' END + T.[Name] AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName, 'fas fa-book' AS EntityIconCssClass,
                ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass,
                T.IsActive
            FROM DefinedType T LEFT JOIN
                Category C ON C.ID = T.CategoryID LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryID
            WHERE T.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.FinancialAccount' BEGIN
            INSERT INTO @Entity
            SELECT [Name], [Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'fas fa-piggy-bank' AS EntityIconCssClass,
                'fas fa-piggy-bank' AS IconCssClass,
                IsActive
            FROM FinancialAccount
            WHERE ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.FinancialBatch' BEGIN    
            INSERT INTO @Entity    
            SELECT [Name], [Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'fas fa-archive' AS EntityIconCssClass,
                'fas fa-archive' AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM FinancialBatch
            WHERE ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.FinancialPersonSavedAccount' BEGIN
            INSERT INTO @Entity
            SELECT A.[Name], CASE WHEN P.ID IS NOT NULL THEN P.NickName + ' ' + P.LastName + ' > ' ELSE '' END + A.[Name] AS LongName,
                P.ID AS Parent1ID, CASE WHEN P.ID IS NOT NULL THEN P.NickName + ' ' + P.LastName END AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName, 
                'fas fa-money-check' AS EntityIconCssClass,
                'fas fa-money-check' AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM FinancialPersonSavedAccount A LEFT JOIN
                PersonAlias PA ON PA.ID = A.PersonAliasID LEFT JOIN
                Person P ON P.ID = PA.PersonID
            WHERE A.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.GroupType' BEGIN
            INSERT INTO @Entity
            SELECT T.[Name], T.[Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                IT.ID AS TypeID, IT.[Name] AS TypeName, 
                'fas fa-sitemap' AS EntityIconCssClass,
                ISNULL(T.IconCssClass, IT.IconCssClass) AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM GroupType T LEFT JOIN
                GroupType IT ON IT.ID = T.InheritedGroupTypeID
            WHERE T.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Location' BEGIN
            INSERT INTO @Entity
            SELECT L.[Name], CASE WHEN PL.ID IS NOT NULL THEN PL.[Name] + ' > ' ELSE '' END + L.[Name] AS LongName,
                PL.ID AS Parent1ID, PL.[Name] AS Parent1Name, 
                GPL.ID AS Parent2ID, GPL.[Name] AS Parent2Name, 
                GGPL.ID AS Parent3ID, GGPL.[Name] AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'fas fa-map-marker-alt' AS EntityIconCssClass,
                'fas fa-map-marker-alt' AS IconCssClass, L.IsActive
            FROM [Location] L LEFT JOIN
                [Location] PL ON PL.ID = L.ParentLocationID LEFT JOIN
                [Location] GPL ON GPL.ID = PL.ParentLocationID LEFT JOIN
                [Location] GGPL ON PL.ID = GPL.ParentLocationID
            WHERE L.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Metric' BEGIN
            INSERT INTO @Entity
            SELECT TOP 1 M.Title, C.[Name] + ' > ' + M.Title AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName, 'fas fa-signal' AS EntityIconCssClass,
                ISNULL(M.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM Metric M LEFT JOIN
                MetricCategory MC ON MC.MetricID = M.ID LEFT JOIN
                Category C ON C.ID = MC.CategoryID LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryID
            WHERE M.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.NoteType' BEGIN
            INSERT INTO @Entity
            SELECT N.[Name], T.FriendlyName + ' > ' + N.[Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                T.ID AS TypeID, T.FriendlyName AS TypeName, 
                'far fa-sticky-note' AS EntityIconCssClass,
                N.IconCssClass, CAST(1 AS bit) AS IsActive
            FROM NoteType N INNER JOIN
                EntityType T ON T.ID = N.EntityTypeID
            WHERE N.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Report' BEGIN
            INSERT INTO @Entity
            SELECT R.[Name], CASE WHEN C.ID IS NOT NULL THEN C.[Name] + ' > ' ELSE '' END + R.[Name] + ' [' + T.FriendlyName + ']' AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                T.ID AS TypeID, T.FriendlyName AS TypeName, 'fas fa-clipboard-list' AS EntityIconCssClass,
                ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass,
                CAST(1 AS bit) AS IsActive
            FROM Report R INNER JOIN
                EntityType T ON T.ID = R.EntityTypeID LEFT JOIN
                Category C ON C.ID = R.CategoryID LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryID
            WHERE R.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Tag' BEGIN
            INSERT INTO @Entity
            SELECT T.[Name], ET.FriendlyName + ' > ' + T.[Name] AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                ET.ID AS TypeID, ET.FriendlyName AS TypeName,
                'fas fa-tag' AS EntityIconCssClass, 
                T.IconCssClass, T.IsActive
            FROM Tag T INNER JOIN
                EntityType ET ON ET.ID = T.EntityTypeID LEFT JOIN
                Category C ON C.ID = T.CategoryID LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryID
            WHERE T.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.RestController' BEGIN
            INSERT INTO @Entity
            SELECT [Name], [Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName, 
                'fas fa-exchange-alt' AS EntityIconCssClass,
                'fas fa-exchange-alt' AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM RestController
            WHERE ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.Badge' BEGIN
            INSERT INTO @Entity
            SELECT B.[Name], B.[Name] + ' [' + T.FriendlyName + ']' AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                T.ID AS TypeID, T.FriendlyName AS TypeName, 
                'fas fa-icons' AS EntityIconCssClass,
                'fas fa-icons' AS IconCssClass, B.IsActive
            FROM Badge B INNER JOIN
                EntityType T ON T.ID = B.EntityTypeID
            WHERE B.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.RestAction' BEGIN
            INSERT INTO @Entity
            SELECT A.Method, C.[Name] + ': ' + A.Method AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'fas fa-exchange-alt' AS EntityIconCssClass,
                'fas fa-exchange-alt' AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM RestAction A INNER JOIN
                RestController C ON C.ID = A.ControllerID
            WHERE A.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.ContentChannelItem' BEGIN
            INSERT INTO @Entity
            SELECT I.Title, C.[Name] + ' > ' + I.Title AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                T.ID AS TypeID, T.[Name] AS TypeName,
                'far fa-file-code' AS EntityIconCssClass,
                C.IconCssClass, CAST(1 AS bit) AS IsActive
            FROM ContentChannelItem I INNER JOIN
                ContentChannel C ON C.ID = I.ContentChannelID INNER JOIN
                ContentChannelType T ON T.ID = C.ContentChannelTypeID
            WHERE I.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.ContentChannel' BEGIN
            INSERT INTO @Entity
            SELECT TOP 1 CH.[Name], CH.[Name] + ' [' + T.[Name] + ']' AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                T.ID AS TypeID, T.[Name] AS TypeName, 'fas fa-bullhorn' AS EntityIconCssClass,
                ISNULL(CH.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM ContentChannel CH INNER JOIN
                ContentChannelType T ON T.ID = CH.ContentChannelTypeID LEFT JOIN
                ContentChannelCategory CA ON CA.ContentChannelID = CH.ID LEFT JOIN
                Category C ON C.ID = CA.CategoryID LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryID
            WHERE CH.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.RegistrationTemplate' BEGIN
            INSERT INTO @Entity
            SELECT RT.[Name], 
                CASE WHEN PC.ID IS NOT NULL THEN PC.[Name] + ' > ' ELSE '' END 
                    + CASE WHEN C.ID IS NOT NULL THEN C.[Name] + ' > ' ELSE '' END + RT.[Name] AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName, 'far fa-clipboard' AS EntityIconCssClass,
                ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass, 
                RT.IsActive
            FROM RegistrationTemplate RT INNER JOIN
                Category C ON C.ID = RT.CategoryID LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryID
            WHERE RT.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.ConnectionOpportunity' BEGIN
            INSERT INTO @Entity
            SELECT O.[Name], T.[Name] + ' > ' + O.[Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                T.ID AS TypeID, T.[Name] AS TypeName,
                'fas fa-link' AS EntityIconCssClass,
                ISNULL(O.IconCssClass, T.IconCssClass) AS IconCssClass, 
                O.IsActive
            FROM ConnectionOpportunity O INNER JOIN
                ConnectionType T ON T.ID = O.ConnectionTypeId
            WHERE O.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.ConnectionType' BEGIN
            INSERT INTO @Entity
            SELECT T.[Name], T.[Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'fas fa-plug' AS EntityIconCssClass,
                T.IconCssClass, T.IsActive
            FROM ConnectionType T
            WHERE T.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.ContentChannelType' BEGIN
            INSERT INTO @Entity
            SELECT T.[Name], T.[Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'fas fa-project-diagram' AS EntityIconCssClass,
                NULL AS IconCssClass, CAST(1 AS bit) AS IsActive
            FROM ContentChannelType T
            WHERE T.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.EventCalendar' BEGIN
            INSERT INTO @Entity
            SELECT C.[Name], C.[Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'fas fa-calendar-alt' AS EntityIconCssClass,
                C.IconCssClass, C.IsActive
            FROM EventCalendar C
            WHERE C.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.CommunicationTemplate' BEGIN
            INSERT INTO @Entity
            SELECT CT.[Name], CASE WHEN C.ID IS NOT NULL THEN C.[Name] + ' > ' ELSE '' END + CT.[Name] AS LongName,
                C.ID AS Parent1ID, C.[Name] AS Parent1Name, 
                PC.ID AS Parent2ID, PC.[Name] AS Parent2Name, 
                GPC.ID AS Parent3ID, GPC.[Name] AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'far fa-list-alt' AS EntityIconCssClass,
                ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass)) AS IconCssClass, 
                CT.IsActive
            FROM CommunicationTemplate CT INNER JOIN
                Category C ON C.ID = CT.CategoryID LEFT JOIN
                Category PC ON PC.ID = C.ParentCategoryID LEFT JOIN
                Category GPC ON GPC.ID = PC.ParentCategoryID
            WHERE CT.ID = @EntityID
        END
        ELSE IF @EntityType = 'Rock.Model.SignalType' BEGIN
            INSERT INTO @Entity
            SELECT [Name], [Name] AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'fas fa-flag' AS EntityIconCssClass,
                SignalIconCssClass AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM SignalType
            WHERE ID = @EntityID
        END
        ELSE BEGIN
            INSERT INTO @Entity
            SELECT FriendlyName + ' ID ' + CAST(@EntityID AS varchar) AS [Name], FriendlyName + ' ID ' + CAST(@EntityID AS varchar) AS LongName,
                NULL AS Parent1ID, NULL AS Parent1Name, 
                NULL AS Parent2ID, NULL AS Parent2Name, 
                NULL AS Parent3ID, NULL AS Parent3Name, 
                NULL AS TypeID, NULL AS TypeName,
                'fas fa-cube' AS EntityIconCssClass,
                'fas fa-cube' AS IconCssClass, 
                CAST(1 AS bit) AS IsActive
            FROM EntityType 
            WHERE ID = @EntityTypeID
        END
        
        RETURN
    END
    GO
  2. Implement the Content Pagination Shortcode recipe. If you already have your own way of handling content pagination, then you can definitely use that instead. You'll just have to modify the necessary bits of this recipe.
  3. Create a new page under Internal Homepage > Admin Tools > Rock Settings > Security

    • Page Title: Inspect Role Permissions
    • Site: Rock RMS
    • Layout: Full Width
    • Icon CSS Class: fas fa-clipboard-list
  4. Make a note of the ID of your new report page for later
  5. Add a new Page Parameter Filter block to the Main zone
    • Block Title: Filters
    • Show Block Title: Yes
    • Block Title Icon CSS Class: fa fa-filter
    • Filter Button Text: Apply Filter
    • Show Reset Filters Button: Yes
    • Filters Per Row: 2
    • Filters:
      • Security Role
        • Name: Security Role
        • Key: GroupId
        • Field Type: Single-Select
        • Values:
          Adjust the GroupTypeID value near the bottom if the Security Role group type has a different ID in your Rock instance.
          SELECT Value, Text 
          FROM (SELECT 'P' AS Value, '[Individual People]' AS Text, 0 AS Sort
                  UNION ALL
                  SELECT 'S1' AS Value, '[All Users]' AS Text, 1 AS Sort
                  UNION ALL
                  SELECT 'S2' AS Value, '[All Authenticated Users]' AS Text, 2 AS Sort
                  UNION ALL
                  SELECT 'S3' AS Value, '[All Un-Authenticated Users]' AS Text, 3 AS Sort
                  UNION ALL
                  SELECT CAST(ID AS varchar) AS Value, IIF(IsSecurityRole = 1 AND GroupTypeID <> 1, 'GROUP - ', '') + [Name] AS Text, 4 AS Sort
                  FROM [Group] 
                  WHERE GroupTypeID = 1 --Security Role Group Type
                      OR IsSecurityRole = 1
              ) AS Options
          ORDER BY Sort, Text
                                          
        • Control Type: Drop Down List (Enhanced for Long Lists)
        • Default Value: (blank)
      • Entity Type
        • Name: Entity Type
        • Key: EntityType
        • Field Type: Entity Type
        • Default Value: (blank)
      • Entity Name
        • Name: Entity Name
        • Key: EntityName
        • Field Type: Text
        • Max Characters: 200
        • Default Value: (blank)
      • Active Entities Only
        • Name: Active Entities Only
        • Key: ActiveOnly
        • Field Type: Single-Select
        • Values: 1^Yes,0^No
        • Control Type: Drop Down List
        • Default Value: Yes
      • Action
        • Name: Action
        • Key: Action
        • Field Type: Single-Select
        • Values:
          SELECT DISTINCT Action AS Value, Action AS Text, 
              CASE Action 
                  WHEN 'View' THEN 0 
                  WHEN 'Edit' THEN 1 
                  WHEN 'Delete' THEN 2 
                  WHEN 'Administrate' THEN 3 
                  ELSE 99 
              END AS Sort 
          FROM Auth 
          ORDER BY Sort, Action
                                          
        • Control Type: Drop Down List
        • Default Value: (blank)
      • Permission
        • Name: Permission
        • Key: Permission
        • Field Type: Single-Select
        • Values: A^Allow,D^Deny
        • Control Type: Drop Down List
        • Default Value: (blank)
  6. Add a Dynamic Data block to the Main zone
    • Block > Edit Criteria:
      • SQL Query:
        DECLARE @GroupID AS int = 0
        DECLARE @SpecialID AS int = 0
        {%- assign groupID = 'Global' | PageParameter:'GroupId' | Default:'0' -%}
        {%- case groupID -%}
            {%- when 'P' -%}
        SET @GroupID = -1
            {%- when 'S1' -%}
        SET @SpecialID = 1    
            {%- when 'S2' -%}
        SET @SpecialID = 2
            {%- when 'S3' -%}
        SET @SpecialID = 3
            {%- else -%}
        SET @GroupID = {{ groupID | AsInteger }}
        {%- endcase -%}
        DECLARE @EntityTypeGUID AS varchar(36) = '{{ "Global" | PageParameter:"EntityType" }}'
        DECLARE @EntityName AS varchar(36) = '{{ "Global" | PageParameter:"EntityName" }}'
        DECLARE @Action AS varchar(36) = '{{ "Global" | PageParameter:"Action" }}'
        DECLARE @Permission AS varchar(1) = '{{ "Global" | PageParameter:"Permission" }}'
        DECLARE @ActiveOnly AS bit = {{ 'Global' | PageParameter:'ActiveOnly' | Default:'1' | AsInteger }}
        DECLARE @Name AS varchar(250)
        
        SELECT A.ID AS AuthID, A.EntityID, 
            CASE WHEN G.ID IS NOT NULL THEN CAST(G.ID AS varchar) WHEN P.ID IS NOT NULL THEN 'P' + CAST(P.ID AS varchar) END AS RoleID, 
            ISNULL(G.[Name], ISNULL(P.NickName + ' ' + P.LastName, CASE A.SpecialRole WHEN 1 THEN '[All Users]' WHEN 2 THEN '[All Authenticated Users]' WHEN 3 THEN '[All Un-Authenticated Users]' END)) AS Role, 
            T.FriendlyName AS EntityType, A.EntityTypeID, E.Name AS RawEntityName, 
            CASE A.EntityID WHEN 0 THEN '[GLOBAL]' ELSE ISNULL(E.LongName, '[NOT FOUND]') END AS EntityName, 
            E.EntityIconCssClass, E.IconCssClass, E.IsActive, 
            A.[Action], A.AllowOrDeny
        FROM Auth A INNER JOIN
            EntityType T ON T.ID = A.EntityTypeID LEFT JOIN
            [Group] G ON G.ID = A.GroupID LEFT JOIN
            PersonAlias PA ON PA.ID = A.PersonAliasID LEFT JOIN
            Person P ON P.ID = PA.PersonID CROSS APPLY
            (SELECT * FROM dbo.ufnWell_GetEntityDetails(A.EntityTypeID, A.EntityID)) AS E
        WHERE (
                (A.GroupID = @GroupID OR (@GroupID = 0 AND @SpecialID = 0))
                OR (@GroupID = -1 AND A.PersonAliasID IS NOT NULL)
                OR (@GroupID = 0 AND A.SpecialRole = @SpecialID)
            )
            AND (CAST(T.GUID AS varchar(36)) = @EntityTypeGUID OR @EntityTypeGUID = '')
            AND (E.LongName LIKE '%' + @EntityName + '%' OR @EntityName = '')
            AND (A.Action = @Action OR @Action = '')
            AND (A.AlloworDeny = @Permission OR @Permission = '')
            AND (E.IsActive = 1 OR @ActiveOnly = 0)
        ORDER BY Role,
            IIF(A.EntityID = 0, 2, 1), EntityType, 
            ISNULL(E.LongName, 'zzzzzzzzz'), 
            E.IsActive DESC, A.EntityID,
            CASE Action WHEN 'View' THEN 0 WHEN 'Edit' THEN 1 WHEN 'Delete' THEN 3 ELSE 99 END, Action
        	    				
      • Formatted Output:
        Adjust the pageSize value on the first line if you'd prefer to show more or less items per page.
        {%- assign pageSize = 100 -%}
        {%- assign pageNum = 'Global' | PageParameter:'p' | Default:'' -%}
        {%- assign query = 'Global' | Page:'QueryString' -%}
        {%- assign url = 'Global' | Page:'Url' -%}
        {%- assign rootURL = url | Url:'localpath' -%}
        {%- assign firstLoop = true -%}
        {%- for queryParam in query -%}
            {%- assign kvParam = queryParam | PropertyToKeyValue -%}
            {%- if kvParam.Key != 'p' and  kvParam.Key != 'PageId' -%}
                {%- if firstLoop == true -%}
                    {%- capture rootURL %}{{ rootURL }}?{{ kvParam.Key }}={{ kvParam.Value }}{% endcapture -%}
                    {%- assign firstLoop = false -%}
                {%- else -%}
                    {%- capture rootURL %}{{ rootURL }}&{{ kvParam.Key }}={{ kvParam.Value }}{% endcapture -%}
                {%- endif -%}
            {%- endif -%}
        {%- endfor -%}
        {%- if pageNum == '1' -%}
            {[-_ redirect if the page query value is 1 -]}
            {{ rootURL | PageRedirect }}
        {%- endif -%}
        {%- if pageNum == '' %}{% assign pageNum = '1' %}{% endif -%}
        {%- assign pageNum = pageNum | AsInteger -%}
        {%- assign itemCount = rows | Size -%}
        {%- assign pageCount = itemCount | DividedBy:pageSize | Ceiling -%}
        {%- assign skipCount = pageNum | Minus:1 | Times:pageSize -%}
        {%- assign roleID = 'Global' | PageParameter:'GroupId' | Default:'0' -%}
        <div class="panel panel-block">
            <div class="panel-body">
                <div class="grid grid-panel">
                    <div class="table-responsive">
                        <table class="grid-table table js-sticky-headers sticky-headers table-bordered table-striped">
                            <thead>
                                <tr align="left">
        {%- assign columnCount = 3 -%}
        {%- if roleID == '0' or roleID == 'P' or roleID == 'S1' or roleID == 'S2' or roleID == 'S3' -%}
            {%- assign columnCount = 4 -%}
                                    <th data-priority="1" scope="col">Role</th>
        {%- endif -%}
                                    <th data-priority="1" scope="col">Type</th>
                                    <th data-priority="1" scope="col">Name</th>
                                    <th data-priority="1" scope="col">Permission</th>
                                </tr>
                            </thead>
                            <tbody>
        {%- for row in rows limit:pageSize offset:skipCount -%}
            {%- case row.AllowOrDeny -%}
                {%- when 'A' -%}
                    {%- assign permitName = 'Allow' -%}
                    {%- assign permitClass = 'success' -%}
                    {%- assign permitIcon = 'check-circle' -%}
                {%- when 'D' -%}
                    {%- assign permitName = 'Deny' -%}
                    {%- assign permitClass = 'danger' -%}
                    {%- assign permitIcon = 'times-circle' -%}
                {%- else -%}
                    {%- assign permitName = 'UNKNOWN' -%}
                    {%- assign permitClass = 'warning' -%}
                    {%- assign permitIcon = 'question-circle' -%}
            {%- endcase -%}
                                <tr align="left" title="Auth ID: {{ row.AuthID }}, Role ID: {{ row.RoleID }}, Entity ID: {{ row.EntityID }}{% if row.IsActive == false %} INACTIVE" class="is-inactive{% endif %}" data-key="{{ row.AuthID }}">
            {%- if roleID == '0' or roleID == 'P' or roleID == 'S1' or roleID == 'S2' or roleID == 'S3' -%}
                                    <td data-priority="1">{{ row.Role }}</td>
            {%- endif -%}
                                    <td data-priority="1"><i class="{{ row.EntityIconCssClass | Default:'fa fa-cube' }} fa-fw"></i> {{ row.EntityType }}</td>
                                    <td data-priority="1"><i class="{{ row.IconCssClass | Default:row.EntityIconCssClass | Default:'fa fa-cube' }} fa-fw"></i> {{ row.EntityName }}</td>
                                    <td data-priority="1" class="{{ permitClass }}"><i title="{{ permitName }}" class="text-{{ permitClass }} fas fa-lg fa-{{ permitIcon }}"><span class="sr-only">{{ permitName }}</span></i> {{ row.Action }}</td>
                                </tr>
        {%- endfor -%}
                            </tbody>
                            <tfoot>
        {%- if pageCount > 1 -%}
                                <tr>
                                    <td class="grid-paging" colspan="{{ columnCount }}">
                                        <span class="grid-itemcount">{{ itemCount | Format:'#,##0' }} {{ 'Item' | PluralizeForQuantity:itemCount }}</span>
                                        {[ pager current:'{{ pageNum }}' total:'{{ pageCount }}' rooturl:'{{ rootURL }}' buttoncount:'10' size:'small' class:'grid-pager' usequery:'true' ]}
                                    </td>
                                </tr>
        {%- endif -%}
                            </tfoot>
                        </table>
                    </div>
                </div>
            </div>
        </div>
        	    			    
      • Page Title Lava
        {%- assign group = 'Global' | PageParameter:'GroupId' | Default:'0' | GroupById -%}
        {%- if group %}Permissions for {{ group.Name }}{% else %}Security Role Permissions{% endif -%}
        	    			    
  7. The remaining steps are optional, but are helpful for quickly viewing the permissions for a specific security role straight from the Security Roles page
  8. Go to the Security Roles page at Internal Homepage > Admin Tools > Rock Settings > Security > Security Roles
  9. Open the Block Properties for the Security Role Groups (Group List) block and go to the Custom Grid Options tab
  10. Under Custom Columns, add a new column
    • Column Position: Last Column
    • Offset: 1
    • Header Text: (blank)
    • Header Class: grid-columncommand
    • Item Class: grid-columncommand
    • Lava Template:
      <a href="/page/YOUR_REPORT_PAGE_ID?GroupId={{ Row.Id }}" title="View assigned permissions" class="btn btn-default"><i class="fa fa-list"><span class="sr-only">Permissions</span></i></a>
          			

      Make sure to replace YOUR_REPORT_PAGE_ID with the ID of your new report page.

  11. Save the Block Properties and you're done!

There will be a new button to the right of every role on the Security Roles page that will take you to a list of every permission record that exists for the selected role.

Alternatively, you can go directly to the Inspect Role Permissions page by going to Admin Tools > Security > Inspect Role Permissions. By default the page will show every permission record in the system. The Filters panel can be used to narrow the list down to what you're looking for.

Please leave a comment or hit me up on Rock Chat if you have questions, issues or suggestions for this recipe. Thanks!