Overview

This article introduces a simple model for row-oriented security. It is based on a small number of security tables and a few triggers. The model is independent of SQL Server's own security, and may be used in other database products with minimal change.

The basic premise of this model is that some user table acts as a gateway to all other tables. Row-oriented security uses a single table to list which rows of the gateway table may be accessed by a given user. Such users may access related rows in other tables if access has been granted to the corresponding gateway rows.

It also assumes that users will access the database through approved applications only. That's because the model puts a tiny part of the security burden on the front-end applications instead of the database server (where most of the security processing occurs). In particular, those apps can implement additional row-oriented and column-oriented security without additional programming.

This model has been successfully used by a large Medical faculty for several years.

Example

To make things really simple, suppose our database contains exactly one table Employee for which, say, an HR application has been written:

In that application, we would like each user to be restricted to various rows depending on the roles that user has been assigned. We would like those restrictions to be defined in a set of security tables that the application reads at run-time, and which may be modified in real-time by an administrator.

The rows in Employee that each role may access will be defined by filters on that table, such as:

SELECT EmployeeId AS SelectId FROM Employee WHERE City = 'Vancouver'

Such filters simply list key values of the gateway table with a fixed name SelectId. Each role may have any number of filters assigned to it and each user may have multiple roles. For convenience, the gateway table's key and SelectId are INT columns (although that can be easily changed).

We also want the rows that any role (and user) can see to accurately reflect the current state of the data (an employee may move at any time, for example) or the filters currently assigned to it (they may be inserted or deleted at any time).

Furthermore, the app itself should automatically impose additional row- and column-oriented constraints, such as preventing record deletions or hiding certain fields, depending on the restrictions entered into the security tables by the administrator.

The model presented here does that, using special security tables that designate what roles users belong to, what rows each role may access in the gateway table, what kind of editing may be done on those rows, and what columns will be hidden by the apps.

Functionality

Users, roles and restrictions are defined by an organization using special security tables added to the database.

A row-oriented restriction is nothing more than a filter defining what rows of the gateway table may be accessed by a given role. Such filters may also be defined using additional tables, such as lookup tables.

Users may be assigned any number of roles. They automatically inherit the "sum" of all security restrictions on those roles.

The filtering mechanism for invoking security works dynamically, using triggers that fire whenever the gateway table is modified:

• 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).

Furthermore, any changes to the set of security filters is immediately witnessed by all users, using triggers that fire whenever one of them is added or deleted:

• 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

The entire system is written using triggers and stored procedures. So it is basically independent of any desktop or web application, which need only include some standard, front-end security code to use it. That's why only "approved" applications are allowed to access the database. Of course, one may bypass the security model by simply ignoring the security tables. That allows developers to test applications that are security-free, before imposing user restrictions prior to deployment. Because a single relationship connects the security tables to the gateway table, adding security to any approved app simply involves "fooling" the app by replacing the gateway table with a recordset filtered by security. In that way, legacy apps may have row-oriented security bolted onto them fairly quickly.

For convenience, a VB.NET security manager interactively defines users, roles and restrictions. Any changes to security are witnessed by users when they log in through an approved app.

This system is ideal for a Citrix server farm (where it was originally developed), where only approved applications access data and the underlying recordsets and code are hidden from the user.

Security Model

The lkpUser and lkpRole tables define users and roles, connected by the tblUserRole table.

The lkpView table defines various "views" (not SQL views) a role might use to access data from the same table under various contexts. To keep things simple for this article, there's only one view and everybody has it.

The key security table is tblRoleAccessSelect. For each record, the foreign key SelectId points to a record in the gateway table Employee for a given Role and View. Approved applications no longer see Employee (which has been renamed and hidden), but rather a subset of it silently filtered by the security table tblRoleAccessSelect. Users will not be aware of this restriction except that rows in the filtered table will appear/disappear whenever changes are made to user data or the tblRoleAccessSelectFilter table. That's because the key security table is dynamically populated by whatever filters have been added or deleted in the tblRoleAccessSelectFilter table.

