Shamil Salakhetdinov
shamil at users.mns.ru
Thu Jul 1 15:59:14 CDT 2004
Arthur, Yes, I also "don't want some "power user" to load Excel and f**k with the data!" And in the past SPs were almost the only secure way to defend DB from these power users. But now MS SQL have Application Role feature(see BOL), which IMO allows to write (server side) components in VB/C++/C#/VB.NET, which are as secure as SPs... > Ideally, I don't want any code that touches the database in the FE. Same goal here. Shamil ----- Original Message ----- From: "Arthur Fuller" <artful at rogers.com> To: <dba-sqlserver at databaseadvisors.com> Sent: Thursday, July 01, 2004 4:46 AM Subject: RE: [dba-SQLServer] Difference between views and queries > As usual, Shamil, you make excellenmt points. We are not arguing so much > as complementing each other's position. > > IMO, (and that's all it is), everything the database can do the database > should do. In my ideal SQL app, there would be almost no code in the FE > except the code that decides which sproc to fire and what to pass as > arguments. The more code that exists in the FE, the weaker I think the > app is. Why? Because I don't want some "power user" to load Excel and > f**k with the data! > > So when I build a SQL database, NO ONE (except me and my peers) can > touch a table. Everyone else using the system hits a sproc or view or > UDF. End of story. I, as godlike being, can touch tables. Mere mortals > cannot. Period. No exceptions. > > In practice, this means that I examine the human roles, i.e. job specs, > to see what the various humans in the organization are allowed to do. I > build roles corresponding to job specs. I generate simple sprocs for > each op (DISU = delete/insert/select/update) on each table. Could be > that a single form hits a number of tables, but the hits always result > in some combination of DISU -- there are no other ops. Having built the > single-table DISU ops, it becomes trivial to write a sproc that hits say > 5 tables, inserting into 2, updating 1, and deleting from 2. Nothing to > it, brain-dead simple once you have the atomic pieces in place. > > Ideally, I don't want any code that touches the database in the FE. Why? > Because if we dump FE#1 (let's call it Access) and replace it with FE#2 > (let's call it vb.net), I don't want to rewrite any database-hitting > code. I want to fire sprocs and/or call UDFS and be done with it. So if > some "power user" fires up Excel and creates an ODBC hook to the > database, he can't do anything but select (at most). Can't add, can't > edit, can't insert, can't delete. Substitute POwerBuilder or Delphi or > anything you want... If the only available access is via > sprocs/views/udfs, then I'm confident that you can't trudge in and stomp > all over my data. > > Every new UI inherits the smarts in the sprocs, and nothing needs to be > duplicated except the calls to the sprocs/views/udfs. IMO, this way no > code needs to be copied from one UI to another; you simply fire the > sprocs, passing what they require. If there's a bug in the sproc you fix > the sproc and all apps inherit the fix -- because the logic resides in > precisely one place -- the db, where it belongs! > > Ok, I stated this a little bit fundamentalist. There are exceptions, and > I accept them. > > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Shamil > Salakhetdinov > Sent: Wednesday, June 30, 2004 6:56 PM > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] Difference between views and queries > > > Arthur, > > Just a philosopical opinion/point of view: > > Database-centered programming for nowaday businesses are becoming more > and more dynamic and constantly changing.... The SPs are like a > "chiseled in stone" stuff. And dynamic SQL is an amorphic matter, > flexibly and smoothly adapting to constantly changing businesses and > their business rules. Please take into consideration this fact: > "chiseled in stone" DLL-Hell producing COM interfaces are being > substituted with .NET Framework flexible architecture... IMHO the > similar tendence is starting to appear itself more and more apparently > in data(base) layer programming. Here is why Application Roles (see BOL) > were introduced. Here is why Youkon will support smooth integration with > C#/VB.NET/... extended SPs which in turn will (I guess) work heavily > with dynamic SQL... > > And as far as I see metadata-based data access and data > integrity/business rules verification/support are also becoming more and > more popular - and this is what is needed for the modern small and > middle size businesses: they urge for the broad automation of their > businesses because of economical and other reasons but they don't have > enough resources to finance design and development and then constant > redesign and code rewriting/refactoring... > > And of course I'm not talking about "SPs must die" - as usual solution > is somewhere in between and is a balance of many interests but for sure > dynamic SQL's role isn't that narrow as described in > http://www.sqlservercentral.com/columnists/rmarda/whendynamicsqlisuseful > _printversion.asp - the fact is that most of these samples look obsolete > because they can be rewritten IMO using UDFs, XML (OpenXML) - i.e.using > SPs but without EXEC I think! :) > > Just my pair of roubles, > Sorry in advance if I did miss the subject of this thread, Shamil > > -- > e-mail: shamil at smsconsulting.spb.ru > Web: http://smsconsulting.spb.ru/shamil_s > > ----- Original Message ----- > From: "Arthur Fuller" <artful at rogers.com> > To: <dba-sqlserver at databaseadvisors.com> > Sent: Wednesday, June 30, 2004 6:57 PM > Subject: RE: [dba-SQLServer] Difference between views and queries > > > > Good example, and good reasoning, and I agree that dynamic SQL is > never > > out of the question. What I intended to write is that more often than > > not, dynamic SQL is the lazy way out of thinking carefully about > sprocs. > > Most of the time it is unnecessary and costly in terms of performance > > and front-end code; some of the time, there is no other way. > > > > Arthur > > > > -----Original Message----- > > From: dba-sqlserver-bounces at databaseadvisors.com > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy > > > Pang > > Sent: Wednesday, June 30, 2004 12:57 AM > > To: dba-sqlserver at databaseadvisors.com > > Subject: RE: [dba-SQLServer] Difference between views and queries > > > > > > oh boy, can't wait to get into this discussion :)...there is one > example > > I > > can think of.. the example would be based on the physical storage of > > the sproc in a single db. it is not possible for a sproc to query > > table > in > > another db that the sproc does not reside in. > > > > To illustrate, let's say that there are three copies of the northwind > db > > on > > db server: northwind0, northwind1 and northwind2; all three have exact > > > db schema; > > > > Your goal is to develop a report that counts number of records in the > > products table in each of the northwind databases. sometimes you may > > only want to count records in northwind0 and northwind1 but not > > northwind2; sometimes you may only want northwind2 and northwind1 but > > not northwind0; > > and sometimes you want all three northwind dbs in your count. It is > > possible to create a sp for each of the permutation but if a fourth > > northwind db is introduced to the system, then the code base is > doubled. > > > > The alternate solution would be to build dynamic sql to piece together > > > select statement referencing tables using the [db].[owner].[table] > > format; which tables are pieced together is based on what the user > > selects as criteria for report. Not elegant solution but it is a lot > > less code > to > > maintain. > > > > With that being said, dynamic sql is not as safe for reporting > compared > > to > > sproc. > > > > I think what Francis is trying to get at is that it is not possible > for > > a > > developer to claim that a particular "coding method" is never needed. > > Rather, it is more correct to state that it is not likely that a > > particular "coding method" is ever needed. Difference between the > > words "impossible" > > and "improbable". Similiar to how a judge cannot hand out a verdict > to > > court case before the case is presented no matter obvious it may seem. > > > > Another point of reference on this topic would be look up "Validating > > User Input" in BOL for best practices on this issue. > > > > Billy > > > > > > >From: "Arthur Fuller" <artful at rogers.com> > > >Reply-To: dba-sqlserver at databaseadvisors.com > > >To: <dba-sqlserver at databaseadvisors.com> > > >Subject: RE: [dba-SQLServer] Difference between views and queries > > >Date: Tue, 29 Jun 2004 20:08:54 -0400 > > > > > >All I want is an example that cannot be coded by one or more sprocs. > > > > > >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 10:47 AM > > >To: 'dba-sqlserver at databaseadvisors.com' > > >Subject: RE: [dba-SQLServer] Difference between views and queries > > > > > > > > >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> > > >_______________________________________________ > > >dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com > > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > >http://www.databaseadvisors.com > > > > > >_______________________________________________ > > >dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com > > >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > >http://www.databaseadvisors.com > > > > > > > _________________________________________________________________ > > MSN Premium with Virus Guard and Firewall* from McAfeeR Security : 2 > > months > > FREE* > > > http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU > > =http://hotmail.com/enca&HL=Market_MSNIS_Taglines > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >