Like this article? We recommend
Like this article? We recommend
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)