MySQL 5.7 学习心得之安全相关特性_MySQL

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

查看用户权限：

show grants for dxy@localhost;
+---------------------------------------------------------------------------+
| Grants for dxy@localhost |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dxy'@'localhost' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `dba_test`.* TO 'dxy'@'localhost' |
+---------------------------------------------------------------------------+

查看用户密码：

show create user dxy@localhost;
+----------------------------------------------------------------------------------+
| CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+----------------------------------------------------------------------------------+

1.2：密码过期策略

为用户设置密码过期时间，一定时间以后，强制用户修改密码。可以直接在create user的时候设置，也可以alter user设置：

PASSWORD EXPIRE DEFAULT	默认，过期时间受全局变量default_password_lifetime控制
PASSWORD EXPIRE NEVER	永不过期
PASSWORD EXPIRE INTERVAL N DAY	N天后过期
PASSWORD EXPIRE	过期

直接创建用户的时候设置：

create user dxy@localhost identified by '123456' password expire interval 10 day; ---- 10天后过期

对已有用户设置

alter user zjy@localhost password expire never; ----永不过期

注意：设置一个用户过期后，登陆会有提示修改密码，不能进行任何操作：适用让程序不能访问数据库。

设置用户密码过期：

alter user dxy@localhost password expire;

执行任何命令报错：

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

解决办法：重置密码 alter user dxy@localhost identified by '123456';

1.3：锁定禁用用户 alter user

当某些场景需要"锁"住用户，暂时禁用某个用户：适用让程序不能访问数据库。

设置锁定用户：

alter user dxy@localhost account lock;

登陆报错：

ERROR 3118 (HY000): Access denied for user 'dxy'@'localhost'. Account is locked.

解决办法：解锁用户

alter user dxy@localhost account unlock;

1.4 代理用户

基于mysql_native_password的认证插件自带了代理用户的功能。代理用户相当于“代理”其他用户的权限，这样很方便的把一个账号的权限授予其他账号，而不需要每个账号都需要执行授权操作。开启代理用户的功能需要开启参数：check_proxy_users 和 mysql_native_password_proxy_users

创建原始账号：

create user dxy@127.0.0.1 identified by '123456';

授权：

grant all on test.* to dxy@127.0.0.1;

创建代理账号：

create user dxy_proxy@127.0.0.1 identified by '123456';

授权代理权限：

grant proxy on dxy@127.0.0.1 to dxy_proxy@127.0.0.1;

查看：

show grants for dxy_proxy@127.0.0.1;
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dxy_proxy'@'127.0.0.1' |
| GRANT PROXY ON 'dxy'@'127.0.0.1' TO 'dxy_proxy'@'127.0.0.1' |
+-------------------------------------------------------------+

用代理账号登陆测试：

查看登陆账号：代理账号current_user()，原始账号user()

select user(),current_user();
+---------------------+----------------+
| user() | current_user() |
+---------------------+----------------+
| dxy_proxy@127.0.0.1 | dxy@127.0.0.1 |
+---------------------+----------------+

查看权限：发现代理账号的权限显示的是原始账号的权限

show grants;+-------------------------------------------------------+ +-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dxy'@'127.0.0.1' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'dxy'@'127.0.0.1' |
+-------------------------------------------------------+

验证代理账号是否有test库的权限：

mysql> show databases;
+--------------------+
| Database |