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 Visns
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE [seclyr].[uspGetUserVisns]
-- Add the parameters for the stored procedure here
@userId int,
@moduleIds varchar(max)
AS
DECLARE @ACCESS_LEVEL_REGION tinyint,
@ACCESS_LEVEL_CCNREGION tinyint,
@ACCESS_LEVEL_VISN tinyint,
@ACCESS_LEVEL_STATION tinyint,
@ACCESS_STATUS_APPROVED varchar(10),
@isNationalUser bit,
@minModuleID tinyint
DECLARE @resultsTable table
(
ModuleID tinyint,
RegionID tinyint,
CCNRegionID tinyint,
VisnID tinyint,
VisnCode varchar(10),
VisnDisplayName varchar(50),
VisnName varchar(100),
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';
SELECT @ACCESS_LEVEL_REGION = seclyr.ufnGetAccessLevelConstant('VHA Region');
SELECT @ACCESS_LEVEL_CCNREGION = seclyr.ufnGetAccessLevelConstant('CCN 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 @isNationalUser = 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 @isNationalUser = 1
BEGIN
WITH LookupTable (RegionID, CCNRegionID, VisnID, VisnCode, VisnDisplayName, VisnName, Inactive) AS
(
SELECT DISTINCT Stations.RegionID, Stations.CCNRegionID, t.VisnID, t.VisnCode, 'VISN ' + t.VisnCode, t.VisnName, t.Inactive
FROM Visns t
INNER JOIN Stations ON Stations.VisnID = t.VisnID
)
INSERT INTO @resultsTable (ModuleID, RegionID, CCNRegionID, VisnID, VisnCode, VisnDisplayName, VisnName, Inactive)
SELECT @minModuleID, RegionID, CCNRegionID, VisnID, VisnCode, VisnDisplayName, VisnName, Inactive
FROM LookupTable
--ORDER BY VisnID, VisnName
END
ELSE
BEGIN
WITH LookupTable (RegionID, CCNRegionID, VisnID, VisnCode, VisnDisplayName, VisnName, Inactive) AS
(
--check Region permissions
SELECT DISTINCT Stations.RegionID, Stations.CCNRegionID, t.VisnID, t.VisnCode, 'VISN ' + t.VisnCode, t.VisnName, t.Inactive
FROM Visns t
INNER JOIN Stations ON Stations.VisnID = t.VisnID
INNER JOIN seclyr.UserRoles ON Stations.RegionID = seclyr.UserRoles.RegionID
WHERE UserID = @userId
AND ModuleID = @moduleIds
AND AccessLevelID = @ACCESS_LEVEL_REGION
AND AccessStatus = @ACCESS_STATUS_APPROVED
--check Region permissions
UNION SELECT DISTINCT Stations.RegionID, Stations.CCNRegionID, t.VisnID, t.VisnCode, 'VISN ' + t.VisnCode, t.VisnName, t.Inactive
FROM Visns t
INNER JOIN Stations ON Stations.VisnID = t.VisnID
INNER JOIN seclyr.UserRoles ON Stations.CCNRegionID = seclyr.UserRoles.CCNRegionID
WHERE UserID = @userId
AND ModuleID = @moduleIds
AND AccessLevelID = @ACCESS_LEVEL_CCNREGION
AND AccessStatus = @ACCESS_STATUS_APPROVED
--check Visn permissions
UNION SELECT DISTINCT Stations.RegionID, Stations.CCNRegionID, t.VisnID, t.VisnCode, 'VISN ' + t.VisnCode, t.VisnName, t.Inactive
FROM Visns t
INNER JOIN seclyr.UserRoles ON t.VisnID = seclyr.UserRoles.VisnID
INNER JOIN Stations ON Stations.VisnID = t.VisnID
WHERE UserID = @userId
AND ModuleID = @moduleIds
AND AccessLevelID = @ACCESS_LEVEL_VISN
AND AccessStatus = @ACCESS_STATUS_APPROVED
--check Station permissions
UNION SELECT DISTINCT Stations.RegionID, Stations.CCNRegionID, t.VisnID, t.VisnCode, 'VISN ' + t.VisnCode, t.VisnName, t.Inactive
FROM Visns t
INNER JOIN Stations ON Stations.VisnID = t.VisnID
INNER JOIN seclyr.UserRoles ON Stations.StationID = seclyr.UserRoles.StationID
WHERE UserID = @userId
AND ModuleID = @moduleIds
AND AccessLevelID = @ACCESS_LEVEL_STATION
AND AccessStatus = @ACCESS_STATUS_APPROVED
)
--same insertion to appresults
INSERT INTO @resultsTable (ModuleID, RegionID, CCNRegionID, VisnID, VisnCode, VisnDisplayName, VisnName, Inactive)
SELECT @minModuleID, RegionID, CCNRegionID, VisnID, VisnCode, VisnDisplayName, VisnName, Inactive
FROM LookupTable
--ORDER BY VisnID, VisnName
;
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 VisnID) AS int) AS 'RowID'
, ModuleID
, RegionID
, CCNRegionID
, VisnID
, VisnCode
, VisnDisplayName
, VisnName
, Inactive
FROM @resultsTable
ORDER BY ModuleID, VisnID, VisnName;
END TRY
BEGIN CATCH
PRINT 'There was an error processing the user VISNs' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END