Fregas said:
I respectfully disagree. Most things in this world, including
technology are a matter of degrees. I think most security analysts
understand that they can't make any system "unhackable" but its a
matter of balancing how much effort and inconvenience they put in
versus the pay off of being more secure. I don't think its a simple
matter of "Stored procedures are secure, dynamic sql is not".
Sadly, for most pro/con discussions it IS a matter of procs -> good,
dyn. sql -> bad, and security, and what not, is dragged into this. Of
course it's a matter of balance, but I've seen more than one person in
this thread claim procedures are more secure and dyn. is not, without
giving any example how to achieve that.
Using generated crud procs or handwritten crudprocs is NOT secure, not
even MORE secure than dyn. sql. Furthermore, with the options you have
in modern databases, you can make your db as secure as you want, even
with dyn. sql. The theoretic options you have with procs, where you
offer a small set of procs which have a fine-grained set of checks to
see if the caller is actually legit, are nice but also limit the
actually app's structure on top of it. And besides that: is that level
of security also in place in teh BL tier?
I depends on a lot of factors. In whole, I think you have more
security options with stored procedures, but that certainly doesn't
make your database "completely secure" whatever that is. You're
looking at it as a black and white question, and few things fit into
neat categories like that.
true, I placed it as a black/white situation, though that's how the
pro-proc claims were made in this thread, without any example, proof of
concept etc.
The pro-proc people also forget why procedures are often chosen: DBA's
who write the procs are way more important for the organization: THEY
call the shots, not vice versa. DB vendors also want people to write
procedures instead of dyn. sql: it will tie an app to the db, and makes
it very hard (== a high price) to port it to a competitor's db, and
therefore promote procs as THE way to develop data-access code. But
data-access code doesn't live alone, it's part of an application.
And that extra time might cause the hacker to get noticed by
intrusion detection, get logged off because the network is only open
for a specific window of time, etc. If the key is a guid or
something not easily "loopable" than its going to be even harder.
Don't rely on it, if your data is worth something.
Many of the recommendations microsoft gives in locking IIS for
example, simply cause the hacker to have to put in a lot more work to
do the same damage, in the hope that the hacker will get caught or
reach the extent of their knowledge and give up.
Well, I think we've to make some distinction here. Let's call it
unauthorized access to the data. In that category fall:
managers/information workers with access who run adhoc reports on large
databases, bring it to its knees; hackers who want to delete data for
fun; hackers who want to steal your data for money.
The last category is the most fatal for you and also doesn't stop when
a hurdle is popping up. Also, how would you stop these people with a
set of crud procs?
Again, I disagree. For example, I have a Belkin router. That router
has firewall protection. It has basic security. Am I completely
safe? No I'm not. Am I as safe as if I had a professional come in
and install a Checkpoint firewall? Not nearly. Am I more safe than
if I had no firewall protection at all. Definitely.
Not a good comparison. A firewall is supposed to be lock tight. A
stored proc api is open by definition: it offers a set of functionality
to get to the data/act on the data. Now, to be able to do something
with the data, you need procs which do that something for you, correct?
so these procs are there. And thus the hacker has a nice proc to get
things done.
10 to 1 the app the hacker used to get access, for example a leaky
website, has access to that proc, otherwise, why add that proc if the
website/app can't use it?
I'm fortunate enough to have not experienced that, although I've
heard it from others. I've only worked in two places that had a DBA,
and in both of those places it was a pleasant relationship. We
created our own queries, procedures, views, etc. and the db helped
maintain indexes, gave us tips, did backups, etc. They stayed out of
our way, we stayed out of theirs and for the most part helped each
other when needed. My current DBA is great and my managers
understand the developers need to create data structures rather than
have them dictated to them.
That's a great example how it should be
.
This is a bit of topic, but I have to say I dislike the whole idea of
a team project where certain members are working on one tier like the
database or the business objects, and other people working on other
tiers like the presentation. I'd rather break people up into
developing certain areas of the app and making them work from top to
bottom. Although its good for a designer to create images and chop
of the Html, you really need developers to consume objects and create
the UI, and thats easier if that developer has also created his
businss objects and database structures (whether just tables, views
or stored procs), because he's working with his own API(s).
That won't work in large applications. The disadvantage of having your
team work on all tiers is that when the times get tough, a developer
can decide to create a shortcut in the application, which will work at
one time, but fall apart later on. When you've a team per tier, the GUI
team for example can't do anything with the db, they have to call the
BL api methods, and the BL team just focusses on writing the business
logic, not GUI oriented stuff. So you get cleaner code: the GUI code
isn't polluted with stuff that can hurt its scalability, and the BL
code isn't polluted with stuff related to the gui which can hurt its
scalability as well, also, the GUI doesn't make shortcuts to the DB but
has to call the BL tier.
I've seen the "build one tier at a time" approach to development fail
many times, even with only one or two developers. We had a guy at my
current job get fired because he felll so far behind. He created so
many objects for things and test scripts and so forth but never built
his UI. He was saving that for last. The problem was that the "test
scripts" didn't accurately test his API very well and so he had a lot
of code but no screens to show for it and no real way of knowing if
his API worked. I much prefer to build one piece of the app, usually
one or two screens at a time: design an object model, build my
tables, build my data access code, build my objects, integrate them
into the UI. This way I have something to show and I know that this
piece of functionality really works.
being fired because you wrote the UI last is very weird, then
management definitely didn't know what's important. Pretty pictures are
nice to show to customers, but it's the logic behind it that really
counts. I'd rather have a raw ui with great BL that works, than a shiny
UI with rushed BL that falls apart if I push too hard.
So I build one "module" at a time, from top to bottom.
With smaller projects (where 'smaller' is subjective, so don't take
this wrong, it's not meant negative) it's not always needed to have a
team per tier, but it's IMHO very important to have specs per tier and
code the specs separately as if there is no other tier. Not only do you
then learn if the tiers are well separated, you'll also keep away from
the pitfall where you write code in the GUI which actually had to be in
the BL and vice versa.
FB
--
