Posted by Erland Sommarskog on 05/27/05 00:48

Anon (anon email) writes:
> We are attempting to implement security on top of a shrink-wrapped
> software package and are trying to get row-level security. Here's the
> scenario:
>
> 1. Table dbo.BOOK contains all the information about books in every
> department.
> 2. There are a large number of developed reports that run queries like
> "select * from BOOK..."
> 3. We wish to have each Department only be able to see their books -
> without changing the existing reports.
>
> Our thought was to create a series of views:
>
> create view Dept1.BOOK as
> select * from BOOK where Dept=1
>
> ...
>
> and then create Roles for each Dept. We'd then remove rights to
> dbo.BOOK and grant rights to DeptN.BOOK as appropriate for each role.
> We started testing this and seemed to get it working, but are now having
> problems. Is this possible? Is there another, better solution?

And the problems you get are?

Whther this will work a lot, depends on your shrink-wrap. After all,
you are doing something for which it is not prepared. Updates would
fail, but you could have INSTEAD OF triggers to cate for that.

In the view definition, I would recommend that you say dbo.BOOK for
clarity.

You should also beware of that this sort of row-level security is not
fool-proof. It is possible to dig out information about data you don't
have access to. Then again, it's not trivial and it does require
expert skills to do it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]