0

I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them. How can I do this?

(ideally returning a suitable error message if any change is attempted).

I dont have grant privileges, so i would like to understand how to write BEFORE UPDATE trigger that raises an error (using signals or issuing a statement that always fails).

8
  • 1
    Cross posted: stackoverflow.com/questions/16634741
    – user1822
    May 19, 2013 at 12:55
  • 1
    @Lorenzo Please, don't create duplicate posts. If you already have one post on some site and it is off topic, then people will likely close it and it will be migrated to the proper site. Thanks. May 19, 2013 at 13:04
  • There's also similar question dba.stackexchange.com/questions/14745/… May 19, 2013 at 13:09
  • sorry i am new here :/
    – jo.ker
    May 19, 2013 at 13:23
  • 1
    If you're using MySQL 5.5, there's a good example of raising errors using signals here on StackOverflow (RuiDC's answer). Using MySQL's permission system seems like it would be much more straightforward, though, and I'd recommend working with whoever does have GRANT privileges to try and solve your problem that way. May 19, 2013 at 13:53

1 Answer 1

3

@NathanJolly's comment about using privileges is really the correct answer, since if a user actually has the privileges to delete or update the table, they could likely find a way to work around the suggestions below.

The tactics discussed below should be considered a "soft" immutability implementation -- it's only really good for keeping honest people honest by preventing them from doing things they can but shouldn't. It will not prevent deliberate malicious action by a user with too many permissions.

You can do this in a BEFORE UPDATE and BEFORE DELETE trigger. It won't work properly in an AFTER trigger, since the work has already been done.

In MySQL 5.5 and above:

Prevent deletion:

mysql> CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW SIGNAL
       SQLSTATE '45000' SET MESSAGE_TEXT = 'table `t1` does not support DELETE';

Attempts to delete will throw a fatal exception on the first row, stopping the query, and the row(s) in question will not be deleted.

mysql> DELETE FROM t1 WHERE id = 1;
ERROR 1644 (45000): table `t1` does not support DELETE

Prevent updates with a similar trigger:

mysql> CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW SIGNAL
       SQLSTATE '45000' SET MESSAGE_TEXT = 'table `t1` does not support UPDATE';

The MESSAGE_TEXT argument can take a string of up to 128 characters.

Prior to MySQL 5.5 you can throw an error by calling a procedure that doesn't exist. The error message will be in the form:

ERROR 1305 (42000): PROCEDURE {schema}.{nonexistent-procedure-name} does not exist.

Given this bit of common structure, you can create a nonsense procedure name using backtick escapes that will form the first part of a sentence that makes sense when the phrase "does not exist" is appended to the error message by the server.

mysql> CREATE TRIGGER t1_bd BEFORE DELETE ON table_name FOR EACH ROW 
       CALL `t1: DELETE capability for this table`;

This suggestion is bound to leave people scratching their heads until you see the error that it returns:

mysql> delete from t1 where id = 2;
ERROR 1305 (42000): PROCEDURE test.t1: DELETE capability for this table does not exist

lol... but it works. Here, your "error message" content is limited to the maximum length of a stored procedure name, which is 64 characters. The backticks make the spaces acceptable in the nonexistent procedure name.

Of course, if you're in 5.1 or previous and someone has privileges to actually create such a procedure, they can bypass this restriction by creating a procedure that does nothing, which will result in the trigger doing nothing and the delete/update succeeding.

In either case, if the user has permission to drop the triggers, the mechanism is easily bypassed in this way also... if a user can create tables, they could create a new table, INSERT ... SELECT from the old table to the new table, modify data as desired, rename the tables... so we're back to permissions as the correct solution again.

Caveat lector.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.