Re: Alternative to Dynamic SQL?
- From: sqlguru@xxxxxxxx
- Date: Tue, 2 Jun 2009 04:52:58 -0700 (PDT)
This is commonly done to prevent users from directly modifying the contents
of tables that should only be modified through a specific front end
application, for instance.
I don't know what kind of IT company you're working for but ANY of the
enterprise companies, SQL servers are fully locked down. That means
that a user cannot just directly connect and start modifying tables.
We have a single user per database (sometimes per application). This
isn't an access database, users shouldn't connect and start modifying
data without going through a front-end.
Security is very much a concern for professional DBAs -- at least the ones
who can't afford to shutdown their SQL servers and can't afford to have
their data mindlessly destroyed by so-called "power users" or stolen by
hackers.
Don't be a mindless drone, SQL Server is not an access database, there
is no such thing as "power users" except in 1980s IT companies with
very old methodologies. Users do not connect to a SQL server, period.
All modifications go to the DBAs as scripts.
Have you tried maintaining 1000s of queries? You get Intellisense when
you're assigning a query to a C# string variable, do you? Now you're
talking the "same difference."
With inline queries, each query is usually tied to a method. This
gives it much more "meaning" and what the query does and how it
relates to the method/application. With stored procedures, you got
5000 stored procedure, the only clue you got to as what each SP does
is maybe the name.
When you fire a SELECT query at the database, what exactly are you doing?
This is something more than "asking for data from the database" and "letting
the database worry about retrieving it"? This really makes no sense.
What I meant was that some companies let the DBA do all the database
side processes and the developers do the application side processes.
The problem with this is that sometimes, the two groups are not in
sync for specialized problems. For an example, if Celko was the
DBA....and Tony was the developer and Tony wanted a pagination query,
Celko would probably return the whole table to the front-end for
pagination. The better way would be to do a database side pagination
query.
OR when a complex search interface is being made, the DBA would
probably create a 1000-parameter stored procedure instead of an inline
query.
On Jun 1, 9:48 pm, "Michael Coles" <admin.AT.geocodenet.DOT.com>
wrote:
<sqlg...@xxxxxxxx> wrote in message
news:1b0960b6-d228-40c0-9ee5-07004548bfc7@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Why would you deny a specific user access to the DB? Do users really
connect to your database and run queries??? This is a very poor
argument. If security is that much of a concern, shutdown your sql
servers.
You might deny a user specific access to your DB to keep them out. Happens
all the time, all over the world, every single day.
However, I said deny a user access to a TABLE, which is obviously different
from a DB.
This is commonly done to prevent users from directly modifying the contents
of tables that should only be modified through a specific front end
application, for instance.
Security is very much a concern for professional DBAs -- at least the ones
who can't afford to shutdown their SQL servers and can't afford to have
their data mindlessly destroyed by so-called "power users" or stolen by
hackers.
I would highly recommend you Google up some free information on "ownership
chaining" for a complete discussion of this particular topic, and the
real-world implications to the security of the data in your SQL Server.
In the application, each database is suppose to have an application
side DAL where all applications depending on that database go through
that specific DAL. Stored procedures could be considered a "DAL" but
have you tried maintaining 1000s of SPs? Especially when theres
obsolete ones? Also, when there are modifications, how do you refactor
or do you even get intellisense? Do you just create another SP?
Have you tried maintaining 1000s of queries? You get Intellisense when
you're assigning a query to a C# string variable, do you? Now you're
talking the "same difference."
The idea that the application should just ask for data from the
database and let the database worry about retrieving it will have some
serious performance impact on the application. For an example, like
pagination and search interfaces where using static SQL or stored
procedures is just "cowboy coder" insane.
<< "The idea that the application should just ask for data from the database
and let the database worry about retrieving it will have some performance
impact on the application." >>
When you fire a SELECT query at the database, what exactly are you doing?
This is something more than "asking for data from the database" and "letting
the database worry about retrieving it"? This really makes no sense.
The pagination issue is a well-defined problem with literally hundreds of
solutions all over the place. Search interfaces are also a well-defined
problem, and can also be solved in a variety of ways. Not sure what your
point is here...
--
========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X
.
- Follow-Ups:
- Re: Alternative to Dynamic SQL?
- From: Michael Coles
- Re: Alternative to Dynamic SQL?
- References:
- Alternative to Dynamic SQL?
- From: SQL
- Re: Alternative to Dynamic SQL?
- From: --CELKO--
- Re: Alternative to Dynamic SQL?
- From: sqlguru
- Re: Alternative to Dynamic SQL?
- From: Erland Sommarskog
- Re: Alternative to Dynamic SQL?
- From: sqlguru
- Re: Alternative to Dynamic SQL?
- From: Michael Coles
- Re: Alternative to Dynamic SQL?
- From: sqlguru
- Re: Alternative to Dynamic SQL?
- From: Michael Coles
- Alternative to Dynamic SQL?
- Prev by Date: Re: xp_cmdshell default path (system32) problem
- Next by Date: Re: Date query
- Previous by thread: Re: Alternative to Dynamic SQL?
- Next by thread: Re: Alternative to Dynamic SQL?
- Index(es):
Relevant Pages
|
