1

I want to prevent user from using deleting * from table unless primary key specified, one of our team member accendently used "delete * from table_name" i want to prevent such scenarios in future.

Prateek Rathore
  • 61
  • 5
  • 2
    What and slow down every delete on every table? No proper backups is the solution. – e4c5 May 10 '17 at 12:37
  • 1
    http://stackoverflow.com/questions/7595714/how-to-write-a-trigger-to-abort-delete-in-mysql – Kuba Wyrostek May 10 '17 at 12:37
  • Do not use a trigger. e4c5 is correct and you need proper backups and sensible database access security. – Milney May 10 '17 at 14:05
  • Revoke delete privileges and create a stored proc to delete from the table.. trigger cant be used to check if the WHERE is been used in the DELETE query – Raymond Nijland May 10 '17 at 15:26

1 Answers1

0

Would safe updates be viable for you? This is an option you can enable on the command line, in the option file or set a variable in SQL code that prevents updates and deletes without a where clause that includes the key columns defining the rows to change.

In MySQL Workbench there is a setting in Preferences -> SQL Editor -> Safe Updates (rejects UPDATEs and DELETEs with no restriction). I believe this is even on by default.

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181