WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

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.

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
    
Is there any reference on how to conditionally set the user/pass based on the requested page in a WordPress environment? TA – superjos Jan 24 '14 at 18:44
    
@superjos: Not that I'm aware of off the top of my head, but the gist of it would be to define the normal DB user select/insert/update/delete in wp-config and, when the url matches the relevant /wp-admin pages (likely upgrade, activate theme and activate plugin), to define an alternative user that can do everything else. – Denis de Bernardy Jan 24 '14 at 19:58

"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.

share|improve this answer
    
Thanks for sharing your thoughts. For that site installed I did not GRANT ALL as this is not a development site and instead stick to all incl. INDEX. Looks good so far, I think I keep it tracked over the next days, it's a real life site incl. a lot of plugin use and the like. For INDEX I might search core and the mysql manual a bit as well. – hakre Jan 6 '11 at 15:33
1  
Just remember that 3rd party plug-ins can call just about any SQL statement they want ... so make sure you properly vet them before installing things dependent on privileges you've revoked. – EAMann Jan 6 '11 at 15:41

Here's what Codex 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.

http://codex.wordpress.org/Hardening_WordPress

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.