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: Returns user's permitted list of VHA Regions
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE [seclyr].[uspGetUserVHARegions]
-- Add the parameters for the stored procedure here
@userId int,
@moduleIds varchar(max)
AS
DECLARE @ACCESS_LEVEL_REGION tinyint,
@ACCESS_LEVEL_VISN tinyint,
@ACCESS_LEVEL_STATION tinyint,
@ACCESS_STATUS_APPROVED varchar(10),
@MAX_VHAREGION tinyint,
@isNational bit,
@minModuleID tinyint

DECLARE @resultsTable table
(
ModuleID tinyint,
RegionID tinyint,
RegionCode varchar(10),
RegionName varchar(50),
Inactive bit
)

DECLARE @modulesTable table
(
ModuleID tinyint
)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY
BEGIN
--Set necessary variables
SET @ACCESS_STATUS_APPROVED = 'APPROVED';
SET @MAX_VHAREGION = 4; --VHA regions are 1 - 4; CCN Regions are 1 - 6
SELECT @ACCESS_LEVEL_REGION = seclyr.ufnGetAccessLevelConstant('VHA Region');
SELECT @ACCESS_LEVEL_VISN = seclyr.ufnGetAccessLevelConstant('Visn');
SELECT @ACCESS_LEVEL_STATION = seclyr.ufnGetAccessLevelConstant('Station');
--will need to start the wrapper here for all permutations of appID--
--build a table of various appId's--
IF @moduleIds IS NULL
BEGIN
INSERT INTO @modulesTable (ModuleID)
SELECT ModuleID FROM seclyr.Modules;
END
ELSE
BEGIN
INSERT INTO @modulesTable (ModuleID)
SELECT [Value] FROM ufnSplit(',', @moduleIds);
END

--Set the min rowId to start the while loop
SELECT @minModuleID = MIN(ModuleID)
FROM @modulesTable;

WHILE @minModuleID IS NOT NULL
BEGIN
SELECT @isNational = seclyr.ufnIsNationalUser(@userId, @moduleIds);

--If is a national user or no user id is returned, then return all
IF @userId Is Null OR @userId = 0 OR @isNational = 1
BEGIN
WITH LookupTable (RegionID, RegionCode, RegionName, Inactive) AS
(
SELECT DISTINCT RegionID, RegionCode, RegionName, CAST(0 AS bit) AS Inactive
FROM Regions
WHERE RegionID <= @MAX_VHAREGION
)
INSERT INTO @resultsTable (ModuleID, RegionID, RegionCode, RegionName, Inactive)
SELECT @minModuleID, RegionID, RegionCode, RegionName, Inactive
FROM LookupTable;
END
ELSE
BEGIN
WITH LookupTable (RegionID, RegionCode, RegionName, Inactive) AS
(
--check VHA Region permissions
SELECT DISTINCT Regions.RegionID, RegionCode, RegionName, CAST(0 AS bit) AS Inactive
FROM Regions
INNER JOIN seclyr.UserRoles ON Regions.RegionID = seclyr.UserRoles.RegionID
WHERE UserID = @userId
AND ModuleID = @moduleIds
AND AccessLevelID = @ACCESS_LEVEL_REGION
AND AccessStatus = @ACCESS_STATUS_APPROVED

--check Visn permissions
UNION SELECT DISTINCT Regions.RegionID, RegionCode, RegionName, CAST(0 AS bit) AS Inactive
FROM Regions
INNER JOIN Stations ON Stations.RegionID = Regions.RegionID
INNER JOIN Visns ON Visns.VisnID = Stations.VisnID
WHERE EXISTS (SELECT 1 FROM seclyr.UserRoles usr
WHERE usr.VisnID = Visns.VisnID
AND UserID = @userId
AND ModuleID = @moduleIds
AND AccessLevelID = @ACCESS_LEVEL_VISN
AND AccessStatus = @ACCESS_STATUS_APPROVED)

--check Station permissions
UNION SELECT DISTINCT Regions.RegionID, RegionCode, RegionName, CAST(0 AS bit) AS Inactive
FROM Regions
INNER JOIN Stations ON Stations.RegionID = Regions.RegionID
WHERE EXISTS (SELECT 1 FROM seclyr.UserRoles usr
WHERE usr.StationID = Stations.StationID
AND UserID = @userId
AND ModuleID = @moduleIds
AND AccessLevelID = @ACCESS_LEVEL_STATION
AND AccessStatus = @ACCESS_STATUS_APPROVED)
)

--same insertion to appresults
INSERT INTO @resultsTable (ModuleID, RegionID, RegionCode, RegionName, Inactive)
SELECT @minModuleID, RegionID, RegionCode, RegionName, Inactive
FROM LookupTable;
END
--Iterate at the end of the loop to the next RowID
SELECT @minModuleID = MIN(ModuleID)
FROM @modulesTable
WHERE ModuleID > @minModuleID;
END
END

--Return results
SELECT DISTINCT CAST(ROW_NUMBER() OVER(ORDER BY RegionID) AS int) AS 'RowID'
, ModuleID
, RegionID
, RegionCode
, RegionName
, Inactive
FROM @resultsTable
ORDER BY ModuleID, RegionID, RegionName;
END TRY

BEGIN CATCH
PRINT 'There was an error processing the user CCN regions' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END
GO