Page Security Visualizer

Shared by Jeff Richmond, The Well Community Church
3 months ago

15.0 Security Beginner

This recipe requires the Fluid Lava engine. It will not work if you're still running DotLiquid.

Description

Security in Rock is highly customizable and extremely powerful, but as a result, it can be very difficult to know each person's effective permissions are… even on a fresh Rock install. This recipe is an attempt to visualize a person's permissions for every page and block on a website.

The page will display a hierarchical tree view of every page and block. Each node is color coded to indicate whether the person's effective permission is Allow or Deny. It also shows which ancestor the permission is inherited from as well as which security role or user the person was matched with.

Caveats

This recipe has been very useful for us, but is very likely not perfect. Here are a few things you should keep in mind:

I'm not familiar with every detail of how the security system was built in the background, so it's entirely possible that the effective permissions displayed are not 100% accurate. Even though inheritance is accounted for, there could be other special situations that I'm not aware of that could affect permissions. That being said, I am yet to find a situation that produces invalid results.
The only actions covered by this recipe are "View", "Edit" and "Administrate". Permissions can't currently be checked for other less common actions like "Manage Members".
The queries for this recipe are pretty complex and there's lots of data, so the page loads tend to be a little slow. Ours averages about 5 to 6 seconds when checking permissions for the internal Rock site. It may be faster or slower for you, depending on your server's power and number of pages and blocks in your Rock instance.
Depending on how deep your pages are nested, it is very likely that you will need to scroll horizontally to see everything, especially on smaller screens.

How-To

Create new page under Rock RMS > Admin Tools > Rock Settings > Security:
- Page Title: Visualize Page Security
- Site: Rock RMS
- Layout: Full Width
- Icon CSS Class: fas fa-user-lock
- Page Routes: admin/security/page-security
Add a Page Parameter Filter block to the Main zone

Block Settings:
- Block Title Text: Page Permission Parameters
- Filter Button Text: Refresh Results
- Filter Button Size: Small
- Show Reset Filters Button: No
Filters:
- Person
  - Name: Person
  - Field Type: Person
  - Enable Self Selection: Yes
- Action
  - Name: Action
  - Field Type: Single-Select
  - Values: View,Edit,Administrate
  - Default Value: View
- Website
  - Name: Website
  - Field Type: Site
  - Default Value: Rock RMS

Add a Dynamic Data block to the Main zone

Block Properties:

Name: Security Visualizer
Update Page: No

Edit Criteria:

Parameters: Person=;Action=View;Website=1
Show Grid Filter: No
Customize Results with Lava: Yes

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

Formatted Output:

{%- assign personGUID = 'Global' | PageParameter:'Person' | Default:CurrentPerson.PrimaryAlias.Guid -%}
{%- assign action = 'Global' | PageParameter:'Action' | Default:'View' -%}
{%- assign siteID = 'Global' | PageParameter:'Website' | Default:'1' -%}

{%- assign sitePages = table2.rows -%}
{%- assign breadcrumbs = table1.rows[0].Name -%}
{%- assign blocks = table3.rows -%}

