Home > Articles > Data > SQL Server

Implementing Access Controls on SQL Server Data

  • Print
  • + Share This
Ivan Pepelnjak describes how you can extend the rich set of security mechanisms offered by Microsoft SQL Server (and other enterprise-grade relational databases) to provide a very fine-grained access control to individual records in the most sensitive tables of your database.
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.

  • + Share This
  • 🔖 Save To Your Account