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

2024-03-16 04:56:02 43

1，账号安全相关的特性

1.1：创建用户

5.7版本的用户表mysql.user要求plugin字段非空，且默认值是mysql_native_password认证插件，并且不再支持mysql_old_password认证插件。5.7用户长度最大为32字节，之前最大长度为16字节，并且CREATEUSER和DROPUSER命令里实现了IF[NOT]EXISTS条件判断。5.7之后用户通过grant创建用户报warning。如：

grantallon*.*todxy@localhostidentifiedby'dxy';
QueryOK,0rowsaffected,1warnings(0.00sec)
showwarnings;+---------+------+---------------------------------------------------------------+
|Level|Code|Message|
+---------+------+---------------------------------------------------------------+
|Warning|1287|UsingGRANTforcreatingnewuserisdeprecatedandwillberemovedinfuturerelease.CreatenewuserwithCREATEUSERstatement.|
+---------+------+---------------------------------------------------------------+
2rowsinset(0.01sec)

提示grant创建账户的语法将会被删除，用cerateuser代替，创建用户分2步：创建和授权。

先通过createuser创建用户：

#明文密码创建
CREATEUSER'dxy'@'localhost'IDENTIFIEDBY'123456';等同
CREATEUSER'dxy'@'localhost'IDENTIFIEDWITH'mysql_native_password'BY'123456';
#加密密码创建
CREATEUSER'dxy'@'localhost'IDENTIFIEDBYPASSWORD'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';--willberemovedinafuturerelease等同
CREATEUSER'dxy'@'localhost'IDENTIFIEDWITH'mysql_native_password'AS'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

再通过grant来授权：

grantselect,insert,update,deleteondba_test.*todxy@localhost;

注意：授权管理用户的时候，不止只有all的权限，还要包括withgrantoption和proxy的权限。proxy权限需要在代理用户的时候用到。

查看默认管理用户权限：
showgrantsforroot@localhost;----2条记录+---------------------------------------------------------------------+|
+---------------------------------------------------------------------+
|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'WITHGRANTOPTION|
|GRANTPROXYON''@''TO'root'@'localhost'WITHGRANTOPTION|
+---------------------------------------------------------------------+
新建管理账号：
createuserdba@127.0.0.1identifiedby'123456';
授权：
GRANTALLPRIVILEGESON*.*TO'root'@'127.0.0.1'WITHGRANTOPTION;
授proxy权：创建代理用户的时候需要
GRANTPROXYON''@''TO'dba'@'127.0.0.1'WITHGRANTOPTION;
查看：
showgrantsfor'dba'@'127.0.0.1';
+--------------------------------------------------------------------+
|GRANTALLPRIVILEGESON*.*TO'dba'@'127.0.0.1'WITHGRANTOPTION|
|GRANTPROXYON''@''TO'dba'@'127.0.0.1'WITHGRANTOPTION|
+--------------------------------------------------------------------+

查看用户权限：

showgrantsfordxy@localhost;
+---------------------------------------------------------------------------+
|Grantsfordxy@localhost|
+---------------------------------------------------------------------------+
|GRANTUSAGEON*.*TO'dxy'@'localhost'|
|GRANTSELECT,INSERT,UPDATE,DELETEON`dba_test`.*TO'dxy'@'localhost'|
+---------------------------------------------------------------------------+

查看用户密码：

showcreateuserdxy@localhost;
+----------------------------------------------------------------------------------+
|CREATEUSER'dxy'@'localhost'IDENTIFIEDWITH'mysql_native_password'AS'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'REQUIRENONEPASSWORDEXPIREDEFAULTACCOUNTUNLOCK|
+----------------------------------------------------------------------------------+

1.2：密码过期策略

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

PASSWORDEXPIREDEFAULT 默认，过期时间受全局变量default_password_lifetime控制 PASSWORDEXPIRENEVER 永不过期 PASSWORDEXPIREINTERVALNDAY N天后过期 PASSWORDEXPIRE 过期

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

createuserdxy@localhostidentifiedby'123456'passwordexpireinterval10day;----10天后过期

对已有用户设置

alteruserzjy@localhostpasswordexpirenever;----永不过期

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

设置用户密码过期：

alteruserdxy@localhostpasswordexpire;

执行任何命令报错：

ERROR1820(HY000):YoumustresetyourpasswordusingALTERUSERstatementbeforeexecutingthisstatement.

解决办法：重置密码alteruserdxy@localhostidentifiedby'123456';

1.3：锁定禁用用户alteruser

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

设置锁定用户：

alteruserdxy@localhostaccountlock;

登陆报错：

ERROR3118(HY000):Accessdeniedforuser'dxy'@'localhost'.Accountislocked.

解决办法：解锁用户

alteruserdxy@localhostaccountunlock;

1.4代理用户

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

创建原始账号：

createuserdxy@127.0.0.1identifiedby'123456';

授权：

grantallontest.*todxy@127.0.0.1;

创建代理账号：

createuserdxy_proxy@127.0.0.1identifiedby'123456';

授权代理权限：

