Francisco H Tapia
my.lists at verizon.net
Tue Jun 29 16:16:10 CDT 2004
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