CODEWITHSUNDEEP
mysqlsql
Ramith
Ramith

Reputation: 11

MySQL Stop a user from deleting an entire table

I have a table that contains millions of records in MySQL. Is there a way to prevent users from accidentally executing "delete from table1;" and only allow them to delete records by executing this query "delete from table1 where id=10;"

Currently the user privilege is "GRANT SELECT, INSERT, DELETE ON db1.table1 TO 'user'@'localhost'".

Thanks.

Upvotes: 1

Views: 495

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

MySQL has a server mode called sql_safe_updates. Despite the name, it applies to both updates and deletes.

When this mode is enabled, users need to provide a key in the where clause or limit for update and delete.

This can also be set in the MySQL client.

Upvotes: 1

Related Questions