The tblRoleAccessSelectFilter table assigns any number of filters to each role and view. Any such filter is simply a query with one column SelectId that lists what rows are available in the Employee table (which is our gateway table for his article).

The main problem is to dynamically change tblRoleAccessSelect whenever user data changes (which will affect the output of those filters), or when changes are made to the tblRoleAccessSelectFilter table. Triggers and stored procedures do this automatically.

The lkpAccessRow and lkpAccessColumn tables define additional row- and column- oriented restrictions that each app could automatically impose using front-end code provided by the model:

For rows, you may allow/disallow editing, adding, or deleting. For columns, you may allow hiding or locking (or specify full control). A given user may have several roles and views, so the highest priority wins in each case.

The tblRoleAccessRow table assigns available row-level restrictions to various roles and views.

The tblRoleAccessColumn table assigns available column-level restrictions to various roles and views.

It's these tables that the HR application's front-end code uses to decide how to edit rows that have been made available to it, and what columns it can see. This code may be further customized if you define more restrictions in the lkpAccessRow and lkpAccessColumn tables.

How The Model Works (server side)

The key security table tblRoleAccessSelect points to those records in the gateway table Employee that may be seen by any given role and view.

If a row is updated/inserted into the gateway table, the following trigger passes its key to the stored proc

sqRoleAccessSelectSync.

This proc insures that the correct rows in the key security table are inserted or deleted, to re-synchronize with all of the filters assigned to each role and view:

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

In other words, when user data changes, the key security table will always be in sync with the filters initially used to populate it.

