Harden WordPress using database permissions

Here is a small idea that I would like to throw into the world: most web applications use only one database user for most operations (installation, administration, common usage). Couldn’t we harness the database to protect a bit your data?

How to

This is how you could do it:

  • Create one user (called ‘user’) with full privileges on the database
  • Create another user with no privileges (let’s call him ‘read’)
  • Create a copy of wp-config.php that you will name wp-config-admin.php
  • Write the ‘read’ credentials in the wp-config.php and the normal credentials in wp-config-admin.php (don’t forget to use different auth, secure auth, logged in and nonce keys)
  • Create a copy of wp-load.php that you will name wp-load-admin.php
  • Replace in wp-load-admin.php the reference to wp-config.php by wp-config-admin.php
  • Replace in wp-login.php and wp-admin/* the references to wp-load.php by wp-load-admin.php
  • Now, you can use the admin interface, create posts, etc.
  • Grant some permissions to the ‘read’ database user: GRANT SELECT ON `db`.* TO ‘read’; GRANT INSERT, UPDATE ON `db`.`wp_comments` TO ‘read’;

That was a bit of work, but not that hard! So, what did we do here? We created a user for the admin interface with full privileges on the database (create/update posts, change the taxonomy, approve the comments, etc) and another one for the front end interface, with only read privileges on all tables (that bothers me too, but read on).

This means that SQL injections, either in plugins or in WordPress code (out of the admin panel) will be much harder to implement with this setup. Beware of the custom tables for some plugins. Those will require specific permissions. Depending on the plugin, some could be read only for common usage.

Going further

That’s nice, but not enough in my opinion. As I said, the full select permission for the ‘read’ user bothers me. Couldn’t we restrict a bit the permissions on wp_users? Some of the columns are needed, but do we need to access the user_pass column? Also, the “ALL PRIVILEGES” for ‘user’ is a bit too much. Do we really use the “FILE” privilege (out of SQL injections :D)?

Without further ado, here are the SQL commands you should use:

GRANT SELECT, INSERT, UPDATE ON `db`.`wp_comments` TO ‘read’;

GRANT SELECT ON `db`.`wp_commentmeta` TO ‘read’;

GRANT SELECT ON `db`.`wp_links` TO ‘read’;

GRANT SELECT ON `db`.`wp_options` TO ‘read’;

GRANT SELECT ON `db`.`wp_term_taxonomy` TO ‘read’;

GRANT SELECT ON `db`.`wp_usermeta` TO ‘read’;

GRANT SELECT ON `db`.`wp_terms` TO ‘read’;

GRANT SELECT ON `db`.`wp_term_relationships` TO ‘read’;

GRANT SELECT ON `db`.`wp_postmeta` TO ‘read’;

GRANT SELECT ON `db`.`wp_posts` TO ‘read’;

GRANT SELECT (user_activation_key, id, user_login, user_nicename, user_status, user_url, display_name, user_email, user_registered) ON `db`.`wp_users` TO ‘read’;

REVOKE ALL PRIVILEGES ON `db`.* from ‘user’;

GRANT SELECT, INSERT, DELETE, UPDATE, CREATE, DROP, ALTER, INDEX ON `db`.* TO ‘user’;

With these commands, ‘user’ can only manipulate tables. If you’re an evil DBA, you can even revoke the “CREATE, DROP, ALTER” permission after install, and reactivate them only for upgrades or plugin installation. The ‘read’ user has the same permissions as before on wp_comments, has “SELECT” on all tables except the wp_users. For wp_users, we grant “SELECT” on all columns except the user_pass one.

Thanks to this configuration, even a SQL injection in a plugin will not reach the password hashes! We also removed dangerous permissions like “FILE”. I’d like to prevent timing attacks like “SELECT BENCHMARK(5000000,ENCODE(‘MSG’,’by 5 seconds’));” but i did not figure out what is the right syntax for this (I tried variations around: “revoke execute on function benchmark from read”, without result).

Thankfully, WordPress mostly works with this configuration, and I think that a lot of other applications could be protected like this. Imagine: you could grant insert but not select on the credit card table in an e-commerce application, and process transactions with a background task with the right permissions.

Database privileges are indeed a powerful tool to protect your code from SQL injections. They might require some architectural changes, but the profits can be huge for your security.

Advertisements

14 thoughts on “Harden WordPress using database permissions

  1. The last improvement in the article can’t be done, at least in version 3.5.1. User ‘read’ has to be given SELECT permission for all fields on the wp_users table. Someone thought the only way to get the users’ data was to use a “SELECT *” independently of how, from or by whom the data is accessed.

      • I made a very quick and dirty patch for my local copy. I don’t recommend using it as is, though.

        You have to create a constant in wp-config-admin.php:

        define(‘WHATEVER’,true);

        Then, in the file wp-includes/capabilities.php (http://core.trac.wordpress.org/browser/trunk/wp-includes/capabilities.php#L573), substitute lines 573-576 for the following:

        if (defined(‘WHATEVER’) {// If we’re using ‘user’ permissions
        if ( !$user = $wpdb->get_row( $wpdb->prepare( “SELECT * FROM $wpdb->users WHERE $db_field = %s”, $value)))
        return false;
        } else {// If we’re using ‘read’ permissions
        if ( !$user = $wpdb->get_row( $wpdb->prepare( “SELECT user_activation_key, id, user_login, user_nicename, user_status, user_url, display_name, user_email, user_registered FROM $wpdb->users WHERE $db_field = %s”, $value) ) )
        return false;
        }

        (Sorry about the formatting. Hope it’s, at least, partially legible.) In my copy, I also took out from the second SELECT the fields id and user_login. With that done, you get several warnings (in wp-includes/user.php and wp-includes/capabilities.php due to the fact that some of the $user object’s attributes don’t have a value), but, unless I’m missing something (and I probably am), they seem harmless.

        Hope it’s useful as a starting point.

      • The following is not my exact error message because I’m on another computer and can’t copy/paste it, but the error is the same, only the user and the option_value are different (the latter changes with every refresh of the page):

        “WordPress database error: [UPDATE command denied to user ‘Sql214259’@’62.149.141.30’ for table ‘wp_options’] UPDATE `wp_options` SET `option_value` = ‘1348474937’ WHERE `option_name` = ‘_transient_doing_cron'”

    • To avoid this, you have to include the following line in wp-config.php:

      define(‘DISABLE_WP_CRON’,true);

      It is actually good practice to do this if you’re having performance problems.

  2. Thanks for the nice article. It’s rather surprising that the issue in WP and in webapps in general is hardly discussed enough – it’s barely mentioned in the codex’ Hardening_WordPress.
    I think its technical name is Principle of Least Privilege.
    Funny thing is, if you lookup “hardening wordpress grant revoke all privileges” on google, your blog entry is the first one to show up atm.

    Have you tried mention this to WP developers? I guess it could be difficult to implement is shared hosting environments (et encore), where people generally do not have their say over DBA permissions; for self-hosted sites it should be one of several standard lines of defense (lookup: defense in-depth).

    • Using GRANT SELECT ON `db`.`wp_options` TO ‘read’; will impact performance because WordPress will not be able to set transients. In some cases (plugins/themes) transients are used for other reasons. In these cases the site will break. Caching methods must be implemented to mitigate the performance impact.

      • Sure, that will break the transients system. Using the wp_options table to cache data was a stupid decision. Anyway, if you really want some performance, you will not use the default transients, but use a separate store like memcached or redis.

  3. Yes, I should contact the WordPress developers about that. It should not be to hard to integrate in the core code without disturbing shared hosting environments.

    Most systems do not mess with DB user privileges, because developers are used to working with one full access user and handling security in the code. DBAs use them extensively, but then they are not seen as a tool to secure applications.

    So, yeah, even if this trick is not hard, it is not widely used 😦

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s