| |
| — | mysql_privileges [2012/05/29 15:55] (current) – created - external edit 127.0.0.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 | |
