MySQL 5.7 学习心得之安全相关特性
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 | 默认，过期时间受全局变量控制 |
| 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 | +--------------------+ | information_schema | | test | +--------------------+ mysql> use test mysql> show tables; +----------------+ | tables_in_test | +----------------+ | tttt | +----------------+ mysql> select * from tttt; +------+ | id | +------+ | 1 | | 100 | +------+ mysql> insert into tttt values(2),(200); mysql> select * from tttt; +------+ | id | +------+ | 1 | | 100 | | 2 | | 200 | +------+
验证得出代理账号(dxy_proxy)代理了原始账号(dxy)的权限。
1.5：其他选项：ssl、max_queries_per_hour、max_updates_per_hour、max_connections_per_hour、max_user_connections。当需要限制账号通过ssl登陆，需要添加require，当需要限制资源，需要添加with：
create user dxy@localhost identified by '123456' require ssl with max_queries_per_hour 100 max_user_connections 100 password expire never account unlock;
2，外部相关的安全
2.1：mysql5.7已经删除了test数据库，默认安装完后是没有test数据库，原先任何用户都可以访问test数据库，增加安全隐患。
2.2：mysql5.7提供了更为简单ssl安全访问配置，并且默认连接就采用ssl的加密方式。在5.7之前，生成ssl相关文件需要自己手动创建，可以查看这篇文章，5.7之后mysql通过
mysql_ssl_rsa_setup可以直接生成了：
root@t20:~# mysql_ssl_rsa_setup generating a 2048 bit rsa private key .................................+++ ....................+++ writing new private key to 'ca-key.pem' ----- generating a 2048 bit rsa private key ......+++ ..............................+++ writing new private key to 'server-key.pem' ----- generating a 2048 bit rsa private key .........................................................................................+++ ..+++ writing new private key to 'client-key.pem' -----
可以在数据目录下面看到一些以pem结尾的文件，而这些文件就是开启ssl连接所需要的文件(注意文件权限)，之后用账号
默认登陆：
root@t20:/var/lib/mysql# mysql -udba -p -h127.0.0.1 enter password: mysql> \s -------------- mysql ver 14.14 distrib 5.7.12, for linux (x86_64) using editline wrapper connection id: 4 current database: current user: dba@localhost ssl: cipher in use is dhe-rsa-aes256-sha ... ...
强制ssl登陆：
root@t20:~# mysql -udba -p -h127.0.0.1 --ssl=1 warning: --ssl is deprecated and will be removed in a future version. use --ssl-mode instead. enter password: mysql> \s -------------- mysql ver 14.14 distrib 5.7.12, for linux (x86_64) using editline wrapper connection id: 10 current database: current user: dba@localhost ssl: cipher in use is dhe-rsa-aes256-sha ... ...
从上面看到均已ssl登陆，若在创建用户时，希望该用户每次必须通过ssl方式，则需在创建用户通过require ssl来进行设置，上面已经介绍。姜承尧文章中的测试案例显示开启ssl性能开销在25%左右：mysql的ssl加密连接与性能开销
2.3：mysql5.7开始建议用户使用 mysqld --initialize来初始化数据库，放弃之前的mysql_install_db的方式，新的方式只创建了一个root@localhost的用户，随机密码保存在~/.mysql_secret文件中，第一次使用必须reset password。
初始化数据库：新建实例。
mysqld --initialize --datadir=/var/lib/mysql3309/
2.4：mysql5.7 sql_mode的变更，
5.7默认的sql_mode
select @@sql_mode;
only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_user,no_engine_substitution
5.7之前默认的sql_mode
select @@sql_mode;
no_engine_substitution
看到在5.7中sql_mode更加严格。解释下各个mode的含义：
|
only_full_group_by
|
不要让group by部分中的查询指向未选择的列 |
|
strict_trans_tables
|
为事务存储引擎启用严格模式，也可能为非事务存储引擎启用严格模式 |
| no_zero_in_date | 在严格模式，不接受月或日部分为0的日期 |
| no_zero_date | 在严格模式，不将 '0000-00-00'做为合法日期 |
| error_for_division_by_zero | 在严格模式，在insert或update过程中，如果被零除(或mod(x，0))，则产生错误 |
| no_auto_create_user | 防止grant自动创建新用户，除非还指定了密码 |
| no_engine_substitution | 如果需要的存储引擎被禁用或未编译，可以防止自动替换存储引擎 |
在默认情况下5.7的情况：
----对于datetime类型<no_zero_date>： 插入"0000-00-00 00:00:00"值，会报错：incorrect datetime value ----对于varchar/char类型<strict_trans_tables>： 插入字符串超出长度，会报错： data too long for column... ----对于not null的列<strict_trans_tables>： 插入不指定not null的列会报错：field 'xxx' doesn't have a default value ' ----对于grant<no_auto_create_user>： 授权一个用户，不指定密码会报错：can't find any matching row in the user table ' ----对于engine存储引擎<no_engine_substitution>： 创建一个不支持的存储引擎，不会转换为默认的存储引擎，直接报错：unknown storage engine ... using storage engine innodb for table '...'
注意：在一个主从环境下，为保证数据的一致性，一定要设置主从的sql_mode一样，在数据迁移的时候也要保证sql_mode的一致，不然复制和迁移遇到上面的限制均会失败，所以尽可能使用标准sql语法。
3，总结：
在mysql 5.7中，有不少安全性相关的改进：创建账号分2步：用create user来建立账号（账号长度加大），用grant 来授权；初始数据库的时候密码不为空；账号可以锁和可以设置密码过期；test库被删除；默认提供ssl连接；sql_mode增强等。文章从这些方面进行了介绍和测试，进一步加深对mysql5.7的认识。
以上所述是小编给大家介绍的mysql 5.7 学习心得之安全相关特性，希望对大家有所帮助