Jim Lawrence (AccessD)
accessd at shaw.ca
Tue Jun 29 21:59:52 CDT 2004
Hi All: For those that are interested, here is a series of modules, from M$, on SQL security. They might not always be right, but you can be reliable assured they understand the security issues as related to their SQL product and best methods to handle same: http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/p art3/c1061.mspx (watch for wrap) HTH Jim -----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 10:11 AM To: 'dba-sqlserver at databaseadvisors.com' Subject: RE: [dba-SQLServer] Difference between views and queries 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. 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. 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. 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 11:49 AM > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] Difference between views and queries > > > 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. <snip> _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com