Row Oriented Security Using Triggers

Functionality

• If any row in the gateway table becomes visible to a security filter (because some underlying data has suddenly changed) then that row is immediately available to any role whose security includes that filter.
• Correspondingly, if any row in the gateway table becomes invisible to a security filter then that row is no longer available to any role whose security included that filter (unless some other security filter for that role still allows it).

• If any security filter is added, then each row in the gateway table defined by it is immediately available to any role whose security includes that filter.
• Correspondingly, if any security filter is deleted then each row of the gateway table defined by it is no longer available to any role whose security included that filter (unless some other security filter for that role still allows it).

Implementation

Security Model

How The Model Works (server side)

ALTER TRIGGER [dbo].[TR_Employee_Update_Insert] ON [dbo].[Employee] 
AFTER UPDATE, INSERT
AS

-- No counting
SET NOCOUNT ON

-- Declarations
DECLARE @MyId INT

-- If exactly one record updated/inserted
IF (SELECT COUNT(*) FROM INSERTED) = 1
	BEGIN
	-- Determine key of record
	SELECT @MyId = (SELECT EmployeeId FROM INSERTED)
	-- Synchronize security table for record updated/inserted
	EXECUTE sqRoleAccessSelectSync @MyId
	END

ALTER PROCEDURE [dbo].[sqRoleAccessSelectSync]
	(
	@Id INT
	)
 AS

/*
For each Role, View, and Filter in tblRoleAccessSelectFilter, 
check if @Id belongs to that filter's result set.

Insure that (Role, View, @Id) belongs to RoleAccessSelect if, and only if, 
@Id belongs to at least one of the result sets for that Role and View.

Note that each Role, View combination may be associated with multiple Filters.

So if @Id belongs to the result set of one filter, care must be taken to avoid 
deleting the corresponding row in RoleAccessSelect if @Id does not belong to the 
result set of another filter with the same Role and View.

Avoid this by ordering the Filters by Role and View and using a flag to check 
for an inserted @Id for all Filters of a given Role and View.

Reset that flag when a new Role and View combination appears. 
*/

-- Declarations
DECLARE @RoleIdCurrent	INT	-- current Role for all Filters being checked
DECLARE @ViewIdCurrent	INT	-- current View for all Filters being checked
DECLARE @ynInserted	INT	-- inserted flag for all Filters being checked with given Role, View

-- Declarations for cursor
DECLARE @RoleId		INT
DECLARE @ViewId		INT
DECLARE @FilterName	NVARCHAR(256)

-- Cursor for tblRoleAccessSelectFilter listing Roles, Views, and Filters (ordered by Role, View)
DECLARE curTable CURSOR
FOR 
SELECT RoleId, ViewId, FilterName FROM dbo.tblRoleAccessSelectFilter ORDER BY RoleId, ViewId
FOR READ ONLY

-- Set inserted flag to off
SET @ynInserted = 0

-- Open cursor
OPEN curTable 

-- Fetch first row
FETCH NEXT FROM curTable INTO @RoleId, @ViewId, @FilterName

-- Save current Role, View combination for which inserted check will be monitored
SET @RoleIdCurrent = @RoleId
SET @ViewIdCurrent = @ViewId

