MySQL Database User: Which Privileges are needed?

Question

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.

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

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