[dba-SQLServer] Difference between views and queries

Francis Harvey HARVEYF1 at WESTAT.com
Tue Jun 29 17:21:44 CDT 2004


Give me a break. If you haven't done the research to find an actual
example somebody would agree to as valid usage of dynamic SQL and then
just start coming up with reasons why your sproc wouldn't be better as
dynamic SQL, you aren't interested in actually debating its merits.
You apparently prefer to debate your own version of dynamic SQL which
is easily bested, thus suitably earning its title of straw man. I
never claimed your sproc was suitable for dynamic SQL, and I
certainly won't argue its merits on that inappropriate example.

To my knowledge, you have posted nothing suggesting you have the
experience to classify "every problem that people encounter where
Dynamic SQL appears to be the only or best solution". Have you done
the minimal research I suggested? If you won't do it, then I have
already stated I am not a John Colby, willing to do the research for
you. I hold up my side of the debate; you are responsible for yours.

So, we can agree security is a balance? Thus, saying dynamic SQL means
you must have an unsecured system is not strictly true depending on
where you put the balance. For us, this database is accessed via only
one application which codes the dynamic SQL according to specific user
choices. For us, this is an acceptable security balance for the
performance we get from dynamic SQL, and we consider this to be a
secured system.

Again with the additive. I am starting to wonder whether you are
reading my comments as you simply restate material that everybody
involved: Arthur, you, myself; already knows. Fine, I'll agree not to
object to the additive adjective if in future summaries everyone will
agree to mention DENY as well if only in passing. Please, at the very
least, don't feel required to requote BOL information again. Argh.

Your experience means nothing to me as mine should mean nothing to you.
I don't care if you've never had a use for a linked server, UDF, or
anything else in SQL server. I have given you the terms to search for
exactly because you seem to have had no experience with SQL that
needs to be dynamic. For you to then complain that you haven't seen
any examples is a bit disingenuous. Go look. Or don't. Whatever. Until
then, enjoy beating up your straw man.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com

> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com 
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of Francisco H Tapia
> Sent: Tuesday, June 29, 2004 5:16 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Difference between views and queries
> Francis Harvey wrote On 6/29/2004 10:10 AM:
> >Francisco,
> >
> >I am not going to debate a straw man example that is specifically
> >chosen to highlight the supposed "evils" of dynamic SQL. It was done
> >because there wasn't sufficient time, lack of experience, can be
> >done better with static SQL, etc; are all easy points to win against
> >using dynamic SQL if you were somehow naive enough to believe this
> >actually represented a realistic example of when it should be used.
> >It doesn't, so I won't.
> >
> >  
> >
> every problem that people encounter where Dynamic SQL appears 
> to be the 
> only or best solution is often looked at in this light. If 
> you don't see 
> it, well then I guess there is nothing more to discuss.
> >Security is a different issue, and I am willing to discuss it, but
> >first a question, do you honestly believe that security 
> considerations
> >should outweigh any other consideration? I don't get that impression 
> >since you have allowed the sproc to continue in its present 
> form, so I
> >wonder at your statement that you cannot achieve security using
> >dynamic SQL. Basically, there is a certain level of security I am
> >comfortable with and dynamic SQL does not violate it. I can go into
> >more detail, but I don't get the impression that you would protest
> >this balancing anyway.
> >
> >  
> >
> Security is always a balance of how much data is allowed through w/o 
> comprimising the rest of the system.  Our particular problem w/ our 
> sproc was one because of "TIME" and because our user base currently 
> lacks the knowledge to exploit this has been the determining 
> factor to 
> NOT get this sproc up and running in the correct fashion, It is 
> currently on our list "todo" and will be assimilated (so to speak).
> >I didn't say scaling issues were insane. I implied that most people
> >haven't actually done tests to see how much performance gain you lose
> >by not having a cached query plan, which is not even always the case
> >for dynamic SQL in any event. IOW, you will not always get a better
> >query plan (in fact, for complex dynamic searches, you are virtually
> >guaranteed that a generic static solution would have a very bad
> >query plan) and it will not always have less cost.
> >
> >Again, aren't we all basically in agreement except you both have this
> >addiction to using the word additive to describe what clearly has a
> >non-additive element, DENY permissions? You keep restating what all
> >three of us already know, and then you and Arthur seem to make the
> >leap from not liking to use DENY permissions to implying that only
> >the additive model should be used. Guess what, just like with dynamic
> >SQL, my needs are apparently more complex then yours, and you cannot
> >propose to give a meaningful summary of SQL Server security while
> >ignoring one of its key features. It wasn't added as a lark; it was
> >added to provide additional security options. If you don't want to
> >use it then don't, stop trying to convince me of its benefits. I
> >use what is appropriate, and DENY permissions are appropriate for
> >my needs. Unless you are saying there is some reason you wouldn't
> >use DENY permissions if needed, I can't do any better than the BOL
> >examples on why they are needed. Find a fault with their examples
> >and this might become an interesting discussion. Otherwise, I will
> >repeat that to have a true security summary you must also mention
> >DENY permissions which are not additive in nature.
> >
> >  
> >
> Security is Additive.  BOL states to be explicitly careful 
> when issuing 
> DENY seucurity as it will take precedence over any GRANTs and 
> therefor 
> you will only be able to grant premissions if the user is 
> removed from 
> any ROLES that have DENY rights.  It works this way in NT security as 
> well.   There is nothing diffrent or even remotely obscure in this 
> topology.  You just have to make sure you realize you've 
> issued DENYs.  
> And of course there are situtations where DENYs are 
> necessary.  But I've 
> yet to find a solution where Dynamic SQL was the only out. 
> (or the better).
> -- 
> -Francisco

More information about the dba-SQLServer mailing list