>> Typically it's just one database, one user (your application server) with full access
This is a terrifying way to access databases.
Super user, A Modify user (just below super but cant delegate rights) for schema changes. A read/write app user... Probably a pile of read only users who, have audit trails... You might want some admins or analytics users (who have their own scheme additions).
The words security and audit trails all spring to mind.
A simpler solution is to simply not give direct access to the database to anyone who doesn't own a large stake in the project. Expose it via a more restrictive CRUD interface with access control in the application layer.
This is a terrifying way to access databases.
Super user, A Modify user (just below super but cant delegate rights) for schema changes. A read/write app user... Probably a pile of read only users who, have audit trails... You might want some admins or analytics users (who have their own scheme additions).
The words security and audit trails all spring to mind.