Store procedure vs Direct statement ???

[Guest]

Dear all,

Is there any restriction or rules when you must you Store procedure call
within application code compare to direct SQL statement.

For sure the first reason is data security as all querry are executed on the
server.

But is there some cases where store procedure should be avoid ?
For my case if data security is a must, the first thing would be to always
use strore procedure

Any comments are welcome

Regards
Serge

 

[Cor Ligthert [MVP]]

Serge,

While developing an application it is in my idea complete madness to start
with the SP.

Just my thought,

Cor

 

[Guest]

Hi Cor,

Why it is so madness according to you ?
I browse many places over here, talking about sensitive data when use in a
web application. First simple recommandation was to use store procedure for
easy manitenance nad security issue.

I did not mentionned that it is fully secure, but a starting point

I would love to hear your opinion on that then !!

Regards
Serge

 

[Cor Ligthert [MVP]]

serge,

Just that it is easier to do it dynamic. You don't have to contact your
Admin.

As it is ready the change to a SP is than simple.

One character can cost a lot of time you know if you have to talk about it
if that replacement for that ill Admin can do it for you.

However just my thought,

Cor

 

[Guest]

Serge,

All sql is executed on the server, so stored procedures don't have any
security advantage over dynamic sql in that instance.

If you use parameters in your dynamic sql then you will gain the same
security benefits as using a stored procedure in terms of sql injection.

I think the decision is one of design philosopy as opposed to security or
performance.

Kerry Moorman

 

[Hendrik]

One big thing can be performance. If you have a huge query with lots of
joins, the Stored Procedure is going to execute quicker. Stored
Procedures are compiled, whereas a query is first compiled and then
executed.

I understand Cor's opinion completely, I have had the same trouble. I
try to stick to dynamic statements for update / delete / insert, and
use Stored Procedures when returning big amounts of data. And yes, try
to stick to using parameters.

 

[Guest]

Hendrik,

I don't believe stored procedures are compiled in some way different than
dynamic sql.

Here is an article that discusses theses issues:

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Kerry Moorman

 

