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.