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 [email protected] 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