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.
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.