12

I'm using MySQL 5.0 and I would like to know if there's a way to disable deletes on a table. As in, not make it possible for ANY user to delete anything from the tablets, only update and insert.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Skynight
  • 507
  • 2
  • 7
  • 24
  • 2
    yes. Privileges Provided by MySQL http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html –  Oct 30 '11 at 22:21
  • You can certainly make it using the command given here: http://forums.mysql.com/read.php?10,290555,290778#msg-290778 – lc2817 Oct 30 '11 at 22:22

4 Answers4

23

Here's an example of a trigger:

DELIMITER $$

CREATE TRIGGER tr_table1_del BEFORE DELETE ON table1 FOR EACH ROW
BEGIN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'DELETE cancelled'; 
END $$

DELIMITER ;
SuperShoot
  • 9,880
  • 2
  • 38
  • 55
costamatrix
  • 670
  • 8
  • 17
9

Yes, see the MySQL manual for the GRANT syntax

Here is an example of what you want:

GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

Which gives only SELECT and INSERT privilages to a specific user/host on a specified table.

Community
  • 1
  • 1
Aiden Bell
  • 28,212
  • 4
  • 75
  • 119
8

You can use grant as proposed by others. Or you can create BEFORE DELETE trigger that raises an error, so nobody can issue delete against your table (keep in mind, it is still possible to TRUNCATE TABLE)

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • Triggers are cool. Though for something simple like rights restriction, this may result in an increase in WTF delta. – Aiden Bell Oct 30 '11 at 22:29
2

Setting permissions on your table would let you disable the delete operations. You set permissions with GRANT.

Marcus
  • 12,296
  • 5
  • 48
  • 66