Blog

Bypass TRIGGERS

Bypass TRIGGERS

The content here is not new. I have written about same before many years ago (I think in our old Blog system that is now offline for 4+ years).  But I feel like to restate my point of view after listening to the latest OurSQL Episode. I was a little excited if Sheeri and Gerry would express similar concerns in their podcast, but they did not.

This is my concern: You may build an application on top of a database schema using TRIGGERS. And it may work perfectly as long as the database is only accessed from the application. However sooner or later it will likely happen that some maintenance or other database manipulations is required that was not foreseen when the application was written, and thus the application cannot handle it and you will need to connect with another client. It may be a problem because once you start doing manipulations from outside the application you may fire TRIGGERS that you don’t want to fire in the context (or TRIGGERS you forgot about or overlooked because you did not code the application yourself). And this could easily result in irreversible harm to data.

I remember that at least 3 different ways to implement support for this have been proposed over time -  mostly in the MySQL bugs system as ‘feature requests’. Actually it took only very short time after TRIGGERS were introduced in MySQL before request for various ways to bypass TRIGGERS occured. And none of it has yet been considered in MySQL development. The list (possibly incomplete) is:

1) A SESSION variable turning off TRIGGERS for the session (“SET @@session.triggers = OFF”)

2) Include or Exclude users in the TRIGGER definition so that a TRIGGER is fired or not fired depending on what user manipulates the table ON which the TRIGGER is DEFINED.  (“CREATE TRIGGER .. INCLUDE|EXCLUDE user@host [,user@host]…”)

3) a ‘NOTRIGGER’ keyword to be used in INSERT, UPDATE or DELETE statements.

Frankly I don’t know if other RDBMS’s have such options or even if it would be compliant with SQL standards and various auditing requirements or not.  But I would find it highly useful.

As long as some option like this is not there, I am a little scared of using TRIGGERS – or not trigger-happy, so to say. And if I should use them I would at least consider to wrap all statements between BEGIN and END in a condition testing for CURRENT_USER being the application user the database schema was designed for and not any other user. However if you want to use this ‘hack’ and if you also manipulate data from inside Stored Procedures be aware of how the SQL_SECURITY setting for the procedure affects CURRENT_USER returned from inside a Stored Procedure – refer docs on this.

