深入理解 MySQL 用户和权限

一. 用户

只创建用户

CREATE USER username IDENTIFIED BY 'password';

创建用户的同时授予权限

GRANT ALL ON databasename.* TO username@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

DROP USER username@localhost;

SET PASSWORD FOR username@localhost = password('newpassword');

use mysql;
UPDATE USER SET password=password('newpassword') WHERE user='username' and host='localhost';
FLUSH PRIVILEGES;

mysqladmin -uroot -pold_password password new_password

打开mysql命令行

use mysql;
UPDATE USER SET password=password('newpassword') WHERE user='username' and host='localhost';
FLUSH PRIVILEGES;

！！！注意：在每次执行完分配权限命令后，需要刷新权限
FLUSH PRIVILEGES;

服务器

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

数据库

GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'localhost';

数据表

GRANT ALL PRIVILEGES ON databasename.tablename TO 'username'@'localhost';

数据表的列

GRANT SELECT(id, col1, col2) ON databasename.tablename TO 'username'@'localhost';

存储过程

GRANT EXECUTE ON PROCEDURE databasename.tablename TO 'username'@'localhost';

函数

GRANT EXECUTE ON FUNCTION databasename.tablename TO 'username'@'localhost';

为普通用户添加权限

GRANT SELECT, INSERT, UPDATE, DELETE ON databasename.* TO 'username'@'%';

为开发者添加权限

GRANT CREATE, ALTER, DROP, REFERENCES ON databasename.* TO 'username'@'192.168.0.%';

为普通 DBA 添加权限

GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'localhost';

为高级 DBA 添加权限

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

SHOW GRANTS FOR username;

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

GRANT ：执行命令词，一般为动词
ALL：赋予权限名，参考下面的权限列表
*.*：前者表示数据库名，后者表示数据表名
- databasename.*：表示在databasename中的所有表
- databasename.tablename：表示在databasename中的tablename表
'username'@'localhost'：前者为用户名，后者为接入的IP
- 'username'@'%'：可以从任何地点接入
- 'username'@'192.168.1.%' ：192.168.1 IP下的局域网都可接入
- 'username'@'%.website.com' ：可以从website.com接入
- 'username'@'localhost': 只可以本机登录
- localhost 通过UNIXsocket连接，不会被解析为IP地址
  
  127.0.0.1 通过TCP/IP协议连接，只能本机访问
  
  ::1 ::1 支持ipv6，等同于ipv4的127.0.0.1