mysql 5.7安全_MySQL 5.7 新的权限与安全问题

最新推荐文章于 2021-12-12 00:29:34 发布

你们叫我小蚂蚁吧

最新推荐文章于 2021-12-12 00:29:34 发布

阅读量402

点赞数

文章标签： mysql 5.7安全

本文链接：https://blog.csdn.net/weixin_36212175/article/details/113422464

版权

SQL Error (1130): Host '192.168.1.100' is not allowed to connect to this MySQL server

说明所连接的用户帐号没有远程连接的权限，只能在本机(localhost)登录。

需更改 mysql 数据库里的 user表里的 host项：把localhost改称%

mysql>use mysql;

mysql>update user set host = '%' where user ='root';

mysql>flush privileges;

mysql>select 'host','user' from user where user='root';

旧版本 MySQL 可以 IP 授权与修改密码同时进行：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

注意1：授权可以用上述语句，但是修改密码新版 MySQL 不能再用 GRANT ALL IDENTIFIED BY 了：Using GRANT statement to modify existing user properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.

ALTER USER 'root'@'%' IDENTIFIED BY 'pwd';

如果使用 ALTER USER 'root'@'%' IDENTIFIED WITH sha256_password BY 'pwd'; 指定加密方式则可能在客户端连接时有问题：

注意2： ERROR 2059 (HY000): Authentication plugin 'sha256_password' cannot be loaded: No such file or directory

update user set plugin='mysql_native_password' where user = 'root' and host = '%';

update mysql.user set password=PASSWORD("pwd") where User='root';

注意3： ERROR 1054 (42S22): Unknown column 'password' in 'field list'

update mysql.user set authentication_string=password("pwd") where user='root';

注意4： 'PASSWORD' is deprecated and will be removed in a future release.

password 即将被废弃，官方不建议用继续使用了，建议使用第1点中的 ALTER USER 语法去管理用户属性。

Access denied for user 'root'@'IP地址' ,是因为相应的主机没有对应的访问权限

--开放权限如下

use mysql;

update user u set u.host = '%' where u.user = 'root' limit 1;

flush privileges;

--查看用户权限

show grants for current_user();

--mysql不推荐通过修改表的方式修改用户密码

INSERT or UPDATE statements for the mysql.user table that refer to literal passwords are logged as is,so you should avoid such statements

--通过客户端sql修改

MariaDB [mysql]> UPDATE user SET Password = password('123456') WHERE User = 'root' ;

--此时可在binglog中可以看到明文的密码

[root@rudy_01 3306]# mysqlbinlog binlog.000006 --start-position=4224 >/tmp/test.sql

[root@rudy_01 3306]# cat /tmp/test.sql

SET @@session.collation_database=DEFAULT/*!*/;

UPDATE user SET Password = password('123456') WHERE User = 'root'

--在 mysql 5.7 中 password 字段已经不存在了

mysql> UPDATE user SET Password = password('123456') WHERE User = 'root' ;

ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

mysql> desc user;

+------------------------+-----------------------------------+------+-----+-----------------------+-------+

+------------------------+-----------------------------------+------+-----+-----------------------+-------+

| Host | char(60) | NO | PRI | | |

| User | char(32) | NO | PRI | | |

| Select_priv | enum('N','Y') | NO | | N | |

--注意出于安全考虑，alter user 时提示更新的是 0 条数据，但实际 password 已更新

mysql> select host,user,authentication_string,password_last_changed from user where user='root' and host='%';

+------+------+-------------------------------------------+-----------------------+

+------+------+-------------------------------------------+-----------------------+

| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 2016-01-08 15:38:13 |

+------+------+-------------------------------------------+-----------------------+

1 row in set (0.04 sec)

--提示更新0条，使用此方法不需要再 flush privileges

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE,SET PASSWORD, or RENAME USER,

the server notices these changes and loads the grant tables into memory again immediately.

mysql> alter user 'root'@'%' identified by '12345678';

Query OK, 0 rows affected (0.00 sec)

--实际已更新

mysql> select host,user,authentication_string,password_last_changed from user where user='root' and host='%';

+------+------+-------------------------------------------+-----------------------+

+------+------+-------------------------------------------+-----------------------+

| % | root | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | 2016-01-08 15:53:09 |

+------+------+-------------------------------------------+-----------------------+

1 row in set (0.00 sec)

--在binlog中查出的sql如下

[root@rudy mysql]# cat /tmp/test.sql

SET @@session.collation_database=DEFAULT/*!*/;

ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

--mysql对于密码有3种检验策略，默认validate_password_policy为MEDIUM

? LOW policy tests password length only. Passwords must be at least 8 characters long.

? MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.

? STRONG policy adds the condition that password substrings of length 4 or longer must not match words

--注意validate_password默认是没有安装的

If the validate_password plugin is not installed, the validate_password_xxx system variables are not available,

passwords in statements are not checked, and VALIDATE_PASSWORD_STRENGTH() always returns 0.

--检验密码复杂度

mysql> select VALIDATE_PASSWORD_STRENGTH('abc1235jeme');

+-------------------------------------------+

| VALIDATE_PASSWORD_STRENGTH('abc1235jeme') |

+-------------------------------------------+

| 0 |

+-------------------------------------------+

1 row in set (0.00 sec)