Mysql preventing truncate and drop table statements for a specific table

MySQLpermissions

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.

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

Related Solutions

Mysql – Difference between DROP and TRUNCATE

DELETE - DATA MANIPULATION LANGUAGE(DML)

The DELETE statement in any RDBMS is considered a DML statement. Also known as CRUD (Create, Read, Update, Delete), this kind of statement is intended to manipulate data in a database without affecting the underlying structure of the objects. What this means in practical term is:

A DELETE statement can be fine-tuned using a predicate via either WHERE or JOIN to delete some or all rows in a table.
A DELETE statement will be logged by the database and can be rolled back within a transaction if the statement fails.
Typically a DELETE will take row level locks on the data it is deleted, though this could escalate higher as necessary.
Because of the transactional overhead, DELETE can be "slow" (this is relative), but safer because it is fine-grained.

TRUNCATE - DATA DEFINITION LANGUAGE(DDL)

TRUCNATE is considered a DDL statement, meaning that it is intended to alter how objects are defined in a database. Usually, DDL statements are CREATE, ALTER, or DROP, but TRUNCATE serves a particular purpose, that of "resettting" a table by removing all the rows. The methods of this differ between the RDBMS engines and I would recommend looking at the specifics of MySQL. The practical implications of a TRUNCATE are:

TRUNCATE can not be fine grained. If successful, it will remove all the rows from your table.
TRUNCATE is typically not logged. This varies by RDBMS and I would suggest you looks more specifically at how MySQL handles it. (Hint, it varies by version.)
TRUNCATE requires a table metadata lock to execute. How this is actually implemented can be specific to RDBMS, but essentially the TRUNCATE process must prevent other processes from mucking with the table in order to execute its DDL.
Because it is (typically) not logged and does not use predicates, a TRUNCATE will be faster than a DELETE, but less safe.

DROP TABLE - DATA DEFINITION LANGUAGE(DDL)

DROP TABLE goes further than a TRUNCATE in that it actually removes the table from the database completely. This includes removing all associated objects, such as indexes and constraints. If you dropped a table, not only would you remove all the data, but the structure as well. This will usually be done when a table is no longer needed. The primary concern is that since a DROP is DDL, you typically can't undo it. Some RDBMS engines will allow you to wrap DDL within a transaction so you can roll it back, but this is not considered best practice and should be avoided.

MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Disable binary logging in your session, drop the table, and enable binary logging.

SET sql_log_bin = 0;
DROP TABLE IF EXISTS ... ;
SET sql_log_bin = 1;

This works because if the Slaves do not see the DROP TABLE command registered in the Master's binary logs, it cannot run the DROP TABLE.

I have recommended using SET sql_log_bin = 0; before in my earlier posts

Nov 12, 2012 : Mysql replication and ignore tables
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Jul 11, 2011 : ALTER TABLE on a large table with an indexed column (This is where I use SET sql_log_bin = 0; to directly prevent data from being written to Slaves)

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Difference between DROP and TRUNCATE

MySQL: Prevent DROP TABLE and TRUNCATE TABLE on Slaves

Related Question