Prevent users from removing MySQL triggers set by different user

Question

Im currently setting up a database system with a lot of different users, having access to only limited views, and tables of the database system.

Now I need to create several triggers as the root user, to prevent some actions. But all the users should be able to create a trigger for a database created for them and the tables within. This is working fine since every user has database-specific privileges. Sadly this does allow for some reason the user to delete triggers set by the root user on their database.

If I have database 'A' with table 'test'. I create a trigger as root user for database 'A' table 'test'. Now user 'someone' has privileges to create triggers for database 'A', but he should NOT be able to remove any trigger set by the root account on database 'A'. Sadly he can remove triggers created by root... anyone know how to fix this for MySQL?

Here is the privileges for the user for the specific database:

Now the query executed by the root user:

Result in with SHOW TRIGGERS executed by user 'someone' on database 'A':

Execution of DROP TRIGGER by user 'someone' on database 'A':

Why can the user remove this trigger? It's not created by him but root... Also for anyone asking, the query 'SELECT CURRENT_USER();' returns 'someone@localhost' and NOT 'root@localhost', i have activly switched accounts.

Answer 1

If you grant a user the TRIGGER privilege to create triggers on a given table, you grant them all the operations that privilege covers, which includes both create and drop

https://dev.mysql.com/doc/refman/8.0/en/drop-trigger.html says:

DROP TRIGGER requires the TRIGGER privilege for the table associated with the trigger.

It doesn't matter who defined the trigger. MySQL generally has no concept of ownership for database objects like tables or triggers.

You are going to have to think of a different design that does not require users to be disallowed this access.