The application uses a single database account to perform all actions. Users
are just some records in a table. Account privileges and roles are part of
this table, or separate tables.

This implies that all security must be designed and build by the application
developer. I think this is entirely wrong. There is a big risk:

In such applications, SQL-injection will allow full control of the entire
database.

This is something that is often overlooked. And the solution is simple. The
application should not use a general account with full privileges. The
application should use the database account of the user accessing the
application. All actions performed by this user are thus limited by the
privileges of this database account. The impact of SQL-injection would be
significantly reduced.

The public part of a website is still using an application account, but the
privileges of this account can be significantly reduced. To obtain elevated
privileges, a user must first authenticate against the application and thus
the database.

Please understand another benefit: it is not required to store
username/password combinations of privileged accounts on the application
server. The configuration file will only contain the credentials of the
unprivileged account. An attacker compromising the application server with
limited privileges, won't have access to the database with elevated
privileges.

I understand that this solution requires a bit more work to setup at the
start, but once implemented, it reduces complexity and improves security so
much.

Of course, the security of your data is as good as the hardening of your
database server. But that's another story.