Arthur Fuller
artful at rogers.com
Tue Jun 29 19:22:05 CDT 2004
I must have missed the post including your links to said examples. Please re-post them. Arthur -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francis Harvey Sent: Tuesday, June 29, 2004 6:22 PM To: 'dba-sqlserver at databaseadvisors.com' Subject: RE: [dba-SQLServer] Difference between views and queries Francisco, 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 <snip> _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com