Synology MariaDB 10 创建用户错误

原来用的Wordpress使用的是MariaDB 5,最新更新到了MariaDB 10,然后切换完后想给它一个独立账户登录专门的Wordpress数据库提高安全性。

然而用Adminer/phpmyadmin之类网页管理客户端,登录数据库管理进行创建用户赋予权限时总是提示如下错误。

The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement 14:20:36 SQL命令
CREATE USER 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
(0.001 秒)

仔细搜索了一通解决方案,原来问题是这样子。https://github.com/phpmyadmin/phpmyadmin/issues/12366

默认的MariaDB启动了密码复杂性检查。

MariaDB > show variables like "%validation%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| strict_password_validation | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

参考测试密码格式过程如下:

Thanks for your reply and your detailed explainations. Here are the results:

CREATE USER mysqltest1 identified by '123';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

CREATE USER mysqltest2 IDENTIFIED VIA mysql_native_password USING '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

ERROR 1290 (HY000): The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement

CREATE USER mysqltest3 IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

ERROR 1290 (HY000): The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement

And I also tried something like 
CREATE USER mysqltest4 IDENTIFIED BY 'dGbJ5zWqLPTPbEn2'; - at least that is working.

从以上可以看出,网页管理客户端输入的是正常密码,但是传递给服务器的是用户输入密码Hashed之后的格式,所以不能通过复杂密码格式检查就报出开头的错误。

所以我们在命令行直接执行最后一条命令的格式就可以创建新用户。

CREATE USER 'test'@'localhost' IDENTIFIED BY 'testpassword';

然后再回到数据库的权限管理,赋予创建的用户相当的数据库权限,应用生效即可。

参考资料链接:https://mariadb.com/kb/en/password-validation/

发表评论

邮箱地址不会被公开。 必填项已用*标注