{%- capture childPagesLava -%}
    {%- raw -%}
        {%- assign pages = sitePages | Where:'ParentPageID', parentID | Sort:'Order' -%}
        {%- assign pageCount = pages | Size -%}
        {%- if pageCount > 0 %}
        <ul id="ChildPages{{ parentID }}" class="pages collapse in">
            {%- for page in pages %}
                {%- capture crumb %} <i class=&quot;fa fa-caret-right page-{{ page.PageID }}&quot;></i> {{ page.PageTitle }}{% endcapture -%}
                {%- capture breadcrumbs %}{{ breadcrumbs }}{{ crumb }}{% endcapture %}
                {%- assign cardClass = 'danger' -%}
                {%- if page.AllowOrDeny == 'A' %}{% assign cardClass = 'success' %}{% endif -%}
                {%- assign childCount = sitePages | Where:'ParentPageID',page.PageID | Size -%}
            <li class="d-flex">
                <div class="card border-{{ cardClass }} page{% if parentID == 0 %} root{% endif %} flex-shrink-0">
                    <div class="card-header bg-{{ cardClass }} text-white" data-toggle="tooltip" data-html="true" data-original-title="{{ breadcrumbs }} <small>(ID: {{ page.PageID }})</small>">
                {%- if childCount > 0 %}
                        <span>
                            <i class="{{ page.IconCssClass | Default:'fa fa-file' }}"></i> {{ page.PageTitle }}
                        </span>
                {%- else %}
                        <i class="{{ page.IconCssClass | Default:'fa fa-file' }}"></i> {{ page.PageTitle }}
                {%- endif %}
                    </div> 
                    <div class="card-body">
                        <div class="card-text">
                            <div class="security-details d-flex mb-2">
                                <div class="flex-nowrap mr-2">                                
                {%- if page.AllowOrDeny == 'A' -%}
                                    <span class="label label-success">Allow</span>
                {%- else -%}
                                    <span class="label label-danger">Deny</span>
                {%- endif -%}
                                </div>
                                <div class="flex-wrap flex-grow-1">
                {%- if page.InheritedFrom != null %}
                                    <small><strong>Source:</strong> {{ page.InheritedFrom }}</small>
                {%- else %}
                                    <small><strong>Source:</strong> <span class="text-muted">(Self)</span></small>
                {%- endif -%}<br>
                                    <small><strong>Role/User:</strong> {{ page.SecurityRole }}</small>
                                </div>
                            </div>
                {%- assign pageBlocks = blocks | Where:'PageID',page.PageID -%}
                {%- assign blockCount = pageBlocks | Size -%}
                {%- if blockCount > 0 -%}
                            <div class="page-details mb-2">
                                <small>{{ 'Block' | ToQuantity:blockCount }}</small>,
                                <small>{{ 'Child Page' | ToQuantity:childCount }}</small>
                            </div>
                            <ul class="list-group blocks w-100 mb-0">
                    {%- for block in pageBlocks -%}
                        {%- capture blockTooltip -%}
                            <strong>Block ID:</strong> {{ block.BlockID }}<br>
                            <strong>Type:</strong> {{ block.BlockType }}<br>
                            <strong>Zone:</strong> {{ block.Zone }}<br>
                            {%- if block.InheritedFrom != null -%}
                                <strong>Source:</strong> {{ block.InheritedFrom }}<br>
                            {%- else -%}
                                <strong>Source:</strong> (Self)<br>
                            {%- endif -%}
                                <strong>Role/User:</strong> {{ block.SecurityRole }}
                        {%- endcapture -%}
                                <li class="list-group-item d-flex {% if block.AllowOrDeny == 'A' %}bg-green-100{% else %}bg-red-100{% endif %}" data-toggle="tooltip" data-html="true" title="{{ blockTooltip | StripNewlines }}"> 
                                    <div class="flex-nowrap mr-2">                              
                        {%- if block.AllowOrDeny == 'A' -%}
                                        <span class="label label-success">Allow</span>
                        {%- else -%}
                                        <span class="label label-danger">Deny</span>
                        {%- endif %}
                                    </div>
                                    <div class="flex-wrap flex-grow-1">  
                                        {{ block.Block }}
                                    </div>
                                </li>
                    {%- endfor -%}
                            </ul>
                {%- endif -%}
                        </div>
                    </div>
                </div>
                {%- assign parentID = page.PageID -%}
                {{ childPagesLava | RunLava }}
                {%- capture crumb %} <i class=&quot;fa fa-caret-right page-{{ page.PageID }}&quot;></i> {{ page.PageTitle }}{% endcapture -%}
                {%- assign breadcrumbs = breadcrumbs | Replace:crumb,'' -%}
            </li>
            {%- endfor %}
        </ul>
        {%- endif %}
    {%- endraw -%}
{%- endcapture -%}
<style>
    ul.pages, .pages ul 
    {
        vertical-align: top;
        padding-left: 0;
    }
    .pages ul { display: inline-block; }
    .pages > li 
    {
        list-style: none;
        font-size: 14px;
        white-space: nowrap;
    }
    .pages .page
    {
        display: inline-block;
        width: 250px;
        margin: 0 15px 15px 0;
        white-space: normal;
    }
    .pages .page .card-header { padding: 4px 6px; }
    .pages .page .card-body { padding: 6px; }
    .pages .page .list-group-item { padding: 6px; }
    .pages .blocks li { font-size: 12px; }
    .card:before 
    {
        content: '';
        border: 12px solid transparent;
        border-left: 15px solid #ccc;
        border-right: none;
        position: absolute;
        right: 100%;
        top: 3px;
        margin-right: 1px;
    }
    .root:before { display: none; }
</style>
<div class="panel panel-block">
    <div class="panel-heading">
        <h3 class="panel-title">Website: {{ table1.rows[0].Name }}</h3>
    </div>
    <div class="panel-body permissions">
        {%- assign parentID = 0 -%}
        {{ childPagesLava | RunLava }}
    </div>
</div>

That's it! On initial page load, it should show your effective View permissions for every page and block on your internal Rock website.

Follow Up

Don't hesitate to leave a comment below or hit me up on Rock Chat (@JeffRichmond) if you have questions or find any issues with this recipe.

Please let me know if find a situation where the recipe results don't match the actual permissions set up in your Rock instance. Also, if you come up with better or more efficient ways of doing anything in this recipe, please let me know about that as well. Thanks!

Change Log

2024-05-15 - Initial Version
2024-05-16 - Added note about requiring Fluid

Recipe

Page Security Visualizer

Page Security Visualizer

Description

Caveats

How-To

Follow Up

Change Log

Login to add a comment...