MySQL用户远程登陆权限与数据库权限

#执行查询
use mysql;
select host,user,password from user;

#此时可看到数据库中的用户以及权限列表
+-------------------------+----------+-------------------------------------------+
| host                    | user     | password                                  |
+-------------------------+----------+-------------------------------------------+
| localhost               | root     | *059BFB76A84ADB3C4242B90C43E79454AB04B1F9 |
| iz2ze8ily7tobzmsefw18wz | root     |                                           |
| 127.0.0.1               | root     |                                           |
| ::1                     | root     |                                           |
+-------------------------+----------+-------------------------------------------+

#执行命令
use mysql;
update user set host='%' where user='root' and host='localhost';
flush privileges;

#执行查询
select host,user,password from user;

#查询结果如下，可看到host字段变为 %
+-------------------------+----------+-------------------------------------------+
| host                    | user     | password                                  |
+-------------------------+----------+-------------------------------------------+
| %                       | root     | *059BFB76A84ADB3C4242B90C43E79454AB04B1F9 |
| iz2ze8ily7tobzmsefw18wz | root     |                                           |
| 127.0.0.1               | root     |                                           |
| ::1                     | root     |                                           |
+-------------------------+----------+-------------------------------------------+

#执行命令
use mysql;
update user set host='192.168.1.111' where user='root' and host='%';
flush privileges;

#执行查询
select host,user,password from user;

#查询结果如下，可看到host字段变为指定IP地址
+-------------------------+----------+-------------------------------------------+
| host                    | user     | password                                  |
+-------------------------+----------+-------------------------------------------+
| 192.168.1.111           | root     | *059BFB76A84ADB3C4242B90C43E79454AB04B1F9 |
| iz2ze8ily7tobzmsefw18wz | root     |                                           |
| 127.0.0.1               | root     |                                           |
| ::1                     | root     |                                           |
+-------------------------+----------+-------------------------------------------+

#已存在的用户为 testuser
#通过查看数据库中该用户的登陆权限选择使用 % 还是其它值

grant all privileges on *.* to testuser@'%';

#已存在的用户为 testuser
#dbname的值为要授权的数据库名称
#通过查看数据库中该用户的登陆权限选择使用 % 还是其它值

grant all privileges on dbname.* to testuser@'%';

#创建用户名称为 testuser 密码为 123456 权限为可远程登录 %
#为用户 testuser 授予所有数据库的权限

create user 'testuser'@'%' identified by '123456';
grant all privileges on *.* to testuser@'%';