Is there any way to prevent a DELETE/UPDATE query to use the primary key greater than/less than comparison?

Question

In MySQL/MariaDB there's the SQL_SAFE_MDOE that prevents you to run a query without using a primary key in the where clause.

But even with that flag on I could run a query such as

DELETE FROM table WHERE id > 0;

And that would pretty much have the same effect as

DELETE FROM table

So, is there any way to be more restrict? Thanks in advance

Answer 1

You could implement a stored procedure that deletes in a manner you specify, and let the user call the procedure.

Then revoke DELETE privilege on that table from the user, so they can't delete directly. They would have to call the procedure.

The SQL_SAFE_MODE can always be circumvented, either by changing the session variable, or by using a WHERE clause as you show, or by using a LIMIT clause.

Answer 2

See the documentation for the purpose of the sql_safe_updates.

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). Safe-updates mode is helpful for cases when you might have issued an UPDATE or DELETE statement but forgotten the WHERE clause indicating which rows to modify.

If you want to restrict the deletes based on your logic, you can create a delete trigger.