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.
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).
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.
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.
So not sure how you can protect against this.