Re: Store procedure vs Direct statement ???

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

"Kerry Moorman" <KerryMoorman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1C52B90F-571C-4004-9DA7-0FCF8F6263ED@xxxxxxxxxxxxxxxx
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


"serge calderara" wrote:

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


.

Relevant Pages

  • Re: Stored proceudre SQL reuse ODP?
    ... Maybe you could call a routing procedure and then have it call the stored procedure instance for that customer and have a separate stored procedure for each customer. ... If the SQL statements in the procedure are being handled by SQE, then it's less of an issue. ... The stored procs are called remotely from a Net.Data macro on a Web server to the remote database on another partition. ...
    (comp.sys.ibm.as400.misc)
  • Re: Store procedure vs Direct statement ???
    ... advantage over dynamic sql because of executing on the server, ... sql also executes on the server. ... In response to your example of stored procedures having a security advantage ... Let's say you have a stored procedure called InsertCustomer, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: security
    ... administrator is customer not me. ... >> from network and attach it to a sql on windows 98 my db security breaks. ...
    (microsoft.public.sqlserver.security)
  • Re: Return to previous stored procedure
    ... John wrote: ... > I am very weak on SQL 2000 so I am not sure if I am asking this right. ... Tell your customer to use a source code control program like MS ... SourceSafe or an SQL IDE tool like Imceda Speed IDE for stored procedure ...
    (microsoft.public.sqlserver.server)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)