A simple stupid "UPDATE table SET something=another WHERE (always true)" in accident will easily destroy everything in the database. It could be a human mistake, an SQL injection/overflow/truncation attack, or a bug in the code who build the WHERE causes.
事故中一个简单的愚蠢“UPDATE表SET something =另一个WHERE(总是为真)”将很容易破坏数据库中的所有内容。它可能是人为错误,SQL注入/溢出/截断攻击,或构建WHERE原因的代码中的错误。
Are popular databases provide a feature that protect tables by limit maximum number of row could be updated in one SQL statement?
流行的数据库是否提供了一种保护表的功能,限制最大行数可以在一个SQL语句中更新?
I mean some kind of defensive setting that apply to pre-table access right on database: no-way to bypass, less code to program, no human mistake(unless grant yourself too much access right).
我的意思是某种防御性设置适用于数据库上的表前访问权限:无法绕过,编程代码少,没有人为错误(除非授予自己太多访问权限)。
7 个解决方案
#1
You can add a trigger that checks how many rows are being updated (count the Inserted magic trigger table), and RAISEERROR if that's too many rows.
您可以添加一个触发器来检查正在更新的行数(计算插入的魔术触发器表),以及RAISEERROR(如果行数太多)。
#2
I don't know of anything.
我什么都不知道。
I'm not sure that this would solve anything. How can the database distinguish between a SQL injection attack and a nightly batch update that happens to exceed your limit?
我不确定这会解决任何问题。数据库如何区分SQL注入攻击和碰巧超出限制的每晚批量更新?
One assumption is the auto commit is set to true. If the SQL injection attack isn't committed, you always have the opportunity to roll it back, assuming that you're looking at logs and such.
一个假设是自动提交设置为true。如果未提交SQL注入攻击,您始终有机会将其回滚,假设您正在查看日志等。
I think the real answer is better layering of apps, validation, binding, etc. You can't do SQL injection if those measures are in place.
我认为真正的答案是更好地分层应用程序,验证,绑定等。如果这些措施到位,你就无法进行SQL注入。
#3
The short answer is "no"...
最简洁的答案是不”...
Oracle allows you set define profiles that can be assigned to users to limit usage of resources such as CPU, logical reads. However, it isn't intended for your purpose, it is more about managing resources in a multi-user environment.
Oracle允许您设置可以分配给用户的定义配置文件,以限制CPU,逻辑读取等资源的使用。但是,它不是为了您的目的,而是更多关于在多用户环境中管理资源。
Perhaps more importantly, it also has flashback table so that unintended changes can be easily undone.
也许更重要的是,它还具有闪回表,以便可以轻松撤消意外更改。
Most of your scenarios should be dealt with by other means:
您的大部分场景都应该通过其他方式处理:
- human mistake: most users should not be granted update privileges on tables, they should be forced to call APIs (typically via an application) to perform updates. DBAs must be very careful when accessing live databases - never mind row limits, they can drop the table altogether!
- Injection attack: these can and should be prevented from occuring
- Code bugs: these should be caught through proper testing
人为错误:大多数用户不应被授予对表的更新权限,应强制他们调用API(通常通过应用程序)来执行更新。访问实时数据库时,DBA必须非常小心 - 不要介意行限制,他们可以完全放弃表格!
注射攻击:可以而且应该防止这些发生
代码错误:这些应该通过适当的测试来捕获
If your data is important, it should be properly protected and looked after as above, and a maximum row update limit is unnecessary; if your data isn't important enough to protect as above, then why worry?
如果您的数据很重要,那么它应该得到适当的保护并按照上面的方式进行处理,并且不需要最大行更新限制;如果您的数据不够重要,无法保护,那么为什么要担心?
#4
As David B was first to point out, you can do this with a trigger. It's a good practice to start your triggers with a @@ROWCOUNT test anyway. So imagine:
正如David B首先指出的那样,你可以用触发器来做到这一点。无论如何,使用@@ ROWCOUNT测试启动触发器是一个很好的做法。想象一下:
CREATE TRIGGER dbo.trg_myTrigger_UD ON dbo.myTable FOR UPDATE, DELETE
AS
IF @@ROWCOUNT <> 1 RETURN
This would kick out any updates and/or deletes that try to affect more than one row.
这将触发试图影响多行的任何更新和/或删除。
As a general rule, I start mine with a rowcount test of <> 0. The point being if the trigger was kicked off by something that actually affected no rows (UPDATE table SET col1 = 'hey' WHERE 1 = 0) then there's no point in running through the trigger code as it won't do anything anyway.
作为一般规则,我使用<> 0的rowcount测试开始我的。关键是如果触发器被实际影响没有行的东西踢掉(UPDATE表SET col1 ='hey'WHERE 1 = 0)那么就没有了指出要运行触发器代码,因为它无论如何都不会做任何事情。
#5
I understand your reasons, but how do you want handle batches, which are legitimate?
我理解你的理由,但你想如何处理合法的批次?
If you do manualy some changes, and you want be able "undo" the changes, use transactions. If you want be able recontruct data, use archive of changes. But you are not able create check for "this is correct/incorrect batch" only from batch with 100% correct results.
如果您手动进行了一些更改,并且希望能够“撤消”更改,请使用事务。如果要重建数据,请使用更改存档。但是,您无法仅从具有100%正确结果的批次创建“这是正确/不正确的批次”的检查。
#6
You just need to write stored procedures and only expose those to users. And you will not work in a priviledged account in normal situations. Only connect as admin when needed.
您只需编写存储过程并仅向用户公开。在正常情况下,您不会在特权账户中工作。仅在需要时以管理员身份连接。
#7
You can wrap the update in a transaction and prompt the user (letting them know how many row are going to be updated) before committing.
您可以在事务中包装更新并在提交之前提示用户(让他们知道要更新的行数)。
#1
You can add a trigger that checks how many rows are being updated (count the Inserted magic trigger table), and RAISEERROR if that's too many rows.
您可以添加一个触发器来检查正在更新的行数(计算插入的魔术触发器表),以及RAISEERROR(如果行数太多)。
#2
I don't know of anything.
我什么都不知道。
I'm not sure that this would solve anything. How can the database distinguish between a SQL injection attack and a nightly batch update that happens to exceed your limit?
我不确定这会解决任何问题。数据库如何区分SQL注入攻击和碰巧超出限制的每晚批量更新?
One assumption is the auto commit is set to true. If the SQL injection attack isn't committed, you always have the opportunity to roll it back, assuming that you're looking at logs and such.
一个假设是自动提交设置为true。如果未提交SQL注入攻击,您始终有机会将其回滚,假设您正在查看日志等。
I think the real answer is better layering of apps, validation, binding, etc. You can't do SQL injection if those measures are in place.
我认为真正的答案是更好地分层应用程序,验证,绑定等。如果这些措施到位,你就无法进行SQL注入。
#3
The short answer is "no"...
最简洁的答案是不”...
Oracle allows you set define profiles that can be assigned to users to limit usage of resources such as CPU, logical reads. However, it isn't intended for your purpose, it is more about managing resources in a multi-user environment.
Oracle允许您设置可以分配给用户的定义配置文件,以限制CPU,逻辑读取等资源的使用。但是,它不是为了您的目的,而是更多关于在多用户环境中管理资源。
Perhaps more importantly, it also has flashback table so that unintended changes can be easily undone.
也许更重要的是,它还具有闪回表,以便可以轻松撤消意外更改。
Most of your scenarios should be dealt with by other means:
您的大部分场景都应该通过其他方式处理:
- human mistake: most users should not be granted update privileges on tables, they should be forced to call APIs (typically via an application) to perform updates. DBAs must be very careful when accessing live databases - never mind row limits, they can drop the table altogether!
- Injection attack: these can and should be prevented from occuring
- Code bugs: these should be caught through proper testing
人为错误:大多数用户不应被授予对表的更新权限,应强制他们调用API(通常通过应用程序)来执行更新。访问实时数据库时,DBA必须非常小心 - 不要介意行限制,他们可以完全放弃表格!
注射攻击:可以而且应该防止这些发生
代码错误:这些应该通过适当的测试来捕获
If your data is important, it should be properly protected and looked after as above, and a maximum row update limit is unnecessary; if your data isn't important enough to protect as above, then why worry?
如果您的数据很重要,那么它应该得到适当的保护并按照上面的方式进行处理,并且不需要最大行更新限制;如果您的数据不够重要,无法保护,那么为什么要担心?
#4
As David B was first to point out, you can do this with a trigger. It's a good practice to start your triggers with a @@ROWCOUNT test anyway. So imagine:
正如David B首先指出的那样,你可以用触发器来做到这一点。无论如何,使用@@ ROWCOUNT测试启动触发器是一个很好的做法。想象一下:
CREATE TRIGGER dbo.trg_myTrigger_UD ON dbo.myTable FOR UPDATE, DELETE
AS
IF @@ROWCOUNT <> 1 RETURN
This would kick out any updates and/or deletes that try to affect more than one row.
这将触发试图影响多行的任何更新和/或删除。
As a general rule, I start mine with a rowcount test of <> 0. The point being if the trigger was kicked off by something that actually affected no rows (UPDATE table SET col1 = 'hey' WHERE 1 = 0) then there's no point in running through the trigger code as it won't do anything anyway.
作为一般规则,我使用<> 0的rowcount测试开始我的。关键是如果触发器被实际影响没有行的东西踢掉(UPDATE表SET col1 ='hey'WHERE 1 = 0)那么就没有了指出要运行触发器代码,因为它无论如何都不会做任何事情。
#5
I understand your reasons, but how do you want handle batches, which are legitimate?
我理解你的理由,但你想如何处理合法的批次?
If you do manualy some changes, and you want be able "undo" the changes, use transactions. If you want be able recontruct data, use archive of changes. But you are not able create check for "this is correct/incorrect batch" only from batch with 100% correct results.
如果您手动进行了一些更改,并且希望能够“撤消”更改,请使用事务。如果要重建数据,请使用更改存档。但是,您无法仅从具有100%正确结果的批次创建“这是正确/不正确的批次”的检查。
#6
You just need to write stored procedures and only expose those to users. And you will not work in a priviledged account in normal situations. Only connect as admin when needed.
您只需编写存储过程并仅向用户公开。在正常情况下,您不会在特权账户中工作。仅在需要时以管理员身份连接。
#7
You can wrap the update in a transaction and prompt the user (letting them know how many row are going to be updated) before committing.
您可以在事务中包装更新并在提交之前提示用户(让他们知道要更新的行数)。