Question 20632 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search