[Frans Bouma [C# MVP]]

One big thing can be performance. If you have a huge query with lots
of joins, the Stored Procedure is going to execute quicker. Stored
Procedures are compiled, whereas a query is first compiled and then
executed.

Only DB2 compiles procs and only procs written in non-SQL languages.

Performance of procs is only higher in data-processing procs where
transfering the data back to the caller to do the processing is slower
than the SQL statements processing the data. For CRUD it doesn't make a
difference.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Marina Levit [MVP]]

Stored procedure do have a security advantage.

Let's say you have a stored procedure called InsertCustomer, that takes some
parameters, and adds a customer. Ok, great. You can have a sql account that
has access to this stored procedure, but nothing else.

If you instead allow your app to do dynamic sql, then your sql account needs
rights to the Customer table. As soon as someone gets access to your
application and can alter the sql or something like that, they can now do
anything to the Customer table that they want - like delete all customers,
etc.

So, in this sense a stored procedure is more secure.

 

[Guest]

Marina,

My comment to the OP was that stored procedures did not have a security
advantage over dynamic sql because of executing on the server, since dynamic
sql also executes on the server.

In response to your example of stored procedures having a security advantage
over dynamic sql because of the ability to control access, only granting the
account insert permission on the Customer table would seem to have the same
effect as only granting the account access to an InsertCustomer stored
procedure.

Or maybe I'm missing something?

Kerry Moorman

 

[Robbe Morris [C# MVP]]

You'll want to dig deep into how sql server processes
commands and what the sql provider for .net really
does.

The last half of this article will be a bit of an eye
opener and give you a good start to research
this more in the sql server documentation.

The .net framework could was run using
Lutz's reflector against a 1.1 assembly.

You'll want to review the section that
starts here. This down at the lowest level
prior to the provider submitting the byte
arrays to sql server.

System.Data.SqlClient.TdsParser.TdsExecuteRPC

http://www.eggheadcafe.com/articles/adonet_exec_stored_procedures_sqlhelper.asp

 

[Frans Bouma [C# MVP]]

Stored procedure do have a security advantage.

Let's say you have a stored procedure called InsertCustomer, that
takes some parameters, and adds a customer. Ok, great. You can have a
sql account that has access to this stored procedure, but nothing
else.

That's not going to work, the app accessing the proc (why else have a
proc?) still needs to know the account and has to connect to the db
using that account.

If you instead allow your app to do dynamic sql, then your sql
account needs rights to the Customer table. As soon as someone gets
access to your application and can alter the sql or something like
that, they can now do anything to the Customer table that they want -
like delete all customers, etc.

So, in this sense a stored procedure is more secure.

No it's not. My app instead needs access to your insertcustomer and
deletecustomer proc. So I can then hack the connection string out and
connect directly to the db and execute pr_DeleteCustomer by passing a
random ID. pooff...

What's the difference? nothing.

The only way to do this properly is by passing in a token to every
proc and check that token inside the DB. With manually access I don't
know the token. However even that can be hacked out if I want to, as
the token has to be passed by an application as well.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Otis Mukinfus]

Marina,

My comment to the OP was that stored procedures did not have a security
advantage over dynamic sql because of executing on the server, since dynamic
sql also executes on the server.

In response to your example of stored procedures having a security advantage
over dynamic sql because of the ability to control access, only granting the
account insert permission on the Customer table would seem to have the same
effect as only granting the account access to an InsertCustomer stored
procedure.

Or maybe I'm missing something?

Kerry Moorman

Yes, you are missing something.

When you give a user access to a stored proc you give it only to the SP, not the
table it accesses. When you give a user access to a table they have the ability
to write their own SQL and access the table with it (perhaps with a query
building tool such as the one in MS Access).

When you give users permission to access tables you must be aware you have given
them access not just from your application, but from any SQL they send to the
server, from anywhere.

The only thing a user can do with a SP (if they know the name of it) is execute
it. You have controlled what the SP can do to your database.

Power users at a site are notorious for cranking up Access, connecting to a
database and using Access to get to data on the databases. If you have given
one of those folks delete, update or insert permission on a table, watch out,
they can change your data from outside your application. Even if they only have
select permission they can cause you trouble. Suppose one of them does a select
* from a ten million row table. I recon you might see a performance hit from
something like that.

I agree with the poster who said they don't like dealing with the admin, but in
a sensible development environment you will have permission to create what ever
you need on the DEV server. After development is complete and you go live the
creation of database objects is a maintenance operation that can be developed
and tested on the DEV server before going into production. In that environment
the admin becomes a tester, tweaker and mover.

Sorry about the long winded answer ;o)
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Guest]

Otis,

You answered a different question than the one I asked. I asked:

"Only granting the account insert permission on the Customer table would
seem to have the same effect as only granting the account access to an
InsertCustomer stored procedure.

Or maybe I'm missing something?"

You answered:

"Yes, you are missing something.
..
..
..
If you have given one of those folks delete, update or insert permission on
a table, watch out, they can change your data from outside your application."

But my scenario stated that the only permission the user would have on the
table is insert, just like they only have access to a stored procedure for
insert.

I still don't see where I am missing anything with regard to the specific
scenario that I was addressing.

Kerry Moorman

 

[Guest]

AT first , I was just asking a simple question.
I did not know that it raise so many missunderstanding and different point
of view.
But the good thing once again, is that we learn from experience of others.

Why I come to this was the simple fact that I was in front of that situation
when preparing my MCAD, and I have to say that I was quite embarassed to get
a clear view of when using SP instaed of dynamic SQL. And most answer I get
was in 2 words "Security" , "Performance". Then it seems that it is not the
case according to some article point here and comments.

Now in order to get a conclusion to my initial question, let say that you
are in front of an audiance who would ask you to define rules or simple guide
lines which might help to go to SP or Dynamic integration, what will be the
answer then ?

COmplexe querry = SP for performance ?
SImple querry = dynamic ?

serge

 

[Otis Mukinfus]

Otis,

You answered a different question than the one I asked. I asked:

"Only granting the account insert permission on the Customer table would
seem to have the same effect as only granting the account access to an
InsertCustomer stored procedure.

Or maybe I'm missing something?"

You answered:

"Yes, you are missing something.
.
.
.
If you have given one of those folks delete, update or insert permission on
a table, watch out, they can change your data from outside your application."

But my scenario stated that the only permission the user would have on the
table is insert, just like they only have access to a stored procedure for
insert.

I still don't see where I am missing anything with regard to the specific
scenario that I was addressing.

Kerry Moorman

The difference is:

With a stored procedure you give the user insert permission when using the
stored procedure, but by no other means. They can't do an insert from Access,
etc. because they do not have permission to access the table in any way except
through the sp.

If you give them insert permission on the table they can insert data at any time
from any place. You have no way of controlling when or what they insert.

I suspect your viewpoint on this matter may be why you don't like going to the
admin to get things done.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Otis Mukinfus]

