Mysql—用户表详解(mysql.user)

查询所有用户

mysql> select Host,User,Password from user;


查询用户权限：all表示所有权限，select表示只查权限，update表示只改权限，delete表示只删权限等。

mysql> show grants for "user"@"host";

mysql> show grants for "root"@"localhost";


添加授权用户(新创建的用户，默认情况下是没有任何权限的)：使用root用户连接到服务器

mysql> create user "用户名"@"IP地址" identified by "密码";

mysql> create user "haidon"@"%" identified by "123456";

mysql> create user "haidon"@"localhost" identified by "123456";

IP地址的表示方式：

1.% 表示用户可以从任何地址连接到服务器

2.localhost 表示用户只能从本地连接到服务器

3.指定一个ip表示用户只能从此ip连接到服务器


分配用户权限(给用户授权)

mysql> grant 权限列表 on 库.表 to "用户名"@"ip地址" with grant option;

mysql> grant all privileges on *.* to "haidon"@"%" with grant option;

mysql> grant all privileges on *.* to "haidon"@"%" identified by 'test' with grant option;

mysql> grant all privileges on domain_check.tb_user to "haidon"@"localhost" with grant option;

mysql> grant select on domain_check.tb_user to "haidon"@"localhost" with grant option;

mysql> grant select,insert on domain_check.tb_user to "haidon"@"132.24.98.25" with grant option;

1.权限列表：select、update、delete、insert、alter、drop、create、...(show)

2.库.表：*.*表示所有库的所有表。with grant option表示它具有grant权限。密码是test。

3.如果带了 with grant option，那么用户haidon可以将select ,update权限传递给其他用户( 如xiaodon)。

4.如果没带 with grant option，那么用户haidon不能给用户xiaodon授权。

5.all后面加上privileges，具体到哪些权限时得看MySQL版本，5.7版本不加privileges，8.0版本加privileges。

6.mysql> insert into user values("%","haidon",password("test"),"Y","Y","Y","Y","Y","Y","Y","Y","Y","Y");

  mysql> flush privileges; 这两句和上面第3句grant的效果是一样的。

7.mysql> insert into user (host,user) values("132.24.98.25","haidon");

  mysql> insert into db values("132.24.98.25","haidon","Y","Y","Y","Y","Y","Y","N","N","N","N")

  mysql> flush privileges; 这三句和上面第6句grant的效果是一样的。


收回用户权限

mysql> revoke all on *.* from "haidon"@"localhost";

mysql> revoke all on domain_check.tb_user from "haidon"@"localhost";

mysql> revoke select  on *.* from "haidon"@"localhost";


删除授权用户

mysql> drop user "用户名"@"ip地址"

mysql> drop user "haidon"@"%"

mysql> delete from user where user='haidon';

mysql> flush privileges;