Prevent users deleting more than a certain number of rows in MySQL

Question

The problem:

• I have a table with rows, nothing special about it
• When a database user removes a row, it should increment a number
• A database user is only allowed to remove 3 rows max in the table
• A database administrator should be able to add more "remove tokens" to a database user

I know I should have a BEFORE DELETE trigger to see if the user can remove a row, and an AFTER DELETE trigger to increase the number of rows deleted by the user.

How should I store the info about how many rows a database user has removed from that specific table, and how do I allow a database admin to add more "remove tokens" to the user.

I thought of creating a table to store this, but I wonder if there are different ways to do it without a table? This might sound like a weird question, but I'm just trying to figure out how more advanced stuff works.

Answer 1

With this kind of granularity, you need to have a rather elaborate setup.

I was thinking of suggesting several things

SUGGESTION #1 : Use Stored Procedure for Specialized DML

Having to micromanage the number of DELETEs per user to mitigate DML should not become a server-centric function for DELETEs against a specific table. That should remain an application-centric function. Therefore, create a Stored Procedure that can manage the number of DELETEs.

SUGGESTION #2 : Use the BLACKHOLE storage engine

Any audit trail of DELETEs using a real table can create a localized disk bottleneck that would be a little clumsy to throttle. If the audit trail was recorded in a BLACKHOLE table, that would significantly reduce any disk I/O issues that would otherwise rear its ugly. If you write to a BLACKHOLE table, where would the audit trail data be written? That's leads up to...

SUGGESTION #3 : Use MySQL Replication

How would MySQL Replication help? Suppose the table is called mydb.audittrail. If the mydb.audittrail uses BLACKHOLE on the Prod Server, it could replicate to a Slave that that has mydb.audittrail as MyISAM. That would be the location of the actual audit data. The only drawback would be added disk I/O for writing audit trail info to the binary logs. You can further reduce that by having the binary logs stored on SSD or a RAM disk.

Please see my answer to Is it normal to use many Triggers? for further info on this concept of audit trail recording with the BLACKHOLE Storage Engine and MySQL Replication.