Posted by Anon on 10/01/97 11:17

Sorry, I didn't clarify the view; it is created using "select * from
dbo.BOOK". However, when a user with rights to Dept1.BOOK but not to
dbo.BOOK attempts to run the query they get an error that states

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'BOOK', database 'LIBRARY', owner 'dbo'.

What we'd like to see is the explicit rights on the View supercede the
rights on the table, but that doesn't seem to be the case.



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9662F1EE69756Yazorman@127.0.0.1...
> 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]