I have found that table A had SELECT permissions for 'Public' but not table
B.
Giving 'Public' SELECT permissions on table B did the trick.
HOWEVER, I don't want anyone to be able to do a direct SELECT on table A or
B but only give them access to the data by using the stored procedures. Is
there any way this can be set up?
Thanks for your efforts!
Have a nice day!
Martin
"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message news:...
> Dear Group
> I'm having two stored procedures, sp_a and sp_b
> Content of stored procedure A:
> CREATE PROCEDURE dbo.sp_a
> SELECT * FROM a
> GO
> Content of stored procedure B:
> CREATE PROCEDURE dbo.sp_b
> SELECT * FROM b
> GO
> I have created a user that has execute permissions for both procedures.
> When I run procedure A, all works fine but when running procedure B I'm
> getting an error saying that the user must have SELECT permissions on
> table B.
> Both tables are owned by dbo, and the security role for the user doesn't
> has any SELECT permission on table a and b.
> I'd be grateful if anyone could point me in a direction why this error
> might come up for procedure B but not for A,
> with a possible solution without giving the user SELECT permissions.
> Thanks very much for your help!
> MartinMartin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
>> Content of stored procedure A:
>> CREATE PROCEDURE dbo.sp_a
>> SELECT * FROM a
>> GO
>>
>> Content of stored procedure B:
>> CREATE PROCEDURE dbo.sp_b
>> SELECT * FROM b
>> GO
> I have found that table A had SELECT permissions for 'Public' but not
> table B. Giving 'Public' SELECT permissions on table B did the trick.
> HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
> or B but only give them access to the data by using the stored
> procedures. Is there any way this can be set up?
I have a strong feeling that you are not telling us the full story,
because what you have described is the typical usage of ownership
chaining, and users should indeed be able to access the data in the
tables through the stored procedures.
Is there by chance some dynamic SQL involved?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I also responded to your previous thread. As Erland said, this should work
as long as the objects are in the same database. If in different databases,
you'll need to enable cross-database chaining and the databases need to have
the same owner in order to maintain an unbroken ownership chain for
dbo-owned objects.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:cg53pg$rbo$1@.sparta.btinternet.com...
> Dear Group
> I have found that table A had SELECT permissions for 'Public' but not
table
> B.
> Giving 'Public' SELECT permissions on table B did the trick.
> HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
or
> B but only give them access to the data by using the stored procedures. Is
> there any way this can be set up?
> Thanks for your efforts!
> Have a nice day!
> Martin
>
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:...
> > Dear Group
> > I'm having two stored procedures, sp_a and sp_b
> > Content of stored procedure A:
> > CREATE PROCEDURE dbo.sp_a
> > SELECT * FROM a
> > GO
> > Content of stored procedure B:
> > CREATE PROCEDURE dbo.sp_b
> > SELECT * FROM b
> > GO
> > I have created a user that has execute permissions for both procedures.
> > When I run procedure A, all works fine but when running procedure B I'm
> > getting an error saying that the user must have SELECT permissions on
> > table B.
> > Both tables are owned by dbo, and the security role for the user doesn't
> > has any SELECT permission on table a and b.
> > I'd be grateful if anyone could point me in a direction why this error
> > might come up for procedure B but not for A,
> > with a possible solution without giving the user SELECT permissions.
> > Thanks very much for your help!
> > Martin|||Thanks for your help guys!
Well, as Erland suspected, I haven't given you the full story as I
thought it doesn't matter but as I found out the hard way, it was
indeed dynamic SQL that caused the problem.
Erland, please don't tell me off for using dynamic SQL! LOL
I've read your wonderful fantastic guides and obbey all rules on
dynamic SQL but although I'm not happy myself, I think I really can't
avoid it this time.
However, if you'd offer to have a look at my script and just tell me
whether it can be done without dynamic SQL then this would make me
very happy!
Anyway, I solved the permission problem by basing the stored
procedures that contain the dynamic SQL on Views and implementing row
level security in those.
Thanks again for your efforts!
Have a nice day!
Martin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<07JVc.8691$3O3.3742@.newsread2.news.pas.earthlink.n et>...
> I also responded to your previous thread. As Erland said, this should work
> as long as the objects are in the same database. If in different databases,
> you'll need to enable cross-database chaining and the databases need to have
> the same owner in order to maintain an unbroken ownership chain for
> dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
> news:cg53pg$rbo$1@.sparta.btinternet.com...
> > Dear Group
> > I have found that table A had SELECT permissions for 'Public' but not
> table
> > B.
> > Giving 'Public' SELECT permissions on table B did the trick.
> > HOWEVER, I don't want anyone to be able to do a direct SELECT on table A
> or
> > B but only give them access to the data by using the stored procedures. Is
> > there any way this can be set up?
> > Thanks for your efforts!
> > Have a nice day!
> > Martin
> > "Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
> news:...
> > > Dear Group
> > > > I'm having two stored procedures, sp_a and sp_b
> > > > Content of stored procedure A:
> > > CREATE PROCEDURE dbo.sp_a
> > > SELECT * FROM a
> > > GO
> > > > Content of stored procedure B:
> > > CREATE PROCEDURE dbo.sp_b
> > > SELECT * FROM b
> > > GO
> > > > I have created a user that has execute permissions for both procedures.
> > > When I run procedure A, all works fine but when running procedure B I'm
> > > getting an error saying that the user must have SELECT permissions on
> > > table B.
> > > > Both tables are owned by dbo, and the security role for the user doesn't
> > > has any SELECT permission on table a and b.
> > > I'd be grateful if anyone could point me in a direction why this error
> > > might come up for procedure B but not for A,
> > > with a possible solution without giving the user SELECT permissions.
> > > > Thanks very much for your help!
> > > > Martin
>|||Martin (theintrepidfox@.hotmail.com) writes:
> Erland, please don't tell me off for using dynamic SQL! LOL
> I've read your wonderful fantastic guides and obbey all rules on
> dynamic SQL but although I'm not happy myself, I think I really can't
> avoid it this time.
> However, if you'd offer to have a look at my script and just tell me
> whether it can be done without dynamic SQL then this would make me
> very happy!
Well, there are cases where dynamic SQL is the best solution and there
are cases where dynamic SQL is a really poor choice.
The whole message of http://www.sommarskog.se/dyn-search.html is that
for dynamic search conditions is "use dynamic SQL, if you can handle
the security issues". If you can make it with views, then you should
be fine.
Beware though, that a very skilled person can be able to cram out
information from a view for row-based security that he is not supposed
to have access to. It is not that he can actually get to see the rows,
but he can make conclusions from query plans statistical IO and such.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks very much Erland!
>It is not that he can actually get to see the rows,
> but he can make conclusions from query plans statistical IO and such.
Do you know any source with more information on this view security issue?
What harm can it do? It's a CRM app, not a top secret military app.
Thanks for your efforts!
M
Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns954FEB97D2796Yazorman@.127.0.0.1>...
> Martin (theintrepidfox@.hotmail.com) writes:
> > Erland, please don't tell me off for using dynamic SQL! LOL
> > I've read your wonderful fantastic guides and obbey all rules on
> > dynamic SQL but although I'm not happy myself, I think I really can't
> > avoid it this time.
> > However, if you'd offer to have a look at my script and just tell me
> > whether it can be done without dynamic SQL then this would make me
> > very happy!
> Well, there are cases where dynamic SQL is the best solution and there
> are cases where dynamic SQL is a really poor choice.
> The whole message of http://www.sommarskog.se/dyn-search.html is that
> for dynamic search conditions is "use dynamic SQL, if you can handle
> the security issues". If you can make it with views, then you should
> be fine.
> Beware though, that a very skilled person can be able to cram out
> information from a view for row-based security that he is not supposed
> to have access to. It is not that he can actually get to see the rows,
> but he can make conclusions from query plans statistical IO and such.|||Martin (theintrepidfox@.hotmail.com) writes:
> Do you know any source with more information on this view security issue?
> What harm can it do? It's a CRM app, not a top secret military app.
As long as you don't let SQL Server MVP Steve Kass anywhere near the
database, I think your data is fairly safe. :-) That is, Steve Kass was
the one who discovered this issue, and to exploit you would need to
a query tool like Query Analyzer, and you would probably have to have
some knowledge about the schema. And you need a very good understanding
of SQL Server. Finally a good dosis of patience is good for the task.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
