Comment by technion | Hacker News Reader

Comment by technion | Hacker News Reader

Parent Full thread

I feel there's a couple of places where this doesn't reflect reality.

The first very likely scenario is given business logic says a web server application needs certain data, where "certain" is "pretty much all of it". So your application server in that DMZ has a database credential it needfully holds.

Which in turn is a credential an attacker can use to access "pretty much everything".

    single person at a time

The majority of modern web applications will open a series of database connections. One application server limited to one DB connection for a public application is probably going to break the app.

Overall, security people don't get the choice about "bowing" to business people in most of my experiences. I'm not saying scape goating never happens, but this

    then just be clear that you are getting fired when the shit hits the fan.

What do you think the alternative is? Tell them you're quitting early on because they won't do what you say?

I can present risks, and offer mitigations. More often than not, some get through easily, some never will, and it's those on the fence you work on constantly improving.

Heh. I don't mean the database can do only one query at a time. I mean the db is set up so the front end can't just hit it with a SQL query to dump the database.

Now, sure, if the front ends query 10% of all records every day, then yeah, it only takes 10 days to get all the data, one query at a time, without triggering any alarms. They had 75 days. I don't know though, on any given day, what percentage of the population are getting credit checked? And did they have any alarms?

Thanks for that clarification, it does make more sense. I think there are still practical limits to that. If your application can do:

    SELECT * FROM users WHERE ID = 10

Why not

    SELECT * FROM users where ID > 0

Honestly this is something I'd like to be able to put in place in certain areas, but I'm not aware of a sane strategy to do so existing.

Only give the database user permission to execute stored procedures.

Function level protection sounds reasonable

To be clear, I don't disagree.

But the development world has been turning on stored procedures for a while. You've got popular frameworks actively shunning the concept[0].

I've seen the topic on HN before and it's generally been the view that there is rarely a use case in modern times. We can't suddenly say everyone is expected to have everything in a stored procedure now that it's convenient.

[0] http://web.archive.org/web/20060418215514/http://www.loudthi...

True, enforcing stored-procedures-only is an antiquated methodology that is frowned upon these days. But not because it doesn't work -- rather because it's a bad idea to put your business logic in the database itself, which is what usually happens.

The more modern equivalent, is to just have an "app server" that sits in between the web (DMZ) and db tiers. The app server can basically be just another web server that provides an API for the actual web server to use. So from the web server's point of view, it still has restricted access, but you get to write all the rules in your normal backend architecture/language rather than building them with stored procedures and triggers and all that.

Besides, the "rarely a use case" isn't the same as "never", and if you can envision any scenario where it would be worthwhile to go to extraordinary means to ensure security, I think "we have all the data needed to commit identity theft on half of all Americans" counts. If stored procedures is a convenient way for you to limit the impact of a breach from your web server/DMZ, then do it if you have data that important. Do 20 other things that are inconvenient too if it gives you defense in depth.

You don't even allow that. I'll use REST as an example because that's what I use, but any form of RPC would work.

App server has no database access. No DB credentials, and no network access to the DB. It does, however, have access to a service that sits in front of the DB. It can ask the service:

    GET /users/10

... and that's it. It gets one record. The service fronting the DB has a single query it can execute:

    SELECT * FROM users WHERE ID = ?

Sure, if someone can compromise the user-data service, then they have more access. But that's what defense-in-depth is: if an attacker compromises one system of yours, they don't get the crown jewels: they need to keep going, and each new attack they need to mount costs them more, and sometimes even makes them fail.

We use stored procedures. We actually use them for a different reason: we treat the database server as an application server that happens to use an proprietary binary protocol instead of HTTP. Its API is exposed using stored procedures. This allows us to maintain and update the database data model independently of the "traditional" web tier. DB experts can work on the most effective data model without hunting through client source code.

It also means that we can use access controls on the API (the stored procedures).

In my experience, allowing anyone to hit a database with SQL leads to poor code quality, bugs and performance problems. And security issues.

> The majority of modern web applications will open a series of database connections.

And this is exactly where the problem comes from. A web application should never be allowed to talk directly to the database.

Reply on news.ycombinator.com