专栏首页SEian.G学习记录MySQL 5.7&8.0开启sql_safe_updates安全模式的差异

MySQL 5.7&8.0开启sql_safe_updates安全模式的差异

不知道大家是否有过维护的数据库表业务数据被人或者因为程序bug导致全表更新,全表删除的痛苦经历,恢复业务数据的过程真的太痛苦了,尤其与交易和钱相关的数据,必须恢复成和原来一模一样,那能不能在数据库层面架起最后一道安全堡垒,拒绝全表更新,全表删除的非法操作呢,答案是有的,在mysql中sql_safe_updates可以完美解决这个问题;

MySQL数据库是可以开启安全模式,不过默认情况下,安全模式不开启的,下面就来说说什么是mysql的安全模式,如下是sql_safe_updates参数的官方解释:

If this variable is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error. This makes it possible to catch UPDATE and DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is OFF.

sql_safe_updates默认是不开启的

mysql> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | OFF |
+------------------+-------+
1 row in set (0.01 sec)

现在就开启这个参数,如果要永久生效,需要将参数添加到数据库配置文件(my.cnf)中

mysql> set global sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

注意:需要重新连接一下数据库,才会生效;

下面就来给大家演示一下实际效果,以及在MySQL 5.7以及8.0版本在开启该参数之后进行更新和删除操作的差异;