13 thoughts on “Bypass TRIGGERS

  1. Hi Peter,

    yeah – it’s a great point. And AFAIK other RDBM-es do have a feature like this: Oracle (http://docs.oracle.com/cd/B28359_01/server.111/b28310/general004.htm), MSSQL (http://msdn.microsoft.com/en-us/library/ms189748.aspx)

    But let’s talk about the other way around: trigger firing in response to “automatic” updates / deletes through a foreign key. See http://bugs.mysql.com/bug.php?id=11472. Reported in 2005. Triaged as serious. “We will fix this in 5.1″. Right.

  2. If someone adds triggers then they are there for a reason. Often to do extra things that you do not want the application to worry about (auditing or logging of changes to a log table are simple such examples). Yes, these are not directly visible to the user, but that is partly intended.

    So if you go and look at a new database and want to start doing things on it you should really understand how it is built. Disabling triggers should NOT be possible without the appropriate rights as possibly the person who created those triggers put them there for a reason and does NOT want them disabled. So to disable triggers you probably should be someone with “elevated privileges”. In other RDBMSes you’d be the table owner in which you can see and manipulate the triggers as needed as you’d have the right privileges. If you were not in that position and had not been given the right to disable triggers then you should not be doing it anyway.

    So I can see moments when it would be convenient to be able to disable triggers (a large ALTER table comes to mind) but only in an environment as a DBA when you have the privileges to do that and it makes sense. Making it easy for anyone to do this would in many cases completely nullifies the point of having the triggers in the first place and the comments in your post do not make it clear under what circumstances you want to make this easy.

  3. I will reply to all posts after Xmas (and feel free to discuss here for as long). And for completeness there is a tweet as well: http://twitter.com/ruirrib/status/282144991399251968

    Here and now just a quick comment to Simon: Indeed there is a privilege concern/discussion. But I think (I have not tried) that something like this should work for bypassing a trigger for a specific user:

    CREATE TRIGGER `trigger_sometimes_only` …..
    BEGIN
    IF CURRENT_USER <> ‘notriggeruser’@’somehost’
    – SQL statements here
    ENDIF
    END

    Am I right? In that case any user with TRIGGER privilege can currently allow any user to bypass a TRIGGER.

      • @Jan .. I think rather that everything executed from inside a TRIGGER belongs (and should belong) to the same transaction as ‘parent statement’ firing the trigger. What I also believe that the server takes care of.

        But maybe I did not understand your point?

  4. Peter,

    Within a trigger, CURRENT_USER() relates to the definer of the trigger, and USER() relates to the invoker. So CURRENT_USER() is static.

    At least that’s what I found out a few years ago, I’m assuming unchanged.

  5. @Shlomi .. you are right.

    This was tested on MySQL 5.6.9:

    – as ‘root’@’localhost’
    CREATE DATABASE trigtest;
    USE trigtest;
    CREATE TABLE trig (id INT);

    DELIMITER &&
    CREATE TRIGGER `trig`
    AFTER INSERT ON `trigtest`.`trig` FOR EACH ROW
    BEGIN
    SET @cuser = CURRENT_USER();
    SET @user = USER();
    END &&

    DELIMITER;

    CREATE USER ‘testtrig’@’localhost’;
    GRANT INSERT ON `trigtest`.`trig` TO ‘testtrig’@’localhost’;

    – as ‘testtrig’@’localhost’
    INSERT INTO `trigtest`.`trig` VALUES (1);
    SELECT @cuser; — returns “root@localhost”
    SELECT @user; — returns “testtrig@localhost”

    .. I wonder if this is documented anywhere!

  6. hmmm .. one more detail :-)

    IF USER() <> ‘notriggeruser’@’somehost’
    .. won’t work – but
    IF USER() <> ‘notriggeruser@somehost’
    – will

    Test case for this:
    SELECT USER(); — returns “root@localhost”
    SELECT (USER() = ‘root@localhost’); — returns “1″
    SELECT (USER() = ‘root’@’localhost’); — 1064 syntax error

    USER() (and CURRENT_USER and CURRENT_USER() and SESSION_USER()) return a string. This is inconsistent with how you define users where the user-part and the host-part are individual strings themselves (CREATE USER ‘someuser’@’somehost’;). But I had enough of reporting such inconsistencies in MySQL! Anyway ..

    .. to define a trigger in such a way that the trigger won’t fire for a specific user:

    DELIMITER ||
    CREATE TRIGGER `trigger_sometimes_only` ..
    BEGIN
    IF USER() <> ‘notriggeruser@somehost’
    – SQL statement(s) here;
    ENDIF;
    END ||
    DELIMITER ;

  7. I found another ‘hack’. It is (with InnoDB) possible to to set a SAVEPOINT in TRIGGER code and roll back to same inside the TRIGGER like this

    DELIMITER $$

    CREATE DEFINER= `root`@`localhost` TRIGGER `trigtest`.`tricky`
    BEFORE INSERT ON `trigtest`.trig
    FOR EACH ROW BEGIN
    – set a savepoint
    SAVEPOINT trickier;
    INSERT INTO trig2 VALUES (1);
    – here you may test for a condition and if you want:
    ROLLBACK TO SAVEPOINT trickier;
    END $$

    DELIMITER ;

    TRUNCATE TABLE trig2;
    INSERT INTO trig VALUES (1);
    SELECT * FROM trig2;
    – and see that the trigger did not insert anything. The table is empty.

    (but I don’t guarantee this escept for above simple test case on my environment. Documentation does not tell that this is possible, but also not that it is not. It could very well be some weird ‘edge case’ that nobody have considered before. You cannot START TRANSACTION (or anything that causes an implicit COMMIT) in a TRIGGER, so I actually wonder inside what transaction the trigger code is executing!)

  8. That *is* a great point. We explained if the code doesn’t do the exact thing, the trigger won’t fire (for example, a DELETE trigger won’t fire on a TRUNCATE). Your example is excellent, although it’s more a point for stored procedures/functions than for triggers – if the actual stored procs/functions aren’t called, the desired results won’t appear.

    With triggers, as long as you manipulate the data with INSERT/UPDATE/DELETE/REPLACE, it doesn’t matter whether you’re accessing it through the application or not. Which does speak to your point about unintended consequences. We didn’t specifically make the point that when a DBA is doing maintenance, they might not want the trigger to run, and we should have made that point, including explaining that there isn’t an easy “turn off triggers” like there is for binary logging (I like that idea, for sessions).

    We try hard to avoid spreading myths. For example, what if the trigger is an auditing trigger? You don’t want those to be application-only. We try to encourage people to think, and we hope that by explaining how triggers work, factually, people will understand that an INSERT trigger will fire whenever an INSERT on the table is run, whether that INSERT is through the application or by a DBA.

    As for your examples, did you actually try them? I believe the trigger is run *as* the user it’s defined as, so wouldn’t checking the current user is the same as the definer always be true? The manual says “Triggers and events have no SQL SECURITY characteristic and always execute in definer context.” – http://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html And I’d guess this is why it’s not easy to just “turn off” triggers for a session or for a user, because the trigger is fired when a change happens on the table, it doesn’t care who the session or user is.

  9. @Sheeri .. I don’t think you checked the discussion I and Shlomi had and my correction following that? And yes – I have tried.

    Let us take a simple example:

    We have a table ‘tab’, a trigger ‘trig’ and two users ‘sheeri’ and ‘peter’. ‘trig’ is defined “BEFORE INSERT ON tab” and DEFINER for ‘trig’ is ‘peter’. Now ‘sheeri’ executes an INSERT to ‘tab’ (what she has privilege to do) and ‘trig’ will fire (running with privileges of ‘peter’). Now inside ‘trig’ CURRENT_USER() will return ‘peter’ (as documented), but USER() (or the synonym SESSION_USER()) will return ‘sheeri’. So inside the trigger you can have a CASE/IF condition for USER() and execute different code for different users or even execute nothing for some users – what effectively bypasses the TRIGGER for the/those user(s). To do so you will (only) need to be able to create TRIGGERS – or in other words: you will need the TRIGGER privilege.

    In conclusion: any user with TRIGGER privilege can allow any user to bypass a TRIGGER by adding such condition in the TRIGGER-code. But a more direct way would be preferable, I think.

    Also I don’t find this behavior of USER() inside a TRIGGER properly documented. Maybe this is not even intentional, but rather a lapse in the server code. I posted the bug report for clarification on that.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>