数据库管理语句

MySQL 权限验证

MySQL 采用 主机名 + 用户名 来判断一个用户的身份；连接数据库时，MySQL 会根据连接的用户名和来源（ip或者主机名称）判断是否允许连接。与数据库建立连接之后，在执行如 create table、select、delete、update、create index 等操作时，MySQL 会判断当前连接的用户是否具有执行该类指令的权限。

权限生效

用户和权限信息存放在库名为 mysql 的库中，mysql 启动时，这些内容被读进内存并且立即生效，所以在通过直接操作这些表来修改用户及权限信息时，需要 重启 MySQL 或者执行 flush privileges; 来刷新数据库配置。

用户登录之后，MySQL 会和当前用户之间创建一个连接，此时用户相关的权限信息都保存在这个连接中，存放在内存中，此时如果有其他地方修改了当前用户的权限，这些变更的权限会在下一次登录时才会生效。

操作

查看 MySQL 中所有用户

use mysql;

select user, host from user;

创建用户

create user 用户名[@主机名] [identified by '密码'];

说明：

主机名默认值为 %，表示这个用户可以从任何主机连接 mysql 服务器 密码可以省略，表示无密码登录。

# 不指定主机名时，表示这个用户可以从任何主机连接 mysql 服务器
create user test1;

说明：

创建 test1 无密码用户，不指定任何主机，表示该用户可以从任何机器登录到 MySQL。

create user 'test2'@'localhost' identified by '123';

说明：

test2 的主机为 localhost 表示本机，此用户只能登陆本机的 MySQL。

create user 'test3'@% identified by '123';

说明：

test3 可以从任何机器连接到 MySQL 服务器。

create user 'test4'@'192.168.11.%' identified by '123';

说明：

test4 可以从 192.168.11 段的机器连接 MySQL。

修改密码

• 通过管理员修改密码

  set password for '用户名'@'主机' = password('密码');

• create user 用户名[@主机名] [identified by '密码'];

  set password = password('密码');

• 通过修改 mysql.user 表修改密码

  use mysql;
  update user set authentication_string = password('321') where user = 'test1' and host = '%';
  flush privileges;

  注意：

  通过表的方式修改之后，需要执行flush privileges;才能对用户生效。

  5.7 中 user 表中的 authentication_string 字段表示密码，老的一些版本中密码字段是 password。

用户授权

创建用户之后，需要给用户授权，才有意义。

grant privileges ON database.table TO 'username'[@'host'] [with grant option];

grant 命令说明：

• priveleges（权限列表），可以是 all，表示所有权限，也可以是 select、update、delete、insert 等权限，多个权限之间使用都好分开。

• on 指定权限针对那些库和表，格式为 数据库名.表名，点号前面为数据库名称，点号后面为表名；如果单独使用 . 则表示所有数据库和表。

• to 将权限赋予某个用户，格式为 username@host，@前面为用户名，@后面跟主机名，可以是 IP、IP段、域名以及 %，% 表示任何。

• with grant option 这个选项表示该用户可以将自己的权限授权给别人。

  注意

  经常有人在创建按操作用户的时候不指定 with grant option 选项导致后来该用户不能使用 grant 命令创建用户或者给其它用户授权。

  备注：可以使用 grant 重复给用户添加权限，权限叠加，比如你先给用户添加一个 select 权限，然后又给用户添加一个 insert 权限，那么该用户就同时拥有了 select 和 insert 权限。

示例

grant all on *.* to 'test'@'%';

说明

给 test 授权可以操作所有库的所有权限，相当于 BDA。

grant select on seata.* to 'test'@'%';

说明

test 可以对 seata 库中所有的表执行 select。

grant select, update on seata.* to 'test'@'%';

说明

test 可以对 seata 库中所有的表执行 select，update。

grant select(user, host) on mysql.user to 'test'@'localhost';

说明

test 用户只能查询 mysql 库中的 user 表的 user 和 host 字段。

查看用户有那些权限

• show grants for '用户名'[@'主机']

  mysql> show grants for 'test'@'localhost';
  +--------------------------------------------------------------------+
  | Grants for test1@localhost                                         |
  +--------------------------------------------------------------------+
  | GRANT USAGE ON *.* TO 'test'@'localhost'                          |
  | GRANT SELECT (host, user) ON `mysql`.`user` TO 'test'@'localhost' |
  +--------------------------------------------------------------------+
  2 rows in set (0.00 sec)

• show grants;

  mysql> show grants;
  +---------------------------------------------------------------------+
  | Grants for root@localhost                                           |
  +---------------------------------------------------------------------+
  | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
  | GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'localhost'              |
  | GRANT DELETE ON `seata`.* TO 'root'@'localhost'                     |
  | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
  +---------------------------------------------------------------------+
  4 rows in set (0.00 sec)

撤销用户的权限

• 语法

  revoke privileges ON database.table FROM '用户名'[@'主机'];

  可以先通过show grants命令查询一下用户对于的权限，然后使用revoke命令撤销用户对应的权限，示例：

  mysql> show grants for 'test'@'localhost';
  +--------------------------------------------------------------------+
  | Grants for test@localhost                                         |
  +--------------------------------------------------------------------+
  | GRANT USAGE ON *.* TO 'test'@'localhost'                          |
  | GRANT SELECT (host, user) ON `mysql`.`user` TO 'test'@'localhost' |
  +--------------------------------------------------------------------+
  2 rows in set (0.00 sec)
  
  mysql> revoke select(host) on mysql.user from test@localhost;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> show grants for 'test'@'localhost';
  +--------------------------------------------------------------+
  | Grants for test@localhost                                   |
  +--------------------------------------------------------------+
  | GRANT USAGE ON *.* TO 'test'@'localhost'                    |
  | GRANT SELECT (user) ON `mysql`.`user` TO 'test'@'localhost' |
  +--------------------------------------------------------------+
  2 rows in set (0.00 sec)

删除用户

• 方式一

  drop user '用户名'[@'主机']，示例：

  drop user test1@localhost;

  drop 的方式删除用户之后，用户下次登录就会起效。

• 方式二

  通过删除 mysql.user 表数据的方式删除，如下：

  delete from user where user='用户名' and host='主机';
  flush privileges;

  注意通过表的方式删除的，需要调用 flush privileges；刷新权限信息（权限启动的时候在内存中保存着，通过表的方式修改之后需要刷新一下）。

授权原则说明

• 只授予能满足需要的最小权限，防止用户干坏事，比如用户只是需要查询，那就只能给 select 权限就可以了，不要给用户授予 update、insert 或者 delete 权限；
• 创建用户的时候限制用户的登录主机，一般是限制成指定 IP 或者内网 IP 段；
• 初始化数据库的时候删除没有密码的用户，安装完数据库的时候会自动创建一些用户，这些用户默认没有密码；
• 为每个用户设置满足密码复杂度的密码；
• 定期清理不需要的用户，回收权限或者删除用户。

总结

• 通过命令的方式操作用户和权限不需要刷新，下次登录自动生效；
• 通过操作 MySQL 库中表的方式修改、删除用户信息，需要调用 flush privileges ；刷新一下，下次登录自动生效；
• MySQL 识别用户身份的方式是：用户名 + 主机；
• 本文中讲到的一些指令中带主机的，主机都可以省略，默认值为 %，表示所有机器；
• MySQL 中用户和权限的信息存在库名为 mysql 的库中。