mysql用户权限
1、创建用户帐号：
CREATE USER user_name IDENTIFIED BY 'your_password';
2、用户改名：
RENAME USER old_name TO new_name;
3、删除用户帐号
DROP USER user_name;
4、创建用户并授权：
mysql> grant SELECT, INSERT, UPDATE, DELETE on *.* to lizhi@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for lizhi@'%'\G;
*************************** 1. row ***************************
Grants for lizhi@%: GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'lizhi'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)
ERROR:
No query specified
5、查看用户权限
查看root用户权限：
mysql> show grants for root@'%'\G;
*************************** 1. row ***************************
Grants for root@%: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION
1 row in set (0.00 sec)
ERROR:
No query specified
查看普通用户权限（edu用户）：
mysql> show grants for edu@'%'\G;
*************************** 1. row ***************************
Grants for edu@%: GRANT USAGE ON *.* TO 'edu'@'%' IDENTIFIED BY PASSWORD '*07D7D0444BBFC047420EFDA928B0FF2DA214BC95'
*************************** 2. row ***************************
Grants for edu@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `dbedu`.* TO 'edu'@'%'
2 rows in set (0.00 sec)
ERROR:
No query specified
6、移除权限示例（移除lizhi用户的delete权限）：
mysql> show grants for lizhi@'%'\G;
*************************** 1. row ***************************
Grants for lizhi@%: GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'lizhi'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> revoke delete on *.* from 'lizhi'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for lizhi@'%'\G;
*************************** 1. row ***************************
Grants for lizhi@%: GRANT SELECT, INSERT, UPDATE ON *.* TO 'lizhi'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'
1 row in set (0.00 sec)
ERROR:
No query specified
MySql的权限如下表所示：
| ALL | 除GRANT OPTION外的所有权限 |
| ALTER | 使用ALTER TABLE |
| ALTER ROUTING | 使用ALTER PROCEDURE和DROP PROCEDURE |
| CREATE | 使用CREATE TABLE |
| CREATE ROUTING | 使用CREATE PROCEDURE |
| CREATE TEMPORARY TABLES | 使用CREATE TEMPORARY TABLE |
| CREATE USER | 使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILLEAGES |
| CREATE VIEW | 使用CREATE VIEW |
| DELETE | 使用DELETE |
| DROP | 使用DROP TABLE |
| EXECUTE | 使用CALL和存储过程 |
| FILE | 使用SELECT INTO OUTFILE和LOAD DATA INFILE |
| GRANT OPTION | 使用GRANT和REVOKE |
| INDEX | 使用CREATE INDEX和DROP INDEX |
| INSERT | 使用INSERT |
| LOCK TABLES | 使用LOCK TABLES |
| PROCESS | 使用SHOW FULL PROCESSLIST |
| RELOAD | 使用FFLUSH |
| REPLICATION CLIENT | 服务器位置的访问 |
| REPLICATION SLAVE | 由复制从属使用 |
| SELECT | 使用SELECT |
| SHOW DATABASES | 使用SHOW DATABASES |
| SHOW VIEW | 使用SHOW CREATE VIEW |
| SHUTDOWN | 使用mysqladmin shutdown(用来关闭MySQL) |
| SUPER | 使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER和SET GLOBAL。还允许mysqladmin调试登录 |
| UPDATE | 使用UPDATE |
| USAGE |
