How to appropriately secure a database using different db users

Question

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

Answer 1

Separating by Tables

Separating by tables seems like a good idea, especially if you can estimate the importance of tables to your security. For example a table with usernames and passwords is pretty important, while a table with blog comments is less important. If you have a different database user for those important tables, an injection into eg a query that selects comments might not gain an attacker any real advantage as they cannot access relevant data (the most they could do is reflected XSS).

Separating the blog and website also looks reasonable (at least from a system design point of view), but I would give each of them a separate database (they don't really seem to belong to each other, so it makes logical sense, and would also be easier to handle).

Separating by Read/Write

I'm not sure that separating by write and read would be worth it (what would be the actual thread it defends against? I think mainly an attacker injecting a blog post), and it will probably be hard to enforce. For example if you have an admin user which can edit the database via a web interface:

• attacker can read: attacker can read out admin password, and thus write (via web interface)
• attacker can write: attacker can add admin password, and thus read (via web interface)

Managing the privileges by table prefix also does not seem like a good idea. Consider this (this might be a bit far-fetched, but it's just one example of how to bypass your rules):

• website_read has select rights on website_*
• global_write has write rights on website_* and blog_*
• blog_read has select rights on blog_*
• attacker has access to global_write and blog_read (eg via injection) and wants to read out website_users (to which they don't have the rights)
• attacker renames table website_users to blog_users with global_write [*], and reads it out with blog_read.

_{[*] If this is actually allowed would depend on your concrete system, but generally rename is a write activity. Eg MySQL requires ALTER, DROP, CREATE, and INSERT which I would call write rights.}

Answer 2

You have to distinguish between the account used to connect to the database and the original user's account:

• the service account used to connect the application to the database should be used to constrain access to those schemas and tables that are to be accessed. The service account should focus on security aspects such as preventing DROP or CREATE TABLE - those types of SQL actions that are not typically required in a day-to-day operation
• the original user's account (or identity): it is impractical to create an account in the database for each and every user that would potentially use the database. Yet you still want to control access to the data (mind you, access to the data itself, not the database per se). At the level of the user, we may also care about controlling which specific cells / rows / tables a user can do CRUD actions on.

Let's take your blog example. We have a table called blog_post. You want to implement the following authorization logic:

• all authenticated users can view any blog post.
• a user with the role approve can change the status of a blog post from draft

There are several ways you could achieve that:

• configure fine-grained authorization using your database tools e.g.
  • Oracle VPD (Virtual Private Database)
  • IBM DB2 RCAC (Row and column access control)
• configure fine-grained authorization using a proxy in front of your databases
  • IBM Guardium
  • Axiomatics Data Access Filter MD (ADAF MD)

What this lets you do is

1. decouple the authorization from the database itself. Rather than having to create artificial tables, roles, and user accounts, you can express fine-grained logic using the tools available in VPD, DB2, or Axiomatics.
2. Implement attribute-based access control (as defined by NIST) and data-centric security.

The proxy approach also enables full externalization of the access control logic.

Some of the benefits include:

• non-intrusive approach: you do not need to change anything in your database. That will definitely make your sysdba happy
• reusable for other tiers e.g. the application tier or even the presentation tier
• easier to maintain and audit. ABAC uses attributes (key-value pairs e.g. role==manager) and policies.

HTH