The sole task of the stored proc sqRoleAccessSelectSync is to insure that for each role, view and filter in the table tblRoleAccessSelectFilter, the row (role, view, Id) belongs to the table tblRoleAccessSelect if and only if Id is selected by at least one of the filters in that table with the same role and view:

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 @RoleIdCurrentINT-- current Role for all Filters being checked
DECLARE @ViewIdCurrentINT-- current View for all Filters being checked
DECLARE @ynInsertedINT-- inserted flag for all Filters being checked with given Role, View
-- Declarations for cursor
DECLARE @RoleIdINT
DECLARE @ViewIdINT
DECLARE @FilterNameNVARCHAR(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

It does this by calling the stored proc sqRoleAccessSelectSync2 which insures that the row (role, view, Id) belongs to the table tblRoleAccessSelect if and only if Id is selected by the passed filter. However, this proc passes back to its caller a flag indicating whether an insertion occurred (or the row (role, view, Id) was already found to exist) so that the calling program knows when to avoid deleting rows unnecessarily:

ALTER PROCEDURE [dbo].[sqRoleAccessSelectSync2]
(
@IdINT,
@RoleIdINT,
@ViewIdINT,
@FilterNameNVARCHAR(256),
@ynInsertedINT 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

It should be noted that if a row is deleted from the Employee table, the (cascading delete) relationship connecting it to the key security table takes affect.

A subtle condition on the model is that if a row can/cannot be seen in the gateway table by a given filter, then it remains that way no matter what other other rows are modified. For example, if a filter sees an Employee because they live in Vancouver, that wouldn't change if someone else moves to/from that city. For a bizarre example, a filter that sees everything if and only if there are exactly 100 rows would be invalid. This condition is always satisfied by ordinary filtering for our purposes.

Of course, we must also handle the event where rows have been inserted/deleted in the tblRoleAccessSelectFilter table (updates are not allowed).

For insertions, the following trigger TR_tblRoleAccessSelectFilter_Insert on that table is used to populate the key security table with key values for the inserted role and view that aren't already present (to avoid duplicates):

ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Insert] ON [dbo].[tblRoleAccessSelectFilter] 
FOR INSERT
AS
BEGIN
-- Declarations
DECLARE @SQLStringNVARCHAR(1028)
DECLARE @RoleIdINT
DECLARE @ViewIdINT
DECLARE @FilterNameNVARCHAR(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

Deletions are a little more complicated:

ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Delete] ON [dbo].[tblRoleAccessSelectFilter] 
FOR DELETE
AS
BEGIN
-- Declarations
DECLARE @SQLStringNVARCHAR(2048)-- outer SQL string
DECLARE @SQLString1NVARCHAR(2048)-- inner SQL string consisting of UNIONs (variable number)
DECLARE @RoleIdINT
DECLARE @ViewIdINT
DECLARE @FilterNameNVARCHAR(256)
DECLARE @RoleIdSaveINT
DECLARE @ViewIdSaveINT
DECLARE @FilterNameSaveNVARCHAR(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

Here we have to be careful not to delete a row in the key security table if some other filter with the same role and view still wants it present.

How The Model Works (client side)

Each client application runs a hidden piece of security code when any form is opened, which automatically provides additional row- or column- oriented security. For example, the following snippet locks or hides any control referenced by the tblRoleAccessColumn table:

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()

Strictly speaking, the ColumnName column in the tblRoleAccessColumn table is the form's control name, not the column name of the underlying table. Of course, an app and form name should also be part of the tblRoleAccessColumn table's definition (for greater flexibility) but this is omitted for simplicity.

A Visual Basic 2005 Express source and executable package for maintaining the model (along with a front-end application and sample database) may be found in the Resource Section below.

Example

In this example there are two Users, two Roles, and one View. The Employee table has 100 randomly generated records.

User1 has Role1 while User2 has Role1 and Role2 (all views are View1 so it will be ignored in what follows).

Role1 has filter ftr1, which lists those Employees whose names start with A or B:

CREATE VIEW dbo.ftr1
AS
SELECT     EmployeeId AS SelectId
FROM       dbo.Employee
WHERE     (SUBSTRING(LastName, 1, 1) IN ('A', 'B'))

It also has ftr2 (Employees whose names start with B or C).

Role2 has filters ftr3 (Employees whose names start with C or D) and ftr4 (Employees whose names start with E).

When User2 and View1 are selected, clicking RoleAccessSelect yields 42 (Role, View, Select) combinations:

A given Employee may appear multiple times if the selected User belongs to multiple Roles (in this case 28 Employees appear 42 times with different Roles and Views).

The SQL query generated for this choice of User, Role and View is:

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

Meanwhile, on any apps using the standard security snap-in:

• Role1 hides the control tbEmail
• Role2 hides the controls tbBirthDate and tbSIN and locks the control tbEmail
• Any controls that don't exist are simply ignored.

When the application opens, 28 unique records may be seen by User2 and View1 when Employees is clicked:

The security stored proc sqMain generates the SQL that drives this listing:

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

After selecting an Employee, clicking the Test Security button will lock the Email control and hide the BirthDate and SIN controls (locking has greater priority than hiding, although this priority can be changed in the tblRoleAccessColumn table).

Of course, this demo app doesn't initially secure anything when it first opens, but it allows us to use the Test Security button to test other User-View combinations without re-starting. In real life, the code behind that button would be run when the form opens. The operator's User and View must be passed to the security stored proc sqMain. The application programmer will decide how to determine those parameters so they correspond to what's in the security tables.

Row level editing constraints are not included in this demo.

Summary

This article has presented a simple model for implementing row-oriented security in SQL Server. Although it puts some of the security burden on the client application (so it's more vulnerable) it has proven useful in situations where access to front-end applications is tightly controlled. Also, it's easy to deploy and surprisingly efficient (even when the key security table is large). Furthermore, legacy applications may use it with a simple addition to its front-end code.

For the mathematically inclined, the key security table is really the union of a dynamic set of unary relations (i.e. sets) that is automatically maintained by triggers on the data upon which that union depends.