====== MySQL Privileges ======

  * [[MySQL]]
  * [[mysqladmin]]
  * [[mysqldump]]
  * [[mysqlimport]]

  * [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html|Privileges Provided by MySQL]]

==== Users ====

== Create a new admin ==

<code>
mysql -e "CREATE USER 'user'@'localhost' IDENTIFIED BY 'some_pass';"
mysql -e "GRANT ALL PRIVILEGES ON *.* TO user@localhost WITH GRANT OPTION;"
</code>

== Create a new user for website ==

<code>
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';"
</code>

Note: EXECUTE SQL is for executing prepared statements.

== Create user for backups ==

<code>
GRANT SELECT, LOCK TABLES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'some_pass';
</code>

== Drop User ==

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

<code>
mysql -e "CREATE USER 'user'@'localhost';"
</code>


== Give user privileges to all databases except mysql ==

<code>
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql');
</code>

== Granting a user specific privileges to one table ==

<code>GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON  `steve`.`testing` TO  'testing'@'%';</code>

==== Functions ====

^ MySQL 5.1 Function ^ Admin ^ Users ^ Export ^ Import ^ Users ^
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_all|ALL PRIVILEGES]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_create-user|CREATE USER]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_file|FILE]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_grant-option|GRANT OPTION]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_process|PROCESS]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_reload|RELOAD]] | ✔ | | ✔ | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_replication-client|REPLICATION CLIENT]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_replication-slave|REPLICATION SLAVE]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_show-databases|SHOW DATABASES]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_shutdown|SHUTDOWN]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_super|SUPER]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_usage|USAGE]] | ✔ | | | | Admin |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_alter|ALTER]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_alter-routine|ALTER ROUTINE]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_create|CREATE]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_create-routine|CREATE ROUTINE]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_create-temporary-tables|CREATE TEMPORARY_TABLES]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_create-view|CREATE VIEW]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_execute|EXECUTE]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_delete|DELETE]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_drop|DROP]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_index|INDEX]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_insert|INSERT]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_lock-tables|LOCK TABLES]] | ✔ | ✔ | ✔ | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_select|SELECT]] | ✔ | ✔ | ✔ | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_show-view|SHOW VIEW]] | ✔ | ✔ | ✔ | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_trigger|TRIGGER]] | ✔ | ✔ | | ✔ | All |
| [[http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_update|UPDATE]] | ✔ | ✔ | | ✔ | All |