mysql> CREATE TABLE `t_test1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
插入语句
insert into t_test1 values(NULL,'test1',21);
insert into t_test1 values(NULL,'test2',22);
insert into t_test1 values(NULL,'test3',23);
insert into t_test1 values(NULL,'test4',24);
insert into t_test1 values(NULL,'test5',25);
insert into t_test1 values(NULL,'test6',26);
insert into t_test1 values(NULL,'test7',27);
insert into t_test1 values(NULL,'test8',28);

测试一下全表删除

mysql> delete from t_test1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
 
mysql> delete from t_test1 where 1=1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

从上面的结果看,全部被数据库安全策略拦截了 再来测试一下更新

mysql> update t_test1 set name='test';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
 
mysql> update t_test1 set name='test' where 1=1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

不带条件的更新也被拦截,那测试一下正常带条件的更新和删除看看效果

mysql> update t_test1 set name='test' where name='test1';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
 
mysql> delete from t_test1 where name='test2';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

为什么会这样呢,正常更新或者删除一条记录也会被mysql数据库安全策略拦截了呢?

下面针对update操作以及delete操作不同场景下的报错以及执行条件测试:

1、update操作

a、报错条件:不带where、带where无索引、where条件为常量 (1)不带where:update t_test1 set name=’test’; (2)带where无索引:update t_test1 set name=’test’ where name=’test1′; (3)where条件为常量:update t_test1 set name=’test’ where 1=1; b、执行条件:带where带索引、不带where+带limit、带where无索引+limit、带where有索引+limit、where条件为常量+limit (1)带where带索引:update t_test1 set name=’test’ where age=21; (2)不带where+带limit:update t_test1 set name=’test’ limit 1; (3)带where无索引+limit:update t_test1 set name=’test’ where name=’test1′ limit 1; (4)带where有索引+limit:update t_test1 set name=’test’ where age=21 limit 1; (5)where条件为常量+limit:update t_test1 set name=’test’ where 1=1 limit 1;

2、delete操作

相对于update,delelte的限制会更为严格;并且在5.7和8.0版本存在一点差异。

a、报错条件:不带where、带where无索引、不带where+带limit、where条件为常量、where条件为常量+limit 不带where:delete from t_test1; 带where无索引:delete from t_test1 where name=’test’; where条件为常量:delete from t_test1 where 1=1; b、执行条件:带where带索引、带where无索引+limit、带where有索引+limt 带where带索引:delete from t_test1 where age=22; 不带where+带limit:delete from t_test1 limit 1; (5.7版本执行会报错,8.0版本是可以执行成功的) 带where无索引+limit:delete from t_test1 where name=’test’ limit 1; 带where有索引+limit:delete from t_test1 where age=22 limit 1; where条件为常量+limit:delete from t_test1 where 1=1 limit 1;(5.7版本执行会报错,8.0版本是可以执行成功的)

大家重点关注上面粉色的条件(delete from t_test1 limit 1)和(delete from t_test1 where 1=1 limit 1;)这两种在5.7和8.0的执行情况是不一样的,在5.7版本,满足报错条件,会执行失败;而在8.0版本,满足执行调整,可以执行成功;


不知大家在使用MySQL Workbench操作数据库的时候是否注意到,Workbench默认是开启安全模式的,否则你的删除操作就会出现类型如下的报错信息:

从具体的报错信息中,也是可以看到原因以及解决方案:Edit–>Perferences–>SQL Editor

在实际通过workbench连接生产环境数据库的时候,是强烈不建议关闭的,避免人为导致全表删除或全表更新的操作;

总结

如果设置了sql_safe_updates=1,

update语句必须满足如下条件之一才能执行成功

1)使用where子句,并且where子句中列必须为索引列 2)使用limit 3)同时使用where子句和limit(此时where子句中列可以不是索引列)

delete语句,5.7和8.0版本有些差异,必须满足如下条件之一才能执行成功

5.7版本: 1)使用where子句,并且where子句中列必须为索引列 2)同时使用where子句和limit(此时where子句中列可以不是索引列) 8.0版本: 1)使用where子句,并且where子句中列必须为索引列 2)使用limit 3)同时使用where子句和limit(此时where子句中列可以不是索引列)

在生产环境中,建议开启该参数设置,这样不仅可以避免全表更新或删除的操作,也可以引导开发人员为where条件过滤的字段添加索引等;

本文分享自微信公众号 - DBA的辛酸事儿(dbabitter),作者:SEian.G

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-06-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 如果要升级到MySQL 8.0,可以分几个阶段走

    最近在推一些业务迈入MySQL 8.0,对很多同学来说,好像差别不大,对于我们来说却是一个质的变化,光看版本就能够感受出来,5.7-> 8.0,所幸我们3年前...

    jeanron100
  • MySQL性能基准测试对比:5.7 VS 8.0

    版权声明:本文由腾讯云数据库产品团队整理,页面原始内容来自于severalnines英文官网,若转载请注明出处。翻译目的在于传递更多全球最新数据库领域相关信息,...

    腾讯云数据库 TencentDB
  • MySQL性能基准测试对比:MySQL 5.7与MySQL 8.0

    版权声明:本文由腾讯云数据库产品团队整理,页面原始内容来自于db weekly英文官网,若转载请注明出处。翻译目的在于传递更多全球最新数据库领域相关信息,并不意...

    腾讯技术工程官方号
  • MySQL 8.0复制新特性

    截止2017年8月,MySQL 8.0 仍然是 beta 版本,复制功能有一些很棒的改进。最初,这些改进是为组复制(GR)开发的,但由于 GR 在底层使用常规复...

    wubx
  • 前沿观察 | MySQL性能基准测试对比:5.7 VS 8.0

    ? 点小蓝字加关注! 版权声明:本文由腾讯云数据库产品团队整理,页面原始内容来自于severalnines英文官网,若转载请注明出处。翻译目的在于传递更多全球...

    腾讯云数据库 TencentDB
  • MySQL性能基准测试对比:MySQL 5.7与MySQL 8.0

    ? 版权声明:本文由腾讯云数据库产品团队整理,页面原始内容来自于db weekly英文官网,若转载请注明出处。翻译目的在于传递更多全球最新数据库领域相关信息,...

    腾讯技术工程官方号
  • 好文 | Zabbix监控 MYSQL,Mysql 版本基准性能比较,Mysql8.0主主配置

    Zabbix监控Mysql | Mysql 5.7,8.0基准性能比较,Mysql8.0主主配置

    Zabbix
  • MySQL 8.0复制新特性

    截止目前(2017年8月),MySQL 8.0 仍然是 beta 版本,复制功能有一些很棒的改进。最初,这些改进是为组复制(GR)开发的,但由于 GR 在底层使...

    wubx
  • MySQL 5.7和MySQL 8.0的4个细节差异

    在这些年的MySQL升级需求中,让我大跌眼镜的一个现象是:驱动业务从MySQL 5.5升级到MySQL 5.7的很大一个因素是因为JSON这个特性。

    jeanron100
  • 人生呀,如梦,MYSQL 8.0.15-5

    人生呀,如梦,我觉得只要不投降,就是成功。(周星驰的电影都是多年后成为经典的,这也许就是一种成功)。MYSQL 其实作为数据库产品,对比其他数据库产品(不管收费...

    AustinDatabases
  • Facebook将MySQL升级至8.0

    Facebook 使用了大量的MySQL以支持他们最重要的工作。并且他们积极开发了许多MySQL 中的新功能,以支持不断发展的需求。这些更改特性发生在 MyS...

    MySQLSE
  • MySQL 8支持文档存储,并带来性能和安全方面的改进

    程序你好
  • MySQL多源复制之复制过滤

    在上一篇文章《深入了解MySQL多源复制》中,介绍了MySQL多源复制的相关内容,本文将继续讲解MySQL多源复制,主要内容是过滤复制以及在已有复制过滤配置中新...

    SEian.G
  • MySQL 8.0中的新增功能

    原文:https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available/

    shaonbean
  • 通过yum在centos安装mysql并配置远程登录

    前天按照Oracle上的文档装了一遍mysql,选了最新8.0的版本,后来出现一些问题,网上搜答案,出来的基本还是5.x版本的解决方案,并不适用8.0版本。然后...

    _淡定_
  • centos通过yum安装mysql

    前天按照Oracle上的文档装了一遍mysql,选了最新8.0的版本,后来出现一些问题,网上搜答案,出来的基本还是5.x版本的解决方案,并不适用8.0版本。然后...

    _淡定_
  • MySQL企业版线上专场 | 三合一精华版

    2020年4月7~9日,MySQL团队与3306π社区联合举办大型线上活动,为广大MySQL的爱好者和使用者带来一场MySQL技术盛宴。活动期间,叶金荣老师每晚...

    MySQLSE
  • docker安装mysql

    我对比了一下。发现第一个是mysql官方推出的,而第二是docker自带library推出的。那么我肯定选择mysql官方推出的mysql了。

    魔王卷子
  • MySQL 8.0 新特性之Hash Join

    上周在公司做了针对MySQL 8.0新特性相关的分享,提到MySQL 8.0新特性,不得不提到的就是HashJoin,MySQL一直被人诟病没有实现HashJo...

    SEian.G

扫码关注云+社区

领取腾讯云代金券