Differences
This shows you the differences between two versions of the page.
|
mysql_privileges [2012/05/29 09:55] |
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 | | ||
