4 ms·

If it is a standard web application it probably connects to the DB as one very powerful user that can at least read/modify data in the DB, and may have DDL perm

If it is a standard web application it probably connects to the DB as one very powerful user that can at least read/modify data in the DB, and may have DDL permissions to create tables etc. So not sure how you can protect against this.

andymurd 11y ago
I often use the following pattern: Categorise the app's access to each DB table into one of the following types: read-insert-update-delete, read-insert-update, read-insert, insert-only and read-only. I've never had to worry about delete-only or update-only, but YMMV. For example, the table COUNTRY is probably read-only, whilst ORDER is read-insert-update. Document this stuff and add schema comments to make code reviews easier. Then I have DB roles like dbname_owner, dbname_admin and dbname_app. The owner can create/drop/alter etc, the admin can read and write to all tables but not change the schema, the app user has per-table permissions. Schema updates do require the owner role but I rarely automate them.
- xyzzy123 11y ago
  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).
  - bbcbasic 11y ago
    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.
    - xyzzy123 11y ago
      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.

andymurd 11y ago

I often use the following pattern: Categorise the app's access to each DB table into one of the following types: read-insert-update-delete, read-insert-update, read-insert, insert-only and read-only. I've never had to worry about delete-only or update-only, but YMMV. For example, the table COUNTRY is probably read-only, whilst ORDER is read-insert-update. Document this stuff and add schema comments to make code reviews easier. Then I have DB roles like dbname_owner, dbname_admin and dbname_app. The owner can create/drop/alter etc, the admin can read and write to all tables but not change the schema, the app user has per-table permissions. Schema updates do require the owner role but I rarely automate them.

xyzzy123 11y ago
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).
- bbcbasic 11y ago
  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.
  - xyzzy123 11y ago
    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.