3 ms·
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
by 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 agoDoes 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 agoAbsolutely 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.