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 |+--------------------------------------------------------------------+ |
