1

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.

2
  • 1
    You can move ALL logic of working with this table and its data into stored procedures (with definer of root or another login, and sql security as definer) and revoke all rights to that table from web app users at all (or grant SELECT only, for example). None operation absent in SP can be performed. And triggers will not be needed too. And no admin notice needed - no way to delete data.
    – Akina
    Aug 23, 2018 at 8:55
  • Be cautious about REVOKE -- If it was not explicitly GRANTed, I think REVOKE silently ignores you.
    – Rick James
    Aug 31, 2018 at 3:52

1 Answer 1

1

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.

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.