Hacker News Reader

Comment by lowbloodsugar | Hacker News Reader

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?

View on HN

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.

Reply on news.ycombinator.com