Defence in Depth: Separate database operations by access level

[discobot]

Background

ClassicPress has inherited from WordPress a codebase entirely devoid of even the concept of “defence in depth”.

The purpose of this petition is two-fold:

1. Gain wider acknowledgment and acceptance of the need for defence in depth,
2. Agree on a concrete first step in the process of providing defence in depth.

Creating full defence in depth properly is going to be a long, slow process, but I believe there are very worthwhile quick wins available.

There are many facets of defence in depth; I shall confine this petition to authorisation [1].

Why?

Over the years there have been many bugs in WordPress that have allowed some variation on “an unauthorised user being allowed to access something they shouldn’t”. The most recent is the REST API bug in 4.7.0/1 which allowed an unauthenticated user to insert/update/delete any post. This proposal would have completely prevented the bug from becoming a security issue.

The Problem

The fundamental issue is that CP has a single database connection which, by default, can do anything; if the authorisation checks in the code fail for any reason there is no way to catch it before the damage is done.

The Solution

The only solution is some form of separation of concerns.

The ideal separation is to have multiple database connections, each with a different level of access; ideal from a security perspective, but impractical.

Instead, I propose the addition of a number of database roles to the existing user, with the default role having only read access.

Roles, in one form or another, are common to all databases (with the exception of sqlite). Their exact functionality differs, but generally MySQL is the least capable; whatever works for MySQL is almost guaranteed to be possible on any other database we might consider supporting.

Each role would have a different level of access, but how the roles are structured requires further discussion. For example, we could mirror the CP user roles - which is probably too coarse, or we could mirror the low-level user rights - which is probably too fine and definitely too hard to implement in the first version [2]. We need a compromise and that requires discussion.

Due to the rather “basic” nature of the existing database schema the effectiveness of this approach is slightly blunted; for example, having all the posts and CPTs in a single table makes it difficult to restrict access by type [2], and unpicking this is not something to be undertaken lightly. However, it would still have stopped the REST API bug from becoming a security issue, and would generally prevent SQL injection attacks.

Future Implications

It is vital that any future developments take the security implications of the database schema into account, rather than just simplicity or conformity. For example, the “object relationship” research plugin must use individual tables for relationships so that there is enough granularity for database roles to be effective.

Ultimately, as part of implementing PDO support (which I am very much in favour of and hope we agree to do), we should look at implementing table-per-post-type, amongst other things. However, given the potential for breakage I cannot see how we could attempt that before v3.

---

[1] Distinct from authentication.
[2] Needing to use row-level permissions almost always means the schema is wrong.

---

Read-only archive: Issues · ClassicPress/ClassicPress · GitHub

Author: invisnet

Vote count: 11

Status: open

Tags:

• cp-research-plugin

---

Comments

I agree this would prevent entire classes of issues that have caused major problems with WP in the past. I’ve edited the title of this petition to be more specific.

This is possible today using a specialized database setup (multiple users) and a $wpdb replacement drop-in (e.g. HyperDB – WordPress plugin | WordPress.org)

---

Now let’s talk about making this feature more widely available.

Given a single MySQL username, password, and database, how do I make multiple connections with different permissions (to start, one that has full access and one that has read-only access)?

The standard way I know to do this is to use the built-in permissions scheme offered by the database (multiple sets of connection information, each one connecting as a separate database user with different permissions). This does not seem easy to implement:

• Existing sites (and most hosting providers) will only have one database user available.
• Currently ClassicPress has no concept whatsoever of how database permissions work, and this would need to be introduced.
• ClassicPress can attempt to create and store connection details for a read-only user itself, but correctly configured hosting setups will not allow the connecting user to do this.

Is there something I am missing here?

~ posted by James Nylen

Is there something I am missing here?

Yes.

This is not multiple connections - I explicitly said that’s impractical - this is multiple roles for a single user and a single connection.

When a user is granted multiple roles they can select the role they need for the operation they’re about to perform. Without specifying a role the default applies - read-only.

With the exception of comments, an unauthenticated user should not be able to write to the database at all.
When authenticated, as I said above, there needs to be some mapping between the logged-in user and the database role (if I could edit the post I’d sort out the formatting and make that a little clearer) - exactly how we do that mapping needs discussion.

~ posted by invisnet

OK, I see now. I have not worked with database roles before. Here are some links for the curious:

• http://www.mysqltutorial.org/mysql-roles/
• https://dev.mysql.com/doc/refman/8.0/en/create-role.html

Instead, I propose the addition of a number of database roles to the existing user, with the default role having only read access.

This raises similar questions, I think. Which database users have permission to create these roles, and how can we ensure that they are working properly?

In the case of an SQL injection attack, can’t the malicious query just reset its own role and defeat this protection?

I almost think it would be better (and overall easier) to add built-in support for at least two different database users: one read-only and one read-write. The read-only connection would be prompted and explained during the installation process, and used at runtime if it is correctly defined in the config file.

~ posted by James Nylen

Which database users have permission to create these roles, and how can we ensure that they are working properly?

By default the existing database user can manage its own roles, and we’d start with 2 roles and iterate from there.

can’t the malicious query just reset its own role and defeat this protection?

In theory, yes, but we can add code to make it harder.

…two different database users: one read-only and one read-write

In an ideal world, yes, but I think it’s impractical: we’d need support from hosting companies for the extra user(s), a way of updating existing installs, we’d still need the roles (we can’t have a db user per CP user), and we’d need to deal with switching either $wpdb or the db connection itself - neither sound like fun options.

There is no perfect solution from where we’re starting - we can’t take away all direct access to the tables and force everything through stored procedures - so it’s always going to be a compromise.

This is the least invasive option, it can be easily retrofitted to existing installs, and it doesn’t preclude extra database users later.

~ posted by invisnet

OK, thanks for adding some details here. I think it would be good to see a basic version of this working as a plugin, and as per our standard approach here we can create it on ClassicPress Research · GitHub.

~ posted by James Nylen

I did a POC a couple years ago that extended WPDB and switched the active user on the mysql connection with mysqli_change_user upon certain successful nonce validation and a few authentication related events. There is probably a need for some degrees of enforcement, and change user interacts with transactions, but overall it is quite approachable.

~ posted by Will West

[viktor]

This is not feasible for ClassicPress at this time. It may be revisited in the future.