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