-- Do this while rows are fetched and inserted @Id does not exist for current Role, View
-- If inserted @Id exists, reset inserted flag to prevent possible deletion later on for current Role, View
-- Reset inserted flag to off if new Role, View combination appear
While @@FETCH_STATUS = 0 
	BEGIN
	-- Check if @ID is in filter and insert/delete corresponding row into tblRoleAccessSelect if it is/is not
	-- But only do this if inserted flag is off (otherwise there's nothing more to do for current Row, View)
	IF @ynInserted = 0
		EXECUTE sqRoleAccessSelectSync2
				@Id = @Id,
				@RoleId = @RoleId, 
				@ViewId = @ViewId, 
				@FilterName = @FilterName,
				@ynInserted = @ynInserted OUTPUT
	FETCH NEXT FROM curTable INTO @RoleId, @ViewId, @FilterName
	-- If Row, View combination changes, reset inserted flag to off and continue
	IF (@RoleId <> @RoleIdCurrent) OR (@ViewId <> @ViewIdCurrent)
		BEGIN
		SET @ynInserted = 0
		SET @RoleIdCurrent = @RoleId
		SET @ViewIdCurrent = @ViewId
		END
	END

-- Close cursor
CLOSE curTable

-- Deallocate cursor
DEALLOCATE curTable

ALTER PROCEDURE [dbo].[sqRoleAccessSelectSync2]
	(
	@Id		INT,
	@RoleId		INT,
	@ViewId		INT,
	@FilterName	NVARCHAR(256),
	@ynInserted	INT OUTPUT
	)
AS

/*
Insure that (RoleId, ViewId, @Id) belongs to RoleAccessSelect 
if @Id belongs to the result set of filter @FilterName (otherwise delete it).

Note that this call could delete (RoleId, ViewId, @Id) in tblRoleAccessSelect after
an earlier call inserted it, or discovered it was already inserted, for the same 
RoleId, ViewId.

So the calling script must insure that this doesn't happen by monitoring the value 
of @ynInserted set by this call.  
*/

BEGIN

SET NOCOUNT ON

-- Declarations
DECLARE @SQLString NVARCHAR(1028)

-- Set inserted flag off
SET @ynInserted = 0

-- Check if @Id belongs to the result set of filter @FilterName
-- Use temporary table to avoid result sets in caller
-- Note that SelectId is always a single column in @FilterName, by convention
SET @SQLString = N'SELECT TOP 1 * INTO #t FROM dbo.' + @FilterName + '  WHERE (SelectId = ' + CAST(@Id AS NVARCHAR(10))  + ')'
EXEC sp_executesql @SQLString

-- If it is
IF (@@ROWCOUNT = 1) 
	BEGIN
	-- Check if it is in tblRoleAccessSelect with given Role and View
	SET @SQLString = N'SELECT SelectId FROM tblRoleAccessSelect WHERE SelectId = ' + CAST(@Id AS NVARCHAR(10)) + ' AND RoleId = ' + CAST(@RoleId AS NVARCHAR(10)) + ' AND ViewId = ' + CAST(@ViewId AS NVARCHAR(10))
	EXEC sp_executesql @SQLString
	-- If it isn't
	IF (@@ROWCOUNT = 0) 
		BEGIN
		-- Insert it 
		SET @SQLString = N'INSERT INTO tblRoleAccessSelect VALUES (' +  CAST(@RoleId AS NVARCHAR(10)) + ',' + CAST(@ViewId AS NVARCHAR(10)) + ',' + CAST(@Id AS NVARCHAR(10)) + ')'
		EXEC sp_executesql @SQLString
		-- Set inserted flag on (because it has just been inserted)
		SET @ynInserted = 1
		END
	-- If it is
	ELSE
		-- Set inserted flag on (because it was already inserted)
		SET @ynInserted = 1
	END
ELSE
	-- If it isn't, then it can't be for this (or any other) Role, View combination
	BEGIN
	-- So delete it for current Role, View combination
	SET @SQLString = N'DELETE FROM tblRoleAccessSelect WHERE RoleId = ' +  CAST(@RoleId AS NVARCHAR(10)) + ' AND ViewId = ' + CAST(@ViewId AS NVARCHAR(10)) + ' AND SelectId = ' + CAST(@Id AS NVARCHAR(10)) 
	EXEC sp_executesql @SQLString
	END
END

ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Insert] ON [dbo].[tblRoleAccessSelectFilter] 
FOR INSERT
AS
BEGIN

-- Declarations
DECLARE @SQLString		NVARCHAR(1028)
DECLARE @RoleId			INT
DECLARE @ViewId			INT
DECLARE @FilterName		NVARCHAR(256)	

-- Do nothing unless INSERTED contains exactly one row
IF @@ROWCOUNT <> 1 
	RETURN

-- Avoid extra result sets
SET NOCOUNT ON

-- Get RoleId, ViewId, Filter from record that's been INSERTED
SELECT @RoleId		= (SELECT RoleId FROM INSERTED)
SELECT @ViewId		= (SELECT ViewId FROM INSERTED)
SELECT @FilterName	= (SELECT FilterName FROM INSERTED)

