5 ms·

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 W

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.

krallja 9y ago
Only give the database user permission to execute stored procedures.
- sova 9y ago
  Function level protection sounds reasonable
- technion 9y ago
  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.loudthinking.com/arc/000516.html http://web.archive.org/web/20060418215514/http://www.loudthi...
  - pzxc 9y ago
    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.
kelnos 9y ago
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.
lowbloodsugar 9y ago
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.

krallja 9y ago

Only give the database user permission to execute stored procedures.

sova 9y ago
Function level protection sounds reasonable
technion 9y ago
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.loudthinking.com/arc/000516.html http://web.archive.org/web/20060418215514/http://www.loudthi...
- pzxc 9y ago
  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.

kelnos 9y ago

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.

lowbloodsugar 9y ago

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.