Implementing Access Controls on SQL Server Data
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Most relational databases provide fine-tuned access controls to various objects in the database, including tables, views, and indices, but lack the support for individual row (record) access control. It's similar to how an operating system would provide access control to directories (repositories of files) but not individual files (smallest addressable chunks of data). Developers using document-oriented databases (for example, Lotus Notes) are already familiar with the finer access control granularity that you need in some sensitive environments. In this article, you'll see how you can achieve the same results using a traditional relational database. The provided code samples work with Microsoft's SQL Server 2005, but can be easily adapted to other database environments with equivalent functionality (access controls on views, insert/update triggers).
Throughout the article, we'll work with a sample database supporting a simple corporate blog application. The structure of the blog posts table (the only table we'll work with) is outlined in the following printout:
CREATE TABLE BlogPosts ( PostID int IDENTITY(1,1) NOT NULL, Title nvarchar(50) NOT NULL, Contents nvarchar(max) NULL, Author nvarchar(50) NOT NULL CONSTRAINT DF_Products_Owner DEFAULT (USER_NAME()), Modified datetime NOT NULL CONSTRAINT DF_BlogPosts_Modified DEFAULT (GETDATE()), Published datetime NULL, CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (PostID ASC) )
You can download the sample database from my web site; after you install it into your SQL Server, you’ll have to create two new logins (or use existing ones) and map them to users Jill and Joe in the sample database with the following commands (replace LOGIN parameters with actual login names):
USE [AccessControls] GO ALTER USER [Jill] WITH LOGIN=[Jill] ALTER USER [Joe] WITH LOGIN=[Joe] GO
Control the Updates
Let's start with a simple task: All users authorized to access the database can read the blog posts, but only some can author them. Only the authors and authorized editors can edit or delete their posts.
We'll use standard SQL Server access control mechanisms to control read and write access to the table:
- All users of the database should be members of the db_datareader role to gain read access to the BlogPosts table (or you could GRANT them SELECT permission on the BlogPosts table).
- Authors and editors should be granted INSERT, UPDATE, and DELETE permissions on the BlogPosts table or should be made members of the db_datawriter role.
For a large-scale deployment, it's better to create two new database roles (BlogReader and BlogWriter), grant them access to the BlogPosts table, and make blog users members of these roles:
/* Create roles and grant them table access */ CREATE ROLE BlogReader CREATE ROLE BlogWriter GRANT SELECT,REFERENCES ON BlogPosts TO BlogReader GRANT INSERT,DELETE,UPDATE ON BlogPosts TO BlogWriter /* Add users to roles */ sp_addrolemember @rolename=’BlogReader’,@membername=’Joe’ sp_addrolemember @rolename=’BlogReader’,@membername=’Jill’ sp_addrolemember @rolename=’BlogWriter’,@membername=’Joe’ sp_addrolemember @rolename=’BlogWriter’,@membername=’Jill’
Editors will also be identified by their membership in the BlogEditor role:
CREATE ROLE BlogEditor GRANT SELECT,REFERENCES,INSERT,DELETE,UPDATE ON BlogPosts TO BlogEditor sp_addrolemember @rolename=’BlogEditor’,@membername=’admin’
The insertion of new blog posts is controlled by the SQL access controls on the BlogPosts table; the updates and deletions cannot be controlled as easily, as the SQL Server allows a user with UPDATE permissions on a table to change any row in the table. We’ll fix the problem with a trigger that will check whether our access control rules allow the user to perform the operation. The trigger is executed after the UPDATE or DELETE operation, uses the deleted table to identify the affected rows, and calls the CheckUpdateRights stored procedure if the user is trying to change and/or delete a row not belonging to her.
CREATE TRIGGER BlogPosts_CheckOwner ON BlogPosts AFTER UPDATE,DELETE AS BEGIN SELECT * FROM deleted WHERE Author <> User_Name() IF @@ROWCOUNT <> 0 EXECUTE CheckUpdateRights @RoleName = ’BlogEditor’ END
The CheckUpdateRights procedure allows all table operations to the dbo user, the members of the editor role (its name is passed in the @EditorRole parameter), and rejects all other attempts at unauthorized data modifications:
ALTER PROCEDURE CheckUpdateRights @EditorRole varchar(40) = ’BlogEditor’ AS BEGIN SET NOCOUNT ON; -- don’t check DBO account IF USER_NAME() = ’dbo’ RETURN; -- members of the EDITOR role can update/delete any record IF IS_MEMBER(@EditorRole) = 1 RETURN; -- The user is trying to touch a record not owned by her, -- report an error and rollback the transaction RAISERROR(’Cannot modify records not owned by yourself’,16,1); ROLLBACK TRANSACTION END
Let's test the code on the sample database. When the database administrator sets all the Published dates to NULL, the operation succeeds (the printouts have been slightly abbreviated):
1> select USER_NAME()
2> UPDATE BlogPosts SET Published = NULL
3> go
----------
dbo
(1 rows affected)
PostID Author Published
----------- --------------- -----------------
2 Jill NULL
1 Joe NULL
(2 rows affected.)
However, when Joe tries to set the Published date on Jill's post, the operation fails:
1> EXECUTE AS LOGIN = ’Joe’ 2> UPDATE BlogPosts SET PUBLISHED=GetDate() WHERE PostID=2 3> GO Msg 50000, Level 16, State 1, Procedure CheckUpdateRights, Line 18 Cannot modify records not owned by yourself Msg 3609, Level 16, State 1, Server BRAWNIX\SQLEXPRESS, Line 2 The transaction ended in the trigger. The batch has been aborted.