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?
Thanks!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/techin.../2000/books.asp|||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/techin.../2000/books.asp|||Anon (anon email) writes:
> 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.
I will have to admit that if you granted Dept1 rights on dbo.Book, and
then the users rights to Dept1.book it would work, but nope. In fact
I even tried creating a stored procedure Dept1.book_sp and grant users
execute rights on that one, but that also failed. However, this latter
arrangeent actually works on SQL 6.5, so at least I did remember
correctly so far. (But Microsoft has changed the rules. Grr!)
Right now, I have to good ideas to get this to work in SQL 2000. In
SQL 2005, it would be another matter, because Dept1 would just be a
schema, that still could be owned by dbo.
Of course, you can create the view as dbo.Dept1books, but I don't
if that meets your ambition to fool the shrink-wrap package.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Unfortunately it seems our quick trial was on a 2005 server, and that
remains in Beta. Sigh. Does anyone have any other ideas on how to
accomplish this?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9667F329E4C86Yazorman@.127.0.0.1...
> Anon (anon email) writes:
>> 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.
> I will have to admit that if you granted Dept1 rights on dbo.Book, and
> then the users rights to Dept1.book it would work, but nope. In fact
> I even tried creating a stored procedure Dept1.book_sp and grant users
> execute rights on that one, but that also failed. However, this latter
> arrangeent actually works on SQL 6.5, so at least I did remember
> correctly so far. (But Microsoft has changed the rules. Grr!)
> Right now, I have to good ideas to get this to work in SQL 2000. In
> SQL 2005, it would be another matter, because Dept1 would just be a
> schema, that still could be owned by dbo.
> Of course, you can create the view as dbo.Dept1books, but I don't
> if that meets your ambition to fool the shrink-wrap package.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Anon (anon email) writes:
> Unfortunately it seems our quick trial was on a 2005 server, and that
> remains in Beta. Sigh. Does anyone have any other ideas on how to
> accomplish this?
Maybe you could start to give the full presumptions for your case. You've
presented some scattered some information, from which I was able to make
some guesses. But it does help to know what exact degrees of freedom
you have with your shrink-wrap.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
No comments:
Post a Comment