MySQL Privileges
- - Users
  - Functions

MySQL Privileges

Privileges Provided by MySQL

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