MySQL: What exact commands does each privilege level allow?

Question

I am configuring some new logins for users who have already been using the relevant MySQL servers under other logins. I would like to limit their privileges to the minimum each user needs, based on their input and reviewing the logs of what commands they have been executing. I would therefore like to know exactly what commands are allowed by each grantable privilege.

The specification in MySQL's documentation provides some insight beyond the obvious (e.g. "The SELECT privilege enables you to select rows from tables in a database.") but it seems like the information given there is not exhaustive. For example, I see in the logs that a user with only Select_priv enabled also used SHOW VARIABLES and SET but I don't see any documentation of those commands being included in the SELECT privilege.

This answer and this question give clues that there is more to the privileges than their names would directly imply.

UPDATE: @Colin 't Hart's comment is informative in that it explains the ability to execute SHOW and SET regardless of privileges. So my question should be limited to server side commands. I.e. Are there commands not listed in the specification which can be used by users with a given privilege?

• Colin 't Hart: show and set are commands of the MySQL client; you can't prevent users from using them. set global is only allowed if the user has super. See dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges
• WAF: OK, I see my fundamental misunderstanding of command scope. Can you confirm then, that the "SELECT" privilege allows only that server side command and no other?
• Colin 't Hart: Yes, that's what it says here: dev.mysql.com/doc/refman/5.1/en/…

Answer 1

show and set are commands of the MySQL client; you can't prevent users from using them.

set global is only allowed if the user has the super system privilege. See dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges

Answer 2

The specification provided in the MySQL documentation gives most of the information on what abilities each privilege type enable, but it needs to be supplemented by other sources to get a comprehensive picture of what each privilege level allows.

Here are some examples from Managing and Using MySQL , pp. 100-102, that are not apparent from the docs:

• PROCESS allows KILL SQL.
• ALL PRIVILEGES does not allow FILE, PROCESS, RELOAD, SHUTDOWN, or GRANT.
• SELECT and DELETE together allow REPLACE.