mysql5.7安全_MySQL5.7新的权限与安全问题
SQL Error (1130): Host '192.168.1.100' is not allowed to connect to this MySQL rver
说明所连接的⽤户帐号没有远程连接的权限，只能在本机(localhost)登录。
需更改 mysql 数据库⾥的 ur表⾥的 host项：把localhost改称%
mysql>u mysql;
mysql>update ur t host = '%' where ur ='root';
mysql>flush privileges;
mysql>lect 'host','ur' from ur where ur='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 ur properties other than privileges is deprecated and will be removed in future relea. U ALTER USER statement for this operation.
horde
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 ur t plugin='mysql_native_password' where ur = 'root' and host = '%';
update mysql.ur t password=PASSWORD("pwd") where Ur='root';
注意3： ERROR 1054 (42S22): Unknown column 'password' in 'field list'
update mysql.ur t authentication_string=password("pwd") where ur='root';
注意4： 'PASSWORD' is deprecated and will be removed in a future relea.
prepare的用法
password 即将被废弃，官⽅不建议⽤继续使⽤了，建议使⽤第1点中的 ALTER USER 语法去管理⽤户属性。
Access denied for ur 'root'@'IP地址' ，是因为相应的主机没有对应的访问权限因为痛所以叫青春
--开放权限如下
u mysql;
update ur u t u.host = '%' where u.ur = 'root' limit 1;三月英文
flush privileges;
gift怎么读
--查看⽤户权限
show grants for current_ur();
--mysql不推荐通过修改表的⽅式修改⽤户密码
INSERT or UPDATE statements for the mysql.ur table that refer to literal passwords are logged as is,so you should avoid such statements
--通过客户端sql修改
MariaDB [mysql]> UPDATE ur SET Password = password('123456') WHERE Ur = 'root' ;
--此时可在binglog中可以看到明⽂的密码
[root@rudy_01 3306]# mysqlbinlog binlog.000006 --start-position=4224 >/tmp/test.sql
[root@rudy_01 3306]# cat /tmp/test.sql
SET @@llation_databa=DEFAULT/*!*/;
UPDATE ur SET Password = password('123456') WHERE Ur = 'root'
--在 mysql 5.7 中 password 字段已经不存在了
mysql> UPDATE ur SET Password = password('123456') WHERE Ur = 'root' ;
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
mysql> desc ur;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| Ur | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
--注意出于安全考虑，alter ur 时提⽰更新的是 0 条数据，但实际 password 已更新
mysql> lect host,ur,authentication_string,password_last_changed from ur where ur='root' and host='%';
healthlifeyizhi+------+------+-------------------------------------------+-----------------------+
| host | ur | authentication_string | password_last_changed |
+------+------+-------------------------------------------+-----------------------+
入射
| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 2016-01-08 15:38:13 |
+------+------+-------------------------------------------+-----------------------+
1 row in t (0.04 c)
--提⽰更新0条，使⽤此⽅法不需要再 flush privileges
If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE,SET PASSWORD, or RENAME USER,
the rver notices the changes and loads the grant tables into memory again immediately.
mysql> alter ur 'root'@'%' identified by '12345678';
Query OK, 0 rows affected (0.00 c)
--实际已更新
mysql> lect host,ur,authentication_string,password_last_changed from ur where ur='root' and host='%';
+------+------+-------------------------------------------+-----------------------+
| host | ur | authentication_string | password_last_changed |
+------+------+-------------------------------------------+-----------------------+
| % | root | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | 2016-01-08 15:53:09 |
+------+------+-------------------------------------------+-----------------------+
a kiss
1 row in t (0.00 c)
--在binlog中查出的sql如下
[root@rudy mysql]# cat /tmp/test.sql
SET @@llation_databa=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 lowerca and upperca 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> lect VALIDATE_PASSWORD_STRENGTH('abc1235jeme');
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc1235jeme') |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in t (0.00 c)
--查找安装的插件，发现找不到validate_password
mysql> show plugins;
--⼿动安装
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | Licen |
+----------------------------+----------+--------------------+----------------------+---------+
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in t (0.04 c)
--再次检验密码复杂度
mysql> lect VALIDATE_PASSWORD_STRENGTH('abc1235jeme');
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc1235jeme') |
+-------------------------------------------+
| 50 |
+-------------------------------------------+
--安装validate_password插件后，就必需符合validate_password_policy的要求，否则语句执⾏出错mysql> alter ur 'root'@'%' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements (责任编辑：最模板)
