Francisco H Tapia
my.lists at verizon.net
Tue Jun 29 10:49:03 CDT 2004
Francis, In my current project, there is "ONE" sproc that is still dynamic sql. This sproc was developed in the early stages of the desgin almost 2 years ago, and it was never upgraded to a full static sql because of lack of time. In fact when revisiting it, it is actually fairly straight forward to fix the problematic dynamic SQL and once again remove "SELECT" access from the 3 tables it points to. In fact it was written as dynamic sql because of lack of time. While I did take extra steps to help validate and protect against sql injections, this particular sproc is 'wrong', had we had more time and experiance at the time, it would have been done as static sql the first time. In fact all new sprocs are evaluated repeatedly if Dynamic Sql appears to be the "only" choice. Unlike normalization, if your database stresses to be secure, then you will never acheive this buy allowing dynamic SQL to be passed through in such fashions. The scaling issues are not insane. You will have a better query plan by having stored static sql and will be able to provide the data that much faster w/ less cost to the server. additionally the time spent logic is not bad logic, because if you can do it either way, why "WOULDNT" you do it as static sql? Lastly, Security. Security when working in a Network environment is always done as additive. You join the Service Group, the HR Group, the Accounting Group. so on and so forth. IF by chance the sysadmin locks out a particular access at any level in those groups, well anyone in that DENY group will never "GAIN" access to the respective data store. This is the fashion in which SQL Server security works. SURE you can do it any way you want. But addtive is the streamline way to go. IF I do not grant you access, you can not select/insert/update/delete. If I GRANT you access to SELECT, then you will be able to do so. HOWEVER, Denying you access to read from the table will never GIVE you access again via that login, which is why ONE should be extremly careful of how you give and reject access. Francis Harvey wrote On 6/29/2004 7:47 AM: >Francisco, > >I would like to consider the opinion of someone who shares my namesake >as seriously as possible, but in this case, I believe you suffer from >Arthur's problem of a lack of experience with the very specific >problems I cited. It is fine to talk about not using dynamic SQL >in general when using SQL Server, but just as with normalization, >there are exceptions and no one should be stating it as an absolute >rule or bad practice until they have reviewed the problems I >mentioned. Again, I will state, for those particular problems, >dynamic SQL is often the superior solution. > >Also, the "scaling" issue is teetering awfully close to using FUD. >You can always use any programming method incorrectly, but >correctly written dynamic SQL does not suffer from the horrible >efficiency loss that not having a cached query plan would seem to >imply. Similarly, the "time spent" argument is bad logic. I don't >spend less time on my dynamic SQL solutions, I spend more time >because the problems are difficult. Adding more time for a static SQL >solution will not result in a better design or even a working design >in some cases. > >I don't think you, Arthur or I are in disagreement over security, I >just didn't like the oversimplified way security was described, so I >chose to emphasize a particular point. I think this is an important >part that should be highlighted. > >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: Monday, June 28, 2004 12:42 PM >>To: dba-sqlserver at databaseadvisors.com >>Subject: Re: [dba-SQLServer] Difference between views and queries >> >> >>Francis, >> I respectfully agree w/ Arthur on this. Dynamic SQL as a rule, is >>bad design. Many times over you will find that there are comparable >>solutions that often meet the demand much better, and "scale" much >>better than that of dynamic sql. There is no "ONE" right way >>of doing >>things, that is the nature of this business. However >>justifying dynamic >>sql because of some time spent on design is imnsho incorrect. >> You will >>often find more times that enabling users/roles via Views/Sprocs and >>Functions to be far superior solutions than to open wide dynamic sql. >> >>re: security. >>Of course if you deny permissions on any object the take >>precedence over >>allows, this is the nature of security. That is why you must be >>explicitly "SURE" when making DENY requests on any object. >>NOT giving >>rights to an object does not equeal REJECT. >> >>If I do not give a user rights to a view, under Role1, but >>then give him >>rights to that object via Role2. There will be 0 issues w/ >>accessability. However If by poor planning I had a "REJECT" >>rights in >>Role1, then I'd have the issues you are stating. >> >> >> >> >>Francis Harvey wrote On 6/25/2004 2:24 PM: >> >> >> >>>Arthur, >>> >>>Do some research on the basic solutions for SQL that has to >>> >>> >>adjust for >> >> >>>varying databases, conditions (dynamic search), or servers (different >>>SQL dialects). Some designs also call for encapsulating >>> >>> >>business logic >> >> >>>outside of the data tier. In these situations, dynamic SQL is often >>>the only logical choice. I have only had to deal with dynamic search >>>myself, but this is enough to convince me of its usefulness. >>> >>>Describing permissions solely as additive in nature is >>> >>> >>incorrect. This >> >> >>>is the point I was making. >>> >>>Francis R Harvey III >>>WB 303, (301)294-3952 >>>harveyf1 at westat.com >>> >>> ><snip> > > > > -- -Francisco