Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Confidential Data

If you want to restrict users' read access to individual records, you have to implement a more convoluted solution:

  • The administrators should have access to the original table for maintenance purposes.
  • All regular users should access the table solely through a view which limits the access to individual rows (records).
  • The same view is also used to insert, update, or delete underlying table rows.

The intervening view is used to limit the access to table rows based on the user's role membership.

To implement secure blog posts, we'll copy the BlogPosts table to SecurePostsTable and add a Readers field to the new table to indicate who can read the posts:

SELECT * INTO SecurePostsTable FROM BlogPosts
ALTER TABLE SecurePostsTable ADD Readers nvarchar(50)

We will also create reader, writer, editor, and administrator roles for the new table:

CREATE ROLE SecureEditor
CREATE ROLE SecureReader
CREATE ROLE SecureAuthor
CREATE ROLE SecureAdministrator

The SecureAdministrator role is the only one that is granted direct access to the original table:

GRANT SELECT,REFERENCES,UPDATE,INSERT,DELETE 
 ON SecurePostsTable TO SecureAdministrator

All the other users should access the secure posts through a view that limits their access to the following records:

  • All records can be viewed by the SecureAdministrators and the dbo user.
  • Records can be viewed by a user if she is the post's author, if she's specified in the Readers field, or if she's the member of the role specified in the Readers field.

The view definition is displayed in the following listing:

CREATE VIEW SecurePosts AS
 SELECT * FROM SecurePostsTable WHERE
  USER_NAME() = ’dbo’ OR
  IS_MEMBER(’SecureAdministrator’) = 1 OR
  USER_NAME() = Author OR
  USER_NAME() = Readers OR
  IS_MEMBER(Readers) = 1

Once the view is created, we can grant permissions to access the data through the view:

GRANT SELECT,REFERENCES ON SecurePosts 
 TO SecureReader,SecureAuthor,SecureEditor,SecureAdministrator
GRANT INSERT,UPDATE,DELETE ON SecurePosts 
 TO SecureAuthor,SecureEditor,SecureAdministrator

We also have to define the same update control triggers we've used on the BlogPosts table on the SecurePostsTable:

CREATE TRIGGER SecurePosts_CheckOwner ON SecurePostsTable 
 AFTER UPDATE,DELETE
 AS BEGIN
  SELECT * FROM deleted WHERE Author <> User_Name()
  IF @@ROWCOUNT <> 0 
   EXECUTE CheckUpdateRights @RoleName = ’SecureEditor’
 END

Let's test the solution: If the database administrator tries to access the view, all records are returned:

1> select PostID,Title,Author,Readers From SecurePosts
2> go
PostID Title          Author Readers
------- ----------------------- ------ -------
   1 It’s my lucky day    Joe  NULL
   2 New author        Jill  NULL
   3 Next post        Joe  NULL
(3 rows affected)

However, when Jill tries to read the secure posts, she can only see her record:

1>EXECUTE AS LOGIN=’Jill’
2>SELECT PostID,Title,Author,Readers FROM SecurePosts
3>GO

PostID Title          Author Readers
------- ----------------------- ------ -------
   2 New author        Jill  NULL
 (1 rows affected)

Joe inserts a new post that's accessible to all blog readers (role BlogReader) afterwards:

EXECUTE AS LOGIN=’Joe’
INSERT INTO SecurePosts(Title,Contents,Author,Readers,Modified) 
 VALUES (’Public post’,’This post can be viewed by everyone’,
 USER_NAME(),’BlogReader’,GETDATE())

As expected, Jill can retrieve her post as well as Joe's new post:

1>EXECUTE AS LOGIN=’Jill’
2>SELECT PostID,Title,Author,Readers FROM SecurePosts
3>GO

PostID Title          Author Readers
------- ----------------------- ------ ----------
   7 Public post       Joe  BlogReader
   2 New author        Jill  NULL
 (2 rows affected)
  • + Share This
  • 🔖 Save To Your Account