-- Insert into tblRoleAccessSelect
-- Note that @FilterName must name an existing filter (otherwise insertion will fail)
SET @SQLString =
N'INSERT INTO tblRoleAccessSelect(RoleId,ViewId,SelectId) SELECT D1.RoleId,D1.ViewId,D1.SelectId FROM
(SELECT ' + CAST(@RoleId as NVARCHAR(10)) + ' AS RoleId,' + CAST(@ViewId as NVARCHAR(10)) + ' AS ViewId,' + @FilterName + '.SelectId FROM ' + @FilterName + ') D1 LEFT OUTER JOIN tblRoleAccessSelect ON ' + 'D1.RoleId = tblRoleAccessSelect.RoleId AND ' + 'D1.ViewId = tblRoleAccessSelect.ViewId AND D1.SelectId = tblRoleAccessSelect.SelectId WHERE tblRoleAccessSelect.SelectId IS NULL' 

EXEC sp_executesql @SQLString

END

-- Finalization
SET NOCOUNT OFF

ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Delete] ON [dbo].[tblRoleAccessSelectFilter] 
FOR DELETE
AS
BEGIN

-- Declarations
DECLARE @SQLString		NVARCHAR(2048)	-- outer SQL string
DECLARE @SQLString1		NVARCHAR(2048)	-- inner SQL string consisting of UNIONs (variable number)
DECLARE @RoleId			INT
DECLARE @ViewId			INT
DECLARE @FilterName		NVARCHAR(256)	
DECLARE @RoleIdSave		INT
DECLARE @ViewIdSave		INT
DECLARE @FilterNameSave		NVARCHAR(256)

-- Do nothing unless DELETED contains exactly one row
IF (SELECT COUNT(*) FROM DELETED) <> 1 
	RETURN

-- Avoid extra result sets
SET NOCOUNT ON

-- Get @RoleId, @ViewId, @Filter from DELETED record
SELECT @RoleId		= (SELECT RoleId FROM DELETED)
SELECT @ViewId		= (SELECT ViewId FROM DELETED)
SELECT @FilterName	= (SELECT FilterName FROM DELETED)

-- Save Role, View and Filter for use in building SQL outer string
SET @RoleIdSave		= @RoleId
SET @ViewIdSave		= @ViewId
SET @FilterNameSave	= @FilterName

-- Start building inner SQL string
SET @SQLString1 = 'SELECT SelectId FROM ' + @FilterName 

-- Cursor for tblRoleAccessSelectFilter listing Filters 
-- with the same Roles, Views as DELETED record
DECLARE curTable CURSOR
FOR 
SELECT FilterName FROM dbo.tblRoleAccessSelectFilter 
WHERE
RoleId = @RoleId AND
ViewId = @ViewId 
FOR READ ONLY

-- Open cursor
OPEN curTable 

-- Fetch next row (note that deleted row does not belong to cursor)
FETCH NEXT FROM curTable INTO @FilterName

-- Continue building SQL inner string
While @@FETCH_STATUS = 0 
	BEGIN
	SET @SQLString1 = @SQLString1 + ' UNION ALL SELECT SelectId FROM ' + @FilterName 
	FETCH NEXT FROM curTable INTO @FilterName
	END

