How to appropriately secure a database using different db users

I was thinking recently of the steps to secure a database server. Most of us are aware how to handle the security on the application side, although a malicious user might find his way to bypass the application security and exploit the database.

Scenario

Consider having a database with the following table prefixes blog_ and website_ (for examples and simplicity sake, K.I.S.S.)

I am thinking of something along the lines of the following, but am unsure which is appropriate and which is an overkill, hence the purpose of the question.

Solution 1: One writes, many read

• Have only 1 user in the application with write access on the database. On all tables


• Have more than one user with read access on to the database, but separated on a per table set(for a specific prefix) basis. Meaning that each RO db user respectively read access only to their set of tables.

global_write@localhost: writes to all tables in the database

blog_read@localhost: Reads from tables with prefix blog_

website_read@localhost: Reads from tables with prefix website_

Solution 2: One read and one write on a per table basis

• Have a pair of db users per application component, one to read and one to write

blog_read@localhost: Reads from tables with prefix blog_

blog_write@localhost: Writes to tables with prefix blog_

website_read@localhost: Reads from tables with prefix website_

website_write@localhost: Writes to tables with prefix website_

Solution 3: Only one pair of accounts is sufficient

• Only one pair of accounts is responsible for the entire database

global_read@localhost: Reads from all tables

global_write@localhost: Writes to all tables

---

TL;DR

Solution 1: One writes, many read

Solution 2: One read and one write on a per table basis

Solution 3: Only one pair of accounts is sufficient

1. Are all of these appropriate?


2. Which ones are an overhead?

PS: Sorry if I did not tag appropriately with access-control