More Info: Stored Procedure Security Question

[Martin Feuersteiner]

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

[Erland Sommarskog]

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****@sommarsk og.se

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

[Dan Guzman]

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.btint ernet.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

[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" <da*******@nosp am-earthlink.net> wrote in message news:<07******* **********@news read2.news.pas. earthlink.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.btint ernet.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

[Erland Sommarskog]

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****@sommarsk og.se

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

[Martin]

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****@sommars kog.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.

[Erland Sommarskog]

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****@sommarsk og.se

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