6 Security Role Permissions Inspector Shared by Jeff Richmond, The Well Community Church 2 years ago 11.0 General Advanced 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 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/9/2023 11:58:55 AM ******/ 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), LinkableEntityTypeName varchar(50), LinkableEntityID int, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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) + ' > ' WHEN L.ID IS NOT NULL THEN L.[Name] + ' > ' ELSE '' END + B.[Name] + CASE WHEN T.[Name] <> B.[Name] THEN ' [' + T.[Name] + ']' ELSE '' END AS LongName, ISNULL(P.ID, ISNULL(L.ID, S.ID)) AS Parent1ID, ISNULL(ISNULL(P.InternalName, P.PageTitle), ISNULL(L.[Name], S.[Name])) AS Parent1Name, CASE WHEN L.ID IS NOT NULL THEN S.ID ELSE NULL END AS Parent2ID, CASE WHEN L.ID IS NOT NULL THEN S.[Name] ELSE NULL END AS Parent2Name, CASE WHEN P.ID IS NOT NULL THEN S.ID ELSE NULL END AS Parent3ID, CASE WHEN P.ID IS NOT NULL THEN S.[Name] ELSE NULL END AS Parent3Name, CASE WHEN B.SiteID IS NOT NULL THEN 'Site' WHEN B.LayoutID IS NOT NULL THEN 'Layout' ELSE 'Page' END AS LinkableEntityTypeName, ISNULL(P.ID, ISNULL(B.LayoutID, ISNULL(B.SiteID, 0))) AS LinkableEntityID, 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 = B.LayoutID OR L.ID = P.LayoutID LEFT JOIN [Site] S ON S.ID = B.SiteID OR 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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.WorkflowActivityType' BEGIN INSERT INTO @Entity SELECT A.[Name], W.[Name] + ' > ' + A.[Name] AS LongName, W.ID AS Parent1ID, W.[Name] AS Parent1Name, C.ID AS Parent2ID, C.[Name] AS Parent2Name, PC.ID AS Parent3ID, PC.[Name] AS Parent3Name, 'Workflow Type' AS LinkableEntityTypeName, W.ID AS LinkableEntityID, NULL AS TypeID, NULL AS TypeName, 'fas fa-cubes' AS EntityIconCssClass, ISNULL(W.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass, A.IsActive FROM WorkflowActivityType A INNER JOIN WorkflowType W ON W.ID = A.WorkflowTypeId 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 A.ID = @EntityID END ELSE IF @EntityType = 'Rock.Model.WorkflowActionType' BEGIN INSERT INTO @Entity SELECT A2.[Name], W.[Name] + ' > ' + A1.[Name] + ' > ' + A2.[Name] AS LongName, A1.ID AS Parent1ID, A1.[Name] AS Parent1Name, W.ID AS Parent2ID, W.[Name] AS Parent2Name, C.ID AS Parent3ID, C.[Name] AS Parent3Name, 'Workflow Type' AS LinkableEntityTypeName, W.ID AS LinkableEntityID, NULL AS TypeID, NULL AS TypeName, 'fas fa-cube' AS EntityIconCssClass, ISNULL(W.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass, A1.IsActive FROM WorkflowActionType A2 INNER JOIN WorkflowActivityType A1 ON A1.ID = A2.ActivityTypeId INNER JOIN WorkflowType W ON W.ID = A1.WorkflowTypeId 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 A2.ID = @EntityID END ELSE IF @EntityType = 'Rock.Model.Workflow' BEGIN INSERT INTO @Entity SELECT W.[Name], WT.[Name] + ' > ' + W.[Name] AS LongName, WT.ID AS Parent1ID, WT.[Name] AS Parent1Name, C.ID AS Parent2ID, C.[Name] AS Parent2Name, PC.ID AS Parent3ID, PC.[Name] AS Parent3Name, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, NULL AS TypeID, NULL AS TypeName, 'fas fa-random' AS EntityIconCssClass, ISNULL(WT.IconCssClass, ISNULL(C.IconCssClass, ISNULL(PC.IconCssClass, GPC.IconCssClass))) AS IconCssClass, WT.IsActive FROM Workflow W INNER JOIN WorkflowType WT ON WT.ID = W.WorkflowTypeId INNER JOIN Category C ON C.ID = WT.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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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], ISNULL(T.FriendlyName, '[Global]') + ' > ' + 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, T.ID AS TypeID, ISNULL(T.FriendlyName, '[Global]') 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 LEFT 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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 + ' ' + A.[Path] AS [Name], C.[Name] + ': ' + A.Method + ' ' + A.[Path] AS LongName, C.ID AS Parent1ID, C.[Name] AS Parent1Name, NULL AS Parent2ID, NULL AS Parent2Name, NULL AS Parent3ID, NULL AS Parent3Name, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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 AS [Name], 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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, NULL AS LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 LinkableEntityTypeName, NULL AS LinkableEntityID, 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 IF @EntityType = 'Rock.Model.ServiceJob' 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 LinkableEntityTypeName, NULL AS LinkableEntityID, NULL AS TypeID, NULL AS TypeName, 'fa fa-clock-o' AS EntityIconCssClass, NULL AS IconCssClass, CAST(1 AS bit) AS IsActive FROM ServiceJob 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 LinkableEntityTypeName, NULL AS LinkableEntityID, NULL AS TypeID, NULL AS TypeName, 'fa fa-box' AS EntityIconCssClass, 'fa fa-box' AS IconCssClass, CAST(1 AS bit) AS IsActive FROM EntityType WHERE ID = @EntityTypeID END RETURN END GO 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. 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 Make a note of the ID of your new report page for later 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) 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' -%} {%- comment %} redirect if the page query value is 1 {% endcomment -%} {{ 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 -%} The remaining steps are optional, but are helpful for quickly viewing the permissions for a specific security role straight from the Security Roles page Go to the Security Roles page at Internal Homepage > Admin Tools > Rock Settings > Security > Security Roles Open the Block Properties for the Security Role Groups (Group List) block and go to the Custom Grid Options tab 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. 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. Follow Up Please don't hesitate to leave a comment or hit me up on Rock Chat (@JeffRichmond) if you have questions or find any issues with this recipe. Change Log 2021-06-22 - Initial Version 2022-05-03 - Added global attribute handling 2023-06-09 - Updated entity details SQL function with more entity types