MySQL Database User: Which Privileges are needed?

1 Views

The short installation instruction for WordPress (“5 Minutes”) state that:

Create a database for WordPress on your web server, as well as a MySQL user who has all privileges for accessing and modifying it.

Read More

While setting up a new blog professionally I was wondering how that maps to what the MySQL database user privileges/permissions configuration offers me:

  • Data: SELECT, INSERT, UPDATE, DELETE
  • Definition: CREATE, ALTER, DROP
  • Extra: INDEX
  • More:
    1. LOCK TABLES
    2. REFERENCES
    3. CREATE TEMPORARY TABLES
    4. CREATE VIEW
    5. SHOW VIEW
    6. CREATE ROUTINE
    7. EXECUTE
    8. ALTER ROUTINE

I’m pretty sure for the first three groups, I named them Data, Definition and Extra here. But what about the others below the More entry? Normally I would say, those are not needed, but I would like to get a second opinion.

Post Views: 1

Related posts

Leave a Reply

5 comments

  1. The others are not needed as you point out.

    Btw, what you could do is, conditionally set the user/pass based on the requested page. As in unprivileged with select/insert/update/delete for normal usage, and privileged with definition/index related stuff in addition when visiting the upgrade page.

  2. “All privileges” usually means you should grand everything to the user. However …

    I’ve found at least one article that claims the MySQL user only needs:

    • SELECT
    • INSERT
    • UPDATE

    Digging deeper, I found that in order to operate fully (automated updates, plug-in installation/uninstallation, etc.), WordPress requires some additional permissions:

    • DELETE
    • ALTER (for updates)
    • CREATE TABLE
    • DROP TABLE

    Also, not referenced but it makes sense:

    • INDEX

    But those are the only two solid references I can find that are backed up by opinions posted elsewhere. I’d still encourage you to stick with GRANT ALL, but if you absolutely must limit your DB use, start with these 7 privileges and test fully to make sure things work as expected.

  3. Here’s what the Hardening WordPress article has to say on restricting database user privileges:

    For normal WordPress operations, such as posting blog posts, uploading media files, posting comments, creating new WordPress users and installing WordPress plugins, the MySQL database user only needs data read and data write privileges to the MySQL database; SELECT, INSERT, UPDATE and DELETE.

    Therefore any other database structure and administration privileges, such as DROP, ALTER and GRANT can be revoked. By revoking such privileges you are also improving the containment policies.

    Note: Some plugins, themes and major WordPress updates might require to make database structural changes, such as add new tables or change the schema. In such case, before installing the plugin or updating a software temporarily allow the database user the required privileges.

    https://wordpress.org/support/article/hardening-wordpress/

  4. Regarding the “Note” in redburn’s post, the WordPress Codex also has a Warning you should also read about updates and database schema changes…

    (Edit: I notice however that I DO NOT SEE “GRANT” in the list of privileges anymore when creating or updating a user. Perhaps “CREATE” should be added to the list? Does anyone have information regarding this? — using Hostgator cPanel, March 2016 –)

    WARNING:
    Attempting updates without having these privileges [SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, and GRANT] can cause
    problems when database schema changes occur. Thus, it is NOT
    recommended to revoke these privileges. If you do feel the need to do
    this for security reasons, then please make sure that you have a solid
    backup plan in place first, with regular whole database backups which
    you have tested are valid and that can be easily restored. A failed
    database upgrade can usually be solved by restoring the database back
    to an old version, granting the proper permissions, and then letting
    WordPress try the database update again. Restoring the database will
    return it back to that old version and the WordPress administration
    screens will then detect the old version and allow you to run the
    necessary SQL commands on it. Most WordPress upgrades do not change
    the schema, but some do. Only major point upgrades (3.7 to 3.8, for
    example) will alter the schema. Minor upgrades (3.8 to 3.8.1) will
    generally not. Nevertheless, keep a regular backup.

    Codex: http://codex.wordpress.org/Hardening_WordPress

  5. My opinion is the same as @EAMann above, as well as the sources he referenced: GRANT ALL is necessary in ensuring your site is functional and future proof. Even on a production site, you shall try stick to the user manual.

    As someone who contributes code to WordPress core and a few plug-ins, I recommend you retain the default DB privileges as suggested in the user manual ( GRANT ALL PRIVILEGES ON wpdatabasename.* TO “wordpressusername”@”hostname” ).

    The WordPress source code (both present and future) assumes the WordPress DB user has all DB privileges for the given WordPress database. If your setup miss any DB privileges, you may run into issues as you upgrade WordPress and add more plug-ins.

    So you really shouldn’t be using DB privileges different from the default DB privileges recommended by the manual, unless you know what you’re doing, have very specific needs, and won’t forget your have custom DB privileges.

    The Codex page has since been updated on how to do this with examples on various systems and screenshots.
    https://codex.wordpress.org/Installing_WordPress#Step_2:_Create_the_Database_and_a_User

    Creating a Databse name and user (via PHPMyAdmin):
    https://codex.wordpress.org/Installing_WordPress#Using_phpMyAdmin

    Creating a Databse name and user (via MySQL command line client):
    https://codex.wordpress.org/Installing_WordPress#Using_the_MySQL_Client

    mysql> CREATE DATABASE wpdatabasename;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON wpdatabasename.* TO "wordpressusername"@"hostname"
    -> IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> EXIT