Table of Contents

MySQL Privileges

Users

Create a new admin
mysql -e "CREATE USER 'user'@'localhost' IDENTIFIED BY 'some_pass';"
mysql -e "GRANT ALL PRIVILEGES ON *.* TO user@localhost WITH GRANT OPTION;"
Create a new user for website
mysql -e "CREATE USER 'user'@'localhost' IDENTIFIED BY 'some_pass';"
mysql -e "GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, EXECUTE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT,  SHOW VIEW, TRIGGER, UPDATE ON *.* TO 'user'@'localhost';"

Note: EXECUTE SQL is for executing prepared statements.

Create user for backups
GRANT SELECT, LOCK TABLES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'some_pass';
Drop User

Dropping a user must have both the username and the server as well: 

mysql -e "CREATE USER 'user'@'localhost';"
Give user privileges to all databases except mysql
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql');
Granting a user specific privileges to one table
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON  `steve`.`testing` TO  'testing'@'%';

Functions

MySQL 5.1 Function Admin Users Export Import Users
ALL PRIVILEGES ✔ Admin
CREATE USER ✔ Admin
FILE ✔ Admin
GRANT OPTION ✔ Admin
PROCESS ✔ Admin
RELOAD ✔ ✔ Admin
REPLICATION CLIENT ✔ Admin
REPLICATION SLAVE ✔ Admin
SHOW DATABASES ✔ Admin
SHUTDOWN ✔ Admin
SUPER ✔ Admin
USAGE ✔ Admin
ALTER ✔ ✔ ✔ All
ALTER ROUTINE ✔ ✔ ✔ All
CREATE ✔ ✔ ✔ All
CREATE ROUTINE ✔ ✔ ✔ All
CREATE TEMPORARY_TABLES ✔ ✔ ✔ All
CREATE VIEW ✔ ✔ ✔ All
EXECUTE ✔ ✔ ✔ All
DELETE ✔ ✔ ✔ All
DROP ✔ ✔ ✔ All
INDEX ✔ ✔ ✔ All
INSERT ✔ ✔ ✔ All
LOCK TABLES ✔ ✔ ✔ ✔ All
SELECT ✔ ✔ ✔ ✔ All
SHOW VIEW ✔ ✔ ✔ ✔ All
TRIGGER ✔ ✔ ✔ All
UPDATE ✔ ✔ ✔ All