{$page}

This query will provide you with a list of all security rights organized into a “formatted path”. This can only be executed by Cyrious technical support, as the information on all security rights is contained with the SystemData.mdb file and must first be imported into a temporary SQL table named SystemSecurityRightTmp.

An attached list in excel format of security rights for 4.5, 4.6, and 5.5 are attached below.

control_4.50.1111.2501_security_right_tree.xlsx

control_4.6.1210.0101_security_right_tree.xlsx

control_5.5.1503.0601_security_right_tree.xlsx

A comparison of the 4.6 to 5.5 rights is below:

control_5.5_and_4.6_security_right_tree.xlsx

None. This is a selection query and no data is modified in the running of it.

code_formatsql

DECLARE @Spacing INT;

SET @Spacing = 4;

SELECT

ROW_NUMBER() OVER(ORDER BY FormattedPath) AS ROW,

SPACE(@Spacing * (Depth-1)) + NodeName AS FormattedName,

FROM

  ( SELECT

        COALESCE(SystemSecurityRightTmp.ID,          Level5.ID,          Level4.ID,          Level3.ID,          Level2.ID,          Level1.ID         ) AS NodeID,

        COALESCE(SystemSecurityRightTmp.ClassTypeID, Level5.ClassTypeID, Level4.ClassTypeID, Level3.ClassTypeID, Level2.ClassTypeID, Level1.ClassTypeID) AS NodeClassTypeID,

        COALESCE(SystemSecurityRightTmp.IsActive,    Level5.IsActive,    Level4.IsActive,    Level3.IsActive,    Level2.IsActive,    Level1.IsActive   ) AS IsActive,

1. -IsCategory,

                  COALESCE(Level5.ID - Level5.ID + 5, Level4.ID - Level4.ID + 4, Level3.ID - Level3.ID + 3, Level2.ID - Level2.ID + 2, Level1.ID - Level1.ID + 1, 0)

         + COALESCE(SystemSecurityRightTmp.ID - SystemSecurityRightTmp.ID + 1, 0) AS Depth,  -- Depth is equal to the tree level + 1 for the Part

        COALESCE(SystemSecurityRightTmp.Title,    Level5.Title, Level4.Title, Level3.Title, Level2.Title, Level1.Title) AS NodeName,

        Level1.Title AS LevelName,

        Level2.Title AS Level2Name,

        Level3.Title AS Level3Name,

        Level4.Title AS Level4Name,

        Level5.Title AS Level5Name,

        SystemSecurityRightTmp.Title AS SystemSecurityRightTitle,

        Level1.ID AS Level1ID,

        Level2.ID AS Level2ID,

        Level3.ID AS Level3ID,

        Level4.ID AS Level4ID,

        Level5.ID AS Level5ID,

        SystemSecurityRightTmp.ID AS SystemSecurityRightID,

        COALESCE(Level1.Title + '\', '') +

          coalesce(Level2.Title + '\', '') +

          coalesce(Level3.Title + '\', '') +

          coalesce(Level4.Title + '\', '') +

          coalesce(Level5.Title + '\', '') +

          coalesce(' '+SystemSecurityRightTmp.Title, '')

          as FormattedPath

      from SystemSecurityRightTmp as Level1

      JOIN (SELECT 1 AS IsLevel1 UNION SELECT 0) Temp1 ON 1=1 -- create two rows for each of the above

      left join SystemSecurityRightTmp as Level2 on Level2.ParentID = Level1.ID and IsLevel1 = 0

      JOIN (SELECT 1 AS IsLevel2 UNION SELECT 0) Temp2 ON 1=1

      left join SystemSecurityRightTmp as Level3 on Level3.ParentID = Level2.ID and IsLevel1 = 0 and IsLevel2 = 0

      JOIN (SELECT 1 AS IsLevel3 UNION SELECT 0) Temp3 ON 1=1

      left join SystemSecurityRightTmp as Level4 on Level4.ParentID = Level3.ID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0

      JOIN (SELECT 1 AS IsLevel4 UNION SELECT 0) Temp4 ON 1=1

      left join SystemSecurityRightTmp as Level5 on Level5.ParentID = Level4.ID and IsLevel1 = 0 and IsLevel2 = 0 and IsLevel3 = 0 and IsLevel4 = 0

      JOIN (SELECT 1 AS IsLevel5 UNION SELECT 0) Temp5 ON 1=1

1. -JOIN (SELECT 1 AS IsCategory UNION SELECT 0) Temp6 ON 1=1

      left join SystemSecurityRightTmp on SystemSecurityRightTmp.ParentID = coalesce(Level5.ID, Level4.ID, Level3.ID, Level2.ID, Level1.ID)

      where Level1.ParentID IS NULL and Level1.ID > 0

            and (IsLevel1 + IsLevel2 + IsLevel3 +IsLevel4 + IsLevel5 = 1)

            and (   (IsLevel1=1 and Level1.ID is not NULL and Level2.ID is NULL)

                 or (IsLevel2=1 and Level2.ID is not null and Level3.ID is NULL)

                 or (IsLevel3=1 and Level3.ID is not null and Level4.ID is NULL)

                 or (IsLevel4=1 and Level4.ID is not null and Level5.ID is NULL)

                 or (IsLevel5=1 and Level5.ID is not null )

                )

  ) PartTree

WHERE SystemSecurityRightTitle IS NOT NULL

order by Row

code

• Entered : 11/29/2011
• Revised : 03/24/2015

• Backlinks include_pagepage_componentbacklinks