473,220 Members | 2,246 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,220 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 1644
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft...
1
by: Martin Feuersteiner | last post by:
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:
9
by: Nikolay Petrov | last post by:
How to fill DataSet from stored procedure?
5
by: Steven Blair | last post by:
I am using an objectdatasource and binding to an aggregate data type. My aggregate data type is ebing populated like: http://www.rafb.net/paste/results/6WeLS821.html Is there some way of...
5
by: James Wong | last post by:
Hi, I am writing a vb.net2005 program that needs to create a stored procedure with SqlServerProject Template. Now, I have two questions for this stored procedure. 1) How can I import and...
7
by: Siv | last post by:
Hi, I have a stored procedure that I want to execute and then wait in a loop showing a timer whilst it completes and then carry on once I get notification that it has completed. The main reason...
2
by: Roger | last post by:
I have a stored procedure running on DB2 V7 Z/os calling a COBOL program to do some inserts. The stored procedure have 3 input columns and one column is of varchar(32648) The stored procedure is...
0
by: Roger | last post by:
have a stored procedure running on DB2 V7 Z/os calling a COBOL program to do some inserts. The stored procedure have 3 input columns and one column is of varchar(32648) The stored procedure is...
3
by: Bob Alston | last post by:
I have recently been trying to determine the best technique to pull the least amount of info across the LAN link in a slow speed LAN situation (e.g. < 10 Mbps), where data volume = performance. ...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.