Hi Frans,
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". 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.
exactly. And not only that: if a hacker is able to delete one customer
it's already not safe. If the customerID is numeric, like an int, he
might have to write a loop to delete all of them, but that's a matter
of time.
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. 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.
People tend to believe that as long as it is a bit safe, they're at
least a bit more secure, but that's not true. If a hacker is after your
data, he'll get it, as soon as there's one tiny hole he can peek
through.
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.
Though, the vast majority of 'security' related concerns aren't
targeting hackers from the outside, but towards the 'manager' with ms
access who wants to run ad-hoc reports on your 3TB machine. In that
scenario, you can perfectly limit access with normal db-role based
security on tables and a couple of logins instead of one general login
which can do everything.
I do agree with this, although I would say that managers, end users, etc
shouldn't have any access to the database, but should be working through the
application so both security and business rules can be applied. Obviously if
they're developing something like crystal reports, than the security will
need to be at the database level, in which case your right. Stored procs
aren't really helping security much at all, since the developer needs access
to certain tables or views.
Instead of working together, they work against eachother, which is
weird, as they all work for the same company. It's not new though. A
lot of developers have to argue with system administrators a lot,
because the system administrator doesn't want to take a new app into
production if it isn't tested for a month on some obscure test server.
It's a bit of a weird thing: a team is asked to develop an n-tier
application, and they are free to decide what they use in the BL and PL
tier, but the datalayer is written by a person not part of the team,
and at the same time, has a very big say in the rest of the design of
the application.
Because, if you use procedures, it's harder to use objects in the BL
and PL tier. It's not hard to fill a set of customer entities with the
result of a select proc call. It's MUCH harder to fetch a graph of
entities efficiently with a set of procs (as in: impossible without
writing for each occasion a new proc). By opting for procedures, you
decide to move the dataset route, which is perhaps not what the team
wants to use in their app, and THEY write the application.
Instead of using the DBA's knowledge (so add the DBA as a db
consultant to the team, so the DBA knows which tables are targeted
often, and because the DBA knows which tables are large, he/she can
advice on using subqueries instead of joins or vice versa, through the
usage of the abstract DAL), the DBA and the developers aren't working
together, which is counter productive.
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.
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).
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.
Also, I knew another good developer who wrote all his tables, then all his
CRUD stuff in stored procedures, then all his business objects, then all his
UI. This led to a bit of wasted effort because things often change during
development. Many of his stored procs and business objects didn't ever get
used, or had to be rewritten because priorities changed, new requirements
were discovered and better ways of writing these layers are found while
developing the app.
So I build one "module" at a time, from top to bottom.
"But it is more secuuuuuureee!!!"
no. An n-tier application has numerous areas where a hacker can inject
code, alter data during the dataflow etc. And most hackers who look for
databases aren't interested in deleting data, they're interested in
fetching data. So the obvious place to stick a listener component is
outside the db, right below the PL.
I agree with this too. Very astute observation Frans.
Fregas
