Re: Store procedure vs Direct statement ???

On Wed, 29 Mar 2006 15:41:03 -0800, Kerry Moorman
<KerryMoorman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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
.

Relevant Pages

  • Re: Complete Neophyte Question(s)
    ... You should not have to go Google to find out what WITH GRANT means. ... Server comes with online documentation on you hard disk. ... WITH GRANT is one of the more esotheric features in SQL Server in my ... Or does that depend on the permission? ...
    (microsoft.public.sqlserver.security)
  • Re: Permission concept MSSQL05
    ... Each Server has its own security-DB where all the rolls are ... stored and their permission to each DB. ... Well, anything that worked in SQL 2000 will work in SQL 2005, but of ... there are tons of changes in the security area. ...
    (microsoft.public.sqlserver.security)
  • Hybrid sql server and asp.net windows authentication
    ... I have an asp.net web application that executes stored procedures on SQL ... I use integrated windows authentication on IIS, asp.net and SQL Server. ... a different specific NT user's credentials, one that I will give all SQL ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Problems with SLP
    ... SQL is set up using mixed mode, ... >I have set up an SLP in the lab, and when trying to browse to the SLP web ... > "The machine-default permission settings do not grant Local Activation ... > permission for the COM Server application with CLSID ...
    (microsoft.public.sms.setup)
  • Re: Stored Procedures
    ... if they have access to the server. ... Andrew J. Kelly SQL MVP ... > at client sites--or pay for them. ... > making changes to tables and stored procedures. ...
    (microsoft.public.sqlserver.security)