grantproxyondxy@127.0.0.1todxy_proxy@127.0.0.1;

查看：

showgrantsfordxy_proxy@127.0.0.1;
+-------------------------------------------------------------+
|GRANTUSAGEON*.*TO'dxy_proxy'@'127.0.0.1'|
|GRANTPROXYON'dxy'@'127.0.0.1'TO'dxy_proxy'@'127.0.0.1'|
+-------------------------------------------------------------+

用代理账号登陆测试：

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

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

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

showgrants;+-------------------------------------------------------++-------------------------------------------------------+
|GRANTUSAGEON*.*TO'dxy'@'127.0.0.1'|
|GRANTALLPRIVILEGESON`test`.*TO'dxy'@'127.0.0.1'|
+-------------------------------------------------------+

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

mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|test|
+--------------------+
mysql>usetest
mysql>showtables;
+----------------+
|Tables_in_test|
+----------------+
|tttt|
+----------------+
mysql>select*fromtttt;+------+
|id|
+------+
|1|
|100|
+------+
mysql>insertintottttvalues(2),(200);
mysql>select*fromtttt;
+------+
|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：

createuserdxy@localhostidentifiedby'123456'requireSSLwithMAX_QUERIES_PER_HOUR100MAX_USER_CONNECTIONS100passwordexpireneveraccountunlock;

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
Generatinga2048bitRSAprivatekey
.................................+++
....................+++
writingnewprivatekeyto'ca-key.pem'
-----
Generatinga2048bitRSAprivatekey
......+++
..............................+++
writingnewprivatekeyto'server-key.pem'
-----
Generatinga2048bitRSAprivatekey
.........................................................................................+++
..+++
writingnewprivatekeyto'client-key.pem'
-----

可以在数据目录下面看到一些以pem结尾的文件，而这些文件就是开启SSL连接所需要的文件(注意文件权限)，之后用账号

默认登陆：

root@t20:/var/lib/mysql#mysql-udba-p-h127.0.0.1
Enterpassword:
mysql>\s
--------------
mysqlVer14.14Distrib5.7.12,forLinux(x86_64)usingEditLinewrapper
Connectionid:4
Currentdatabase:
Currentuser:dba@localhost
SSL:CipherinuseisDHE-RSA-AES256-SHA
...
...

强制ssl登陆：

root@t20:~#mysql-udba-p-h127.0.0.1--ssl=1
WARNING:--sslisdeprecatedandwillberemovedinafutureversion.Use--ssl-modeinstead.
Enterpassword:
mysql>\s
--------------
mysqlVer14.14Distrib5.7.12,forLinux(x86_64)usingEditLinewrapper
Connectionid:10
Currentdatabase:
Currentuser:dba@localhost
SSL:CipherinuseisDHE-RSA-AES256-SHA
...
...

从上面看到均已ssl登陆，若在创建用户时，希望该用户每次必须通过SSL方式，则需在创建用户通过REQUIRESSL来进行设置，上面已经介绍。姜承尧文章中的测试案例显示开启SSL性能开销在25%左右：MySQL的SSL加密连接与性能开销

2.3：MySQL5.7开始建议用户使用mysqld--initialize来初始化数据库，放弃之前的mysql_install_db的方式，新的方式只创建了一个root@localhost的用户，随机密码保存在~/.mysql_secret文件中，第一次使用必须resetpassword。

初始化数据库：新建实例。

mysqld--initialize--datadir=/var/lib/mysql3309/

2.4：MySQL5.7sql_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 不要让GROUPBY部分中的查询指向未选择的列 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-0000:00:00"值，会报错：Incorrectdatetimevalue
----对于varchar/char类型<STRICT_TRANS_TABLES>：
插入字符串超出长度，会报错：Datatoolongforcolumn...
----对于notnull的列<STRICT_TRANS_TABLES>：
插入不指定notnull的列会报错：Field'xxx'doesn'thaveadefaultvalue'
----对于grant<NO_AUTO_CREATE_USER>：
授权一个用户，不指定密码会报错：Can'tfindanymatchingrowintheusertable'
----对于engine存储引擎<NO_ENGINE_SUBSTITUTION>：
创建一个不支持的存储引擎，不会转换为默认的存储引擎，直接报错：Unknownstorageengine...UsingstorageengineInnoDBfortable'...'

注意：在一个主从环境下，为保证数据的一致性，一定要设置主从的sql_mode一样，在数据迁移的时候也要保证sql_mode的一致，不然复制和迁移遇到上面的限制均会失败，所以尽可能使用标准SQL语法。

3，总结：

在MySQL5.7中，有不少安全性相关的改进：创建账号分2步：用createuser来建立账号（账号长度加大），用grant来授权；初始数据库的时候密码不为空；账号可以锁和可以设置密码过期；test库被删除；默认提供ssl连接；sql_mode增强等。文章从这些方面进行了介绍和测试，进一步加深对MySQL5.7的认识。

以上所述是小编给大家介绍的MySQL5.7学习心得之安全相关特性，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！

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

热门推荐

随机推荐