471,071 Members | 1,528 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

More Info: Stored Procedure Security Question

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" <th************@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

Jul 20 '05 #1
6 1547
Martin Feuersteiner (th************@hotmail.com) writes:
"Martin Feuersteiner" <th************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
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" <th************@hotmail.com> wrote in message
news:cg**********@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" <th************@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


Jul 20 '05 #3
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" <da*******@nospam-earthlink.net> wrote in message news:<07*****************@newsread2.news.pas.earth link.net>...
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" <th************@hotmail.com> wrote in message
news:cg**********@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" <th************@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


Jul 20 '05 #4
Martin (th************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
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 <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>... Martin (th************@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.

Jul 20 '05 #6
Martin (th************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Martin Feuersteiner | last post: by
9 posts views Thread by Nikolay Petrov | last post: by
5 posts views Thread by Steven Blair | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.