Differences

This shows you the differences between two versions of the page.

Link to this comparison view

mysql_privileges [2012/05/29 09:55] (current)
Line 1: Line 1:
 +====== 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 |