[dba-SQLServer] Difference between views and queries

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:

(watch for wrap)

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Francis
Sent: Tuesday, June 29, 2004 10:11 AM
To: 'dba-sqlserver at databaseadvisors.com'
Subject: RE: [dba-SQLServer] Difference between views and queries


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.
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com

More information about the dba-SQLServer mailing list