Francisco H Tapia
my.lists at verizon.net
Mon Jun 28 11:41:59 CDT 2004
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 > > > > >>-----Original Message----- >>From: dba-sqlserver-bounces at databaseadvisors.com >>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf >>Of Arthur Fuller >>Sent: Friday, June 25, 2004 4:23 PM >>To: dba-sqlserver at databaseadvisors.com >>Subject: RE: [dba-SQLServer] Difference between views and queries >> >> >>1. I haven't yet seen a case where dynamic SQL is necessary. All it >>takes to avoid it is one or more well-constructed sprocs, IMO. >> >>2. This is a good thing. But the point I was making is that >>you set up a >>hierarchy of permissions by role, and then add roles to roles. This >>works very well. >> >>-----Original Message----- >>From: dba-sqlserver-bounces at databaseadvisors.com >>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf >>Of Francis >>Harvey >>Sent: Thursday, June 24, 2004 4:28 PM >>To: 'dba-sqlserver at databaseadvisors.com' >>Subject: RE: [dba-SQLServer] Difference between views and queries >> >> >>Arthur, >> >>Two points: >> >>1) Never say no exceptions. Currently, I need dynamic SQL, >>and there is no comparable work around without it. >> >>2) Permissions are not additive in that denied permissions always take >>precedence. You cannot gain rights to an object by adding adding >>additional roles if you have been denied rights at some level. >> >>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 Arthur Fuller >>>Sent: Thursday, June 24, 2004 4:05 PM >>>To: dba-sqlserver at databaseadvisors.com >>>Subject: RE: [dba-SQLServer] Difference between views and queries >>> >>> >>> >><snip> >> >> >>>NO users except sa (and possibly developers) should have >>> >>> >>access to any >> >> >> >>>SQL table. Everything should be done with views or sprocs >>> >>> >>or UDFs. No >> >> >>>exceptions. >>> >>>Access to said objects should be governed by roles, and users >>>should be >>>assigned to roles; this can be done additively. I.e. suppose >>>you have 3 >>>levels of access, a, b and c. Everyone in level B can do >>>everything that >>>everyone in level A can. So just role B as a user in level >>> >>> >>A; then you >> >> >>>"inherit" everything permitted for level A. Similarly, add >>> >>> >>role C as a >> >> >>>user in level B, and inherit both B and A. This is a >>>simplistic example; >>>it may arise in the real world that level C should be able to do >>>anything A can but nothing that B can. In that case it's a >>> >>> >>little more >> >> >>>difficult, but the underlying principle is the same. IMO, as >>>always, and >>>I could be wrong, and it wouldn't be the first time. >>> >>>Arthur >>> >>> >><snip> >> >> ><snip> >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > > > -- -Francisco