MySQL - 用户与权限

创建新用户

创建用户，用户名为 testuser，密码为 123456，仅可本地连接

grant all privileges on *.* to testuser@localhost identified by "123456";

允许指定 IP 远程访问

grant all privileges on *.* to testuser@"192.168.1.100" identified by "123456";

允许指定 IP 段远程访问

grant all privileges on *.* to testuser@"192.168.1.%" identified by "123456";

允许所有 IP 远程访问

grant all privileges on *.* to testuser@"%" identified by "123456";

提醒：分配权限后，需执行 flush privileges;，使其生效，下同。

---

修改用户访问数据库权限

允许用户访问所有数据库

grant all privileges on *.* to testuser@localhost identified by "123456";

允许用户访问指定数据库 test_db

 grant all privileges on test_db.* to testuser@localhost identified by "123456";

允许用户访问指定数据库 test_db 的指定表 user

grant all privileges on test_db.user to testuser@localhost identified by "123456" ;　

---

修改用户操作权限

允许用户所有操作权限

grant all privileges on *.* to testuser@localhost identified by "123456" WITH GRANT OPTION ;

允许用户查询权限

grant select on *.* to testuser@localhost identified by "123456" ;

允许用户增删改查权限

 grant select,insert,update,delete on *.* to testuser@localhost identified by "123456"  ;

---

常用设置

查询所有用户

use mysql;
select User,Host from user;

关闭 root 用户远程访问权限

use mysql;
delete from user where user="root" and host="%" ;

---

修改用户密码 mysql.user

set password for <username>@<host> = password('<new password>');