AT first , I was just asking a simple question.
I did not know that it raise so many missunderstanding and different point
of view.
But the good thing once again, is that we learn from experience of others.

Why I come to this was the simple fact that I was in front of that situation
when preparing my MCAD, and I have to say that I was quite embarassed to get
a clear view of when using SP instaed of dynamic SQL. And most answer I get
was in 2 words "Security" , "Performance". Then it seems that it is not the
case according to some article point here and comments.

Now in order to get a conclusion to my initial question, let say that you
are in front of an audiance who would ask you to define rules or simple guide
lines which might help to go to SP or Dynamic integration, what will be the
answer then ?

COmplexe querry = SP for performance ?
SImple querry = dynamic ?

serge

Sere,

I agree with those who say that sp's do not necessarily improve performance.

In these days of so many folks hell bent on breaching security, I design my data
access for security.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Frans Bouma [C# MVP]]

The difference is:

With a stored procedure you give the user insert permission when
using the stored procedure, but by no other means. They can't do an
insert from Access, etc. because they do not have permission to
access the table in any way except through the sp.

If you give them insert permission on the table they can insert data
at any time from any place. You have no way of controlling when or
what they insert.

I suspect your viewpoint on this matter may be why you don't like
going to the admin to get things done.

Define admin role and general role in your db and perhaps other roles
if you want more granuality
give access rights to roles on your tables
add users to the roles in question.
allow users to connect to the db with the user id you provided.

So, say my user isn't in the admin role, so I for example can only
select on given tables. How is that going to allow me to insert
anything in the tables? it doesn't.

You, as well as other pro-proc-for-crud-people, completely ignore the
fact that if I have a pr_InsertCustomer proc, what's the difference
between me calling the proc or me executing an insert into table ...
statement? Nothing.

Frans


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Otis Mukinfus]

So, say my user isn't in the admin role, so I for example can only
select on given tables. How is that going to allow me to insert
anything in the tables? it doesn't.

You, as well as other pro-proc-for-crud-people, completely ignore the
fact that if I have a pr_InsertCustomer proc, what's the difference
between me calling the proc or me executing an insert into table ...
statement? Nothing.

Frans

Ah, Frans, now you are beginning name calling ("pro-proc-for-crud-people"). I
didn't think you were such a person. Too bad.

Anyway this thread has run it's course. It's become like the 'what is best C#
or Java?' discussions.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Frans Bouma [C# MVP]]

Ah, Frans, now you are beginning name calling
("pro-proc-for-crud-people"). I didn't think you were such a person.
Too bad.

ah, nittpicking on a detail to avoid the real discussion, eh? .

But, weren't you propagating procs for crud? That's what you were
doing, IMHO.

Anyway this thread has run it's course. It's become like the 'what
is best C# or Java?' discussions.

Looking at some replies in this thread, including yours, I don't think
it's a discussion about 'which is better' between equivalent
technologies, at least not according to some in this discussion (mind
you: allthough I wrote that dreaded article 'Stored procedures are bad,
m'kay?', I don't think it's that black and white: use what you think is
best, however use the right argumentation why you use what you use. 'a
proc is more secure' is a myth in 99% of the proc-using systems, simply
because the procs don't accept tokens which aren't known to the human
who wants to execute them through QA.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------