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 list of station roles (what role the user has at each station he/she can access)
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- Ken Baker 04/19/10 Created
-- =============================================
CREATE PROCEDURE seclyr.[uspGetUserStationRoles]
-- Add the parameters for the stored procedure here
@userId int,
@moduleIds varchar(max)
AS
DECLARE @ACCESS_LEVEL_NATIONAL tinyint,
@ACCESS_LEVEL_REGION tinyint,
@ACCESS_LEVEL_CCNREGION tinyint,
@ACCESS_LEVEL_VISN tinyint,
@ACCESS_LEVEL_STATION tinyint,
@ACCESS_STATUS_APPROVED varchar(10),
@ROLE_USER tinyint,
@SENSITIVITYLEVEL_NONE tinyint,
@CAN_EDIT bit,
@isNationalUser bit,
@minModuleID tinyint
DECLARE @userRoleTable TABLE
(
RowID int,
ModuleID smallint,
RegionID tinyint, --this is the VHA Region
CCNRegionID tinyint, --this is the CCN Region
VisnID tinyint,
StationID smallint,
StationName varchar(250),
RoleID tinyint,
AccessLevelID tinyint,
AccessStatus varchar(50),
SensitivityLevelID tinyint,
CanEdit bit
)
DECLARE @modulesTable table
(
ModuleID tinyint
)
DECLARE @rolesTable TABLE
(
RoleID tinyint,
SensitivityLevelID tinyint,
CanEdit bit
)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--Set necessary variables
SET @ACCESS_STATUS_APPROVED = 'APPROVED';
SET @SENSITIVITYLEVEL_NONE = 1;
SET @CAN_EDIT = 0;
SELECT @ROLE_USER = RoleID FROM seclyr.Roles WHERE RoleName = 'User';
SELECT @ACCESS_LEVEL_NATIONAL = seclyr.ufnGetAccessLevelConstant('National');
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
--Clear out the @rolesTable
DELETE FROM @rolesTable;
SELECT @isNationalUser = seclyr.ufnIsNationalUser(@userId, @minModuleID);
--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
IF EXISTS(SELECT RoleID FROM seclyr.UserRoles WHERE UserID = @userId AND ModuleID = @minModuleID AND AccessLevelID = @ACCESS_LEVEL_NATIONAL
AND AccessStatus = @ACCESS_STATUS_APPROVED)
BEGIN
INSERT INTO @rolesTable (RoleID, SensitivityLevelID, CanEdit)
SELECT DISTINCT RoleID, SensitivityLevelID, CanEdit
FROM seclyr.UserRoles
WHERE UserID = @userId
AND ModuleID = @minModuleID
AND AccessLevelID = @ACCESS_LEVEL_NATIONAL
AND AccessStatus = @ACCESS_STATUS_APPROVED;
END
--If a role isn't found, then substitute read only and non-sensitive data
IF (SELECT COUNT(1) FROM @rolesTable) = 0
BEGIN
INSERT INTO @rolesTable (RoleID, SensitivityLevelID, CanEdit)
VALUES (@ROLE_USER, @SENSITIVITYLEVEL_NONE, @CAN_EDIT);
END
--Insert the possible station combinations into the table variable
--not sure if ModuleId needs to be called here via join-josh--
INSERT INTO @userRoleTable (RowID, ModuleID, RegionID, CCNRegionID, VisnID, StationID, StationName,
RoleID, AccessLevelID, AccessStatus, SensitivityLevelID, CanEdit)
SELECT DISTINCT CAST(ROW_NUMBER() OVER(ORDER BY t.StationID) as int),
@minModuleID, t.RegionID, t.CCNRegionID, t.VisnID, t.StationID,
'V' + VisnCode + ': ' + t.Station3N + ': ' + t.StationName,
RoleID, @ACCESS_LEVEL_NATIONAL, @ACCESS_STATUS_APPROVED, SensitivityLevelID, CanEdit
FROM Stations t
INNER JOIN Visns v ON t.VisnID = v.VisnID
CROSS APPLY (
SELECT RoleID, rt.SensitivityLevelID, CanEdit
FROM @rolesTable rt
) as rx
END
--In cases where a user might be a national administrator with sensitivity level of 1 (no PII/PHI/sensitive data),
--but have a higher sensitivity level (PII, PHI, sensitive) for
--a lesser user role, it is necessary to join the national admin and the other possible roles together
--note this only happens for national level people.
--ELSE
BEGIN
INSERT INTO @userRoleTable (ModuleID, RegionID, CCNRegionID, VisnID, StationID, StationName, RoleID,
AccessLevelID, AccessStatus, SensitivityLevelID, CanEdit)
--check Region permissions
SELECT DISTINCT ModuleID, t.RegionID, t.CCNRegionID, t.VisnID, t.StationID,
CAST(t.VisnID as varchar) + ': ' + t.Station3N + ': ' + t.StationName, RoleID,
AccessLevelID, AccessStatus, SensitivityLevelID, CanEdit
FROM Stations t
INNER JOIN seclyr.UserRoles ON t.RegionID = seclyr.UserRoles.RegionID
WHERE UserID = @userId
AND ModuleID = @minModuleID
AND AccessLevelID = @ACCESS_LEVEL_REGION
AND AccessStatus = @ACCESS_STATUS_APPROVED
--check CCNRegion permissions
UNION
SELECT DISTINCT ModuleID, t.RegionID, t.CCNRegionID, t.VisnID, t.StationID,
CAST(t.VisnID as varchar) + ': ' + t.Station3N + ': ' + t.StationName, RoleID,
AccessLevelID, AccessStatus, SensitivityLevelID, CanEdit
FROM Stations t
INNER JOIN seclyr.UserRoles ON t.CCNRegionID = seclyr.UserRoles.CCNRegionID
WHERE UserID = @userId
AND ModuleID = @minModuleID
AND AccessLevelID = @ACCESS_LEVEL_CCNREGION
AND AccessStatus = @ACCESS_STATUS_APPROVED
--check Visn permissions
UNION
SELECT DISTINCT ModuleID, t.RegionID, t.CCNRegionID, t.VisnID, t.StationID,
CAST(t.VisnID as varchar) + ': ' + t.Station3N + ': ' + t.StationName, RoleID,
AccessLevelID, AccessStatus, SensitivityLevelID, CanEdit
FROM Stations t
INNER JOIN seclyr.UserRoles ON t.VisnID = seclyr.UserRoles.VisnID
WHERE UserID = @userId
AND ModuleID = @minModuleID
AND AccessLevelID = @ACCESS_LEVEL_VISN
AND AccessStatus = @ACCESS_STATUS_APPROVED
--check Station permissions
UNION
SELECT DISTINCT ModuleID, t.RegionID, t.CCNRegionID, t.VisnID, t.StationID,
CAST(t.VisnID as varchar) + ': ' + t.Station3N + ': ' + t.StationName, RoleID,
AccessLevelID, AccessStatus, SensitivityLevelID, CanEdit
FROM Stations t
INNER JOIN seclyr.UserRoles ON t.StationID = seclyr.UserRoles.StationID
WHERE UserID = @userId
AND ModuleID = @minModuleID
AND AccessLevelID = @ACCESS_LEVEL_STATION
AND AccessStatus = @ACCESS_STATUS_APPROVED
;
END
--Iterate at the end of the loop to the next RowID
SELECT @minModuleID = MIN(ModuleID)
FROM @modulesTable
WHERE ModuleID > @minModuleID ;
END
--Need to spoof unnecessary columns in order to hook into the data domain for UserRoles
SELECT CAST(ROW_NUMBER() OVER(ORDER BY StationID) AS int) AS 'RowID',
@userId as 'UserID'
, ModuleID as 'ModuleID'
, RegionID AS 'RegionID'
, CCNRegionID AS 'CCNRegionID'
, VisnID AS 'VisnID'
, StationID AS 'StationID'
, RoleID AS 'RoleID'
, AccessStatus AS 'AccessStatus'
, AccessLevelID AS 'AccessLevelID'
, SensitivityLevelID AS 'SensitivityLevelID'
, CanEdit AS 'CanEdit'
FROM @userRoleTable;
END TRY
BEGIN CATCH
PRINT 'There was an error processing the user station roles' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END
GO