Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?-- =============================================
-- Description: <Get menu items allowed for user based on user role and sensitivity level>
-- Author: <Steve Kopecky>
-- Create date: <11/1/2017>
-- Updated 1/16/2019
-- =============================================
CREATE PROCEDURE [seclyr].[uspGetUserMenuItems]
-- Add the parameters for the stored procedure here
@userId int,
@moduleIds varchar(max),
@menuType tinyint,
@isParent bit,
@sensitivityLevelId tinyint,
@parentMenuId smallint
AS
DECLARE @ACCESS_STATUS_APPROVED varchar(10)
DECLARE @SENSITIVITYLEVEL_NONE tinyint
DECLARE @SENSITIVITYLEVEL_PII tinyint
DECLARE @SENSITIVITYLEVEL_PHI tinyint
DECLARE @SENSITIVITYLEVEL_PIIPHI tinyint
DECLARE @SENSITIVITYLEVEL_SENSITIVE tinyint
DECLARE @minRoleID tinyint
DECLARE @minModuleID tinyint
-- Build a table of the roles assigned to the user for the modules the user is allowed to access
-- and that have an APPROVED status for the user.
DECLARE @rolesTable table
(
RoleID tinyint
)
-- Build table of modules for application
DECLARE @modulesTable table
(
ModuleID tinyint
)
DECLARE @resultsTable table
(
MenuItemID smallint,
ParentMenuItemID smallint,
SensitivityLevelID tinyint,
MenuItemName varchar(100),
DisplayName varchar(200),
Synopsis varchar(2000),
ControllerName varchar(200),
ActionName varchar(200),
UrlName varchar(200),
SortOrder smallint,
AllowedRoles varchar(25),
isParent bit,
AssociatedModules varchar(50)
)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
SET @ACCESS_STATUS_APPROVED = 'APPROVED';
SET @SENSITIVITYLEVEL_NONE = 1;
SET @SENSITIVITYLEVEL_PII = 2;
SET @SENSITIVITYLEVEL_PHI = 3;
SET @SENSITIVITYLEVEL_PIIPHI = 4;
SET @SENSITIVITYLEVEL_SENSITIVE = 5;
-- Build table of modules which will be used to restrict report items
if @moduleIds IS NULL
BEGIN
INSERT INTO @modulesTable (ModuleID)
SELECT NULL FROM seclyr.Modules;
END
ELSE
BEGIN
INSERT INTO @modulesTable (ModuleID)
SELECT [Value] FROM ufnSplit(',', @moduleIds);
END
SELECT @minModuleID = MIN(ModuleID)
FROM @modulesTable
WHILE @minModuleID IS NOT NULL
BEGIN
DELETE FROM @RolesTable;
IF @userId > 0
BEGIN
-- Build table of allowed user roles for user with ModuleID and Approved user roles
IF EXISTS(SELECT UserRoleID FROM seclyr.UserRoles
WHERE UserID = @userId
--AND ModuleID = @minModuleID
AND AccessStatus = @ACCESS_STATUS_APPROVED)
BEGIN
INSERT INTO @RolesTable (RoleID)
SELECT DISTINCT RoleID
FROM seclyr.UserRoles
WHERE UserID = @userId
--AND ModuleID = @minModuleID
AND AccessStatus = @ACCESS_STATUS_APPROVED;
END
END
SELECT @minRoleID = MIN(RoleID)
FROM @rolesTable
-- build results table of report items for user based on Report type and are not inactive
-- and where the the user has an approved user role that matches one of the allowed roles for the report item
-- Finally, check the sensitvity level of the item matches the sensitivity level parameter.
WHILE @minRoleID IS NOT NULL
BEGIN
INSERT INTO @resultsTable(MenuItemID, ParentMenuItemID, SensitivityLevelID, MenuItemName, DisplayName, Synopsis, ControllerName,
ActionName, UrlName,SortOrder, AllowedRoles, isParent, AssociatedModules)
SELECT MenuItemID, ParentMenuItemID, SensitivityLevelID, MenuItemName, DisplayName, Synopsis, ControllerName,
ActionName, UrlName, SortOrder, AllowedRoles, IsParent, AssociatedModules
FROM MenuItems
WHERE MenuItemTypeID = @menuType
AND Inactive = '0'
AND (
( @isParent = '1' AND isParent = @isParent )
OR
( @isParent = '0' AND ( @parentMenuId = parentMenuItemID AND isParent = '0') )
)
--AND ( ParentMenuItemID = @parentMenuId OR @parentMenuId IS NULL)
AND (
( AllowedRoles = CAST(@minRoleID as varchar(10)) -- When there is only 1 roleId in the list
OR AllowedRoles LIKE CAST(@minRoleID as varchar(10)) + ',%' -- When the roleId is the first one
OR AllowedRoles LIKE '%,' + CAST(@minRoleID as varchar(10)) + ',%' -- When the roleId is in the middle
OR AllowedRoles LIKE '%,' + CAST(@minRoleID as varchar(10)) -- When the roleId is at the end
)
OR AllowedRoles IS NULL
)
AND (
( @sensitivityLevelId = @SENSITIVITYLEVEL_SENSITIVE AND ( SensitivityLevelID <= @SENSITIVITYLEVEL_SENSITIVE OR SensitivityLevelID IS NULL ) )
OR ( @sensitivityLevelId = @SENSITIVITYLEVEL_PIIPHI AND ( SensitivityLevelID <= @SENSITIVITYLEVEL_PIIPHI OR SensitivityLevelID IS NULL) )
OR ( @sensitivityLevelId = @SENSITIVITYLEVEL_PHI AND ( SensitivityLevelID = @SENSITIVITYLEVEL_PHI OR ( SensitivityLevelID < @SENSITIVITYLEVEL_PII OR SensitivityLevelID IS NULL) )
OR ( @sensitivityLevelId = @SENSITIVITYLEVEL_PII AND ( SensitivityLevelID <= @SENSITIVITYLEVEL_PII OR SensitivityLevelID IS NULL) )
OR ( ( @sensitivityLevelId = @SENSITIVITYLEVEL_NONE OR @sensitivityLevelId IS NULL) AND ( SensitivityLevelID = @SENSITIVITYLEVEL_NONE OR SensitivityLevelID IS NULL) )
)
)
AND (
( AssociatedModules = CAST(@minModuleID as varchar(10)) -- When there is only 1 Associated Module in the list
OR AssociatedModules LIKE CAST(@minModuleID as varchar(10)) + ',%' -- When the Associated Module is the first one
OR AssociatedModules LIKE '%,' + CAST(@minModuleID as varchar(10)) + ',%' -- When the Associated Module is in the middle
OR AssociatedModules LIKE '%,' + CAST(@minModuleID as varchar(10)) -- When the Associated Module is at the end
)
OR AssociatedModules IS NULL
)
-- Get next role
SELECT @minRoleID = MIN(RoleID)
FROM @rolesTable
WHERE RoleID > @minRoleID
END
SELECT @minModuleID = MIN(ModuleID)
FROM @modulesTable
WHERE ModuleID > @minModuleID
END
SELECT DISTINCT MenuItemID, ParentMenuItemID, SensitivityLevelID, MenuItemName, DisplayName, Synopsis, ControllerName,
ActionName, UrlName,SortOrder, AllowedRoles, AssociatedModules, isParent
FROM @resultsTable
ORDER BY SortOrder;
END TRY
BEGIN CATCH
PRINT 'There was an error processing the allowed menu items' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END
GO