Mysql preventing truncate and drop table statements for a specific table

Posted on

Question :

I have a MySQL table that serves as an immutable event store. I added triggers to prevent rows from being updated or deleted;

CREATE TABLE t2 (
  str VARCHAR(32),
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)
);


CREATE TRIGGER stop_update BEFORE UPDATE ON t2
FOR EACH ROW
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UPDATE not allowed!';

CREATE TRIGGER stop_delete BEFORE DELETE ON t2
FOR EACH ROW
  SIGNAL SQLSTATE '45007' SET MESSAGE_TEXT = 'DELETE not allowed!';

That part works fine.
But I am unable to prevent the TRUNCATE TABLE or DROP TABLE statements on that specific table.

MySQL doesn’t support DDL triggers. Currently I have revoked the drop and truncate permissions for the web app user.

But I would like to add some sort of warning/notice/tripple confirmation dialog thing for the root user as well, just in case someone down the lane accidentally/mistakenly/intentionally tries to clear/drop the table.

Is it possible to add some kind of stored procedure [or anything] in MySql that can prevent a drop/truncate on a specific table? Or at least can warn that this is a special table and should not be removed or cleared.

Answer :

Consider putting things like that is a separate database. Then GRANT users access to their database(s) with ‘full’ permissions (or whatever) and GRANT very limited access to this db:

GRANT SELECT ON Specialdb.* TO ...

If you find you need to let them do something less safe, create a Stored Procedure with security=Definer. The definer could be root and you can do whatever you want. However, since you control the proc, you control what things they are allowed to do.

Leave a Reply

Your email address will not be published.