-- Delete from tblRoleAccessSelect (explanation below)
SET @SQLString = 
N'DELETE FROM tblRoleAccessSelect WHERE 
RoleId = ' + CAST(@RoleIdSave AS NVARCHAR(10)) + ' AND
ViewId = ' + CAST(@ViewIdSave AS NVARCHAR(10)) + ' AND
SelectId IN
(
SELECT tblRoleAccessSelect.SelectId FROM tblRoleAccessSelect
INNER JOIN ' +
@FilterNameSave +
' ON
tblRoleAccessSelect.SelectId = ' + @FilterNameSave + '.SelectId
INNER JOIN
(
SELECT SelectId FROM	 
(' +
@SQLString1 +			
') d1
GROUP BY SelectId
HAVING COUNT(*) = 1
) d2
ON
tblRoleAccessSelect.SelectId = d2.SelectId
)'

/*
Sample deletion for filter ftr1 but where ftr2 has same Role, View.
Avoid deleting any SelectId belonging to ftr1 if it also belongs to ftr2.

DELETE 
FROM tblRoleAccessSelect WHERE
RoleId = 1 AND
ViewId = 1 AND
SelectId IN
(
-- Find the SelectId values in tblRoleAccessSelect belonging to ftr1
SELECT tblRoleAccessSelect.SelectId FROM tblRoleAccessSelect
INNER JOIN
ftr1
ON
tblRoleAccessSelect.SelectId = ftr1.SelectId
-- But make sure they don't also belong to ftr2 
INNER JOIN
(
-- UNION of SelectId for two filters without duplication (but where COUNT = 1).
-- This guarantees that each SelectId belongs to exactly one filter. 
SELECT SelectId FROM
(
-- UNION of SelectId for two filters (with duplication)
-- This is the inner SQL string
SELECT SelectId FROM ftr1
UNION ALL
SELECT SelectId FROM ftr2
) d1
GROUP BY SelectId
HAVING COUNT(*) = 1
) d2
ON
tblRoleAccessSelect.SelectId = d2.SelectId
)
*/

EXEC sp_executesql @SQLString

-- Close cursor
CLOSE curTable

-- Deallocate cursor
DEALLOCATE curTable

-- Finalization
SET NOCOUNT OFF

END

How The Model Works (client side)

Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read() ' Loop through constraints for current test
    MySecurity(0) = reader.Item(0).ToString ' ColumnName
    MySecurity(1) = reader.Item(1).ToString ' AccessColumnName
    For Each ctrl As Control In Me.Controls ' Loop through all controls
        ' If this is one of the following controls
        If (TypeOf ctrl Is TextBox) Or _
        (TypeOf ctrl Is Label) Or _
        (TypeOf ctrl Is Button) Or _
        (TypeOf ctrl Is ListBox) Or _
        (TypeOf ctrl Is ListView) Then
            If MySecurity(0) = ctrl.Name Then   ' Referenced by current constraint
                ctrl.Enabled = True             ' Enable it and make it visible
                ctrl.Visible = True
                If MySecurity(1) = "Lock" Then  ' Lock it if required
                    ctrl.Enabled = False
                End If
                If MySecurity(1) = "Hide" Then  ' Hide it if required
                    ctrl.Visible = False
                End If
                If MySecurity(1) = "Full" Then  ' Full access if required
                    ctrl.Enabled = True
                    ctrl.Visible = True
                End If
            End If
        End If
    Next
End While
reader.Close()

SELECT DISTINCT 
lkpRole.RoleName, 
lkpView.ViewName, 
Employee.LastName, 
tblRoleAccessSelect.SelectId FROM 
Employee INNER JOIN 
tblRoleAccessSelect ON 
Employee.EmployeeId = tblRoleAccessSelect.SelectId INNER JOIN 
lkpRole ON 
tblRoleAccessSelect.RoleId = lkpRole.RoleId INNER JOIN 
tblUserRole ON 
lkpRole.RoleId = tblUserRole.RoleId INNER JOIN 
lkpView ON 
tblRoleAccessSelect.ViewId = lkpView.ViewId INNER JOIN 
lkpUser ON 
tblUserRole.UserId = lkpUser.UserId 
WHERE 
(lkpUser.UserName = 'User2') AND 
(1=1) AND 
(lkpView.ViewName = 'View1') 
ORDER BY 
lkpRole.RoleName, 
lkpView.ViewName, 
Employee.LastName

SELECT 
EmployeeId, 
LastName, 
FirstName, 
Address, 
City, 
Postal, 
Prov,
Phone, 
Email, 
BirthDate, 
[SIN]
FROM 
Employee 
WHERE EmployeeId IN
(
SELECT DISTINCT 
tblRoleAccessSelect.SelectId
FROM         
tblRoleAccessSelect INNER JOIN
lkpView ON 
tblRoleAccessSelect.ViewId = lkpView.ViewId INNER JOIN
tblUserRole ON 
tblRoleAccessSelect.RoleId = tblUserRole.RoleId
WHERE 
UserId = 2 AND 
ViewName = 'View1'
) 
ORDER BY 
LastName, 
FirstName

Summary