How would you architect a “standard” web application to protect the database?

Re:How would you architect a “standard” web application to p... by xyzzy123 on Wednesday July 22, 2015 @01:52AM (#9927932)

For high-value custom applications, you can go a step further and enforce a stored procedure interface to the database.

Essentially the application is given no permission to write to tables at all, but just to call various stored procedures which enforce access permissions and implement auditing.

This works particularly well if the database is append-only (writing change records instead of mutating, rather like double entry book-keeping).

[ Parent | on HN ]

Re:How would you architect a “standard” web application to p... by bbcbasic on Wednesday July 22, 2015 @07:11PM (#9932975)

Does the database then deal with user management? E.g. someone logs in, gets an authentication token, etc. Or do app users map to DB users?

If the stored procs are enforcing checks, then it isn't good enough to just say 'I am user bbcbasic'. It should ask for an authentication token or password, or against your current DB login.

[ Parent | on HN ]

Re:How would you architect a “standard” web application to p... by xyzzy123 on Wednesday July 22, 2015 @09:19PM (#9933415)

Absolutely correct, yes, the authentication process needs to be handled outside the app.

While it's possible to have a login stored proc, this is not strictly necessary. If you have e.g. an OAuth2 server which is separate from your application, you can use those tokens for authentication with your database.

If the application is completely compromised (e.g. someone has root on server), then a malicious party would be able to act as any logged in user by dumping tokens.

However, what they would not be able to do is arbitrarily dump your database, bypass your auditing, or mess up your application invariants.

[ Parent | on HN ]