1，账号安全相关的特性
1.1：创建用户
5.7版本的用户表mysql.user要求plugin字段非空，且默认值是mysql_native_password认证插件，并且不再支持mysql_old_password认证插件。5.7用户长度最大为32字节，之前最大长度为16字节，并且CREATE USER 和 DROP USER 命令里实现了 IF [NOT] EXISTS 条件判断。5.7之后用户通过grant创建用户报warning。如：
| grant all on *.* to dxy@localhost identified by 'dxy';Query OK, 0 rows affected, 1 warnings (0.00 sec)show warnings; +---------+------+---------------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------------+| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |+---------+------+---------------------------------------------------------------+2 rows in set (0.01 sec) |
提示grant创建账户的语法将会被删除，用cerate user代替，创建用户分2步：创建和授权。
先通过create user 创建用户：
| #明文密码创建CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456';等同CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';#加密密码创建CREATE USER 'dxy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; --will be removed in a future release等同CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; |
再通过grant来授权：
| grant select,insert,update,delete on dba_test.* to dxy@localhost; |
注意：授权管理用户的时候，不止只有all的权限，还要包括with grant option和proxy的权限。proxy权限需要在代理用户的时候用到。
| 查看默认管理用户权限：show grants for root@localhost; ----2条记录 +---------------------------------------------------------------------+ |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |+---------------------------------------------------------------------+新建管理账号：create user dba@127.0.0.1 identified by '123456';授权：GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;授proxy权：创建代理用户的时候需要GRANT PROXY ON ''@'' TO 'dba'@'127.0.0.1' WITH GRANT OPTION;查看：show grants for 'dba'@'127.0.0.1';+--------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'dba'@'127.0.0.1' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'dba'@'127.0.0.1' WITH GRANT OPTION |+--------------------------------------------------------------------+ |
