As people should know, MySQL 5.0 introduced several new features, with triggers among them. However, creating triggers require a user with SUPER privilege, which is not something the database administrator is willing to spread easily.

The full story of how I came to this solution and how it works can be found in my blog (off site). The gist of it is that a normal user can request a trigger creation or removal by filling a table record, and this program, running as a cron job, will check if the request is appropriate and if so it will comply with the user’s request.

The source code follows.


#!/usr/bin/perl

# addtriggers
#
# Device to add triggers on demand in databases assigned
# to users without SUPER privilege.
#
# It is intended to be run as a cron job from a user with
# SUPER privileges and full access to all the concerned databases.
#
# Set username and password for this user in a ~/.my.cnf file
# under the label [trigger_creator]
#
# See the docs at http://datacharmer.blogspot.com/

package main;
use strict;
use warnings;
use English qw( -no_match_vars );
use Data::Dumper;
use DBI;
use Carp;

our $VERSION    = '0.2';
our $DEBUG      = $ENV{DEBUG};

my $dbh=DBI->connect('dbi:mysql:stardata1;host=localhost'
            . ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
            . ';mysql_read_default_group=trigger_creator',
                undef,
                undef,
                {RaiseError => 1, PrintError=> 0})
         or die "Can't connect: $DBI::errstr\n";

my $database_list_query = qq{
    SELECT DISTINCT
        table_schema
    FROM
        information_schema.tables
    WHERE
        table_name ='trigger_request'};

my $databases = safe_selectcol_arrayref($dbh, $database_list_query,und
+ef);

for my $db (@$databases) {
    use_db($dbh,$db);
    #
    # no need to check anymore: the initial query ensures that
    # only databases with a trigger_request tables are inspected
    #
    # my ($trigger_request) = $dbh->selectrow_array(
    #    qq{SHOW TABLES LIKE 'trigger_request'});
    # next unless $trigger_request;

    #
    # create the trigger_answer table
    #
    safe_do(
            $dbh,
            qq{create table if not exists trigger_answer
                (trigger_name varchar(50) not null primary key,
                TS timestamp, result text) }
            );

    #
    # collects the list of triggers to be created
    #
    my $triggers = safe_selectall_arrayref(
            $dbh,
            qq{select trigger_name, coalesce(trigger_body, '') as trig
+ger_body
                from trigger_request where done = 0},
            {Slice => {}}
            );

    #
    # collects the list of existing triggers, so we know which
    # ones we need to drop before creation
    #

    my $existing_triggers_list = safe_selectcol_arrayref(
            $dbh,
            qq{select trigger_name
               from information_schema.triggers
               where trigger_schema = ?},
            undef, $db);

    my %existing_triggers = map { $_, 1 } @$existing_triggers_list;

    #
    # trigger creation loop
    #
    TRIGGERS:
    for my $trig (@$triggers)
    {
        if ($DEBUG) {
            print "DB: $db\n", Data::Dumper->Dump([$trig],['trig']);
        }
        last TRIGGERS unless exists $trig->{trigger_name};
        last TRIGGERS unless exists $trig->{trigger_body};
        my $result = undef;
        #
        # removing trailing spaces from trigger definition
        #
        $trig->{trigger_body} =~ s/^\s+//x;
        $trig->{trigger_body} =~ s/\s+$//x;
        #
        # sanitizing check. We are going to execute only queries that
+are
        # trigger creations, without any database specification
        #
        if ( ( $trig->{trigger_body} eq q{} ) # = only drop trigger re
+quest
                                              # Notice that the query
+will convert
                                              # any NULL trigger body
+to ''
              or
              ($trig->{trigger_body} =~ /^\s* create \s+ trigger \s+ $
+trig->{trigger_name}/xi) )
        {

            #
            # more sanitizing checks
            #

            if (my ($tdb,$ttable) = $trig->{trigger_body} =~ /(\w+|`[^
+`]+`)\.(\w+|`[^`]+`)/xi)
            {
                if ($tdb ne $db)
                {
                    set_result(
                        $dbh,
                        $trig->{trigger_name},
                        "REJECTED: Attempt at using database $tdb from
+ database $db");
                    next TRIGGERS;
                }
            }

            #
            # The requested trigger will be dropped first, if exists.
            #
            if (exists $existing_triggers{$trig->{trigger_name}}) {
                eval {
                    $dbh->do(qq[drop trigger $trig->{trigger_name}] )
+;
                } ;
                if ($EVAL_ERROR) {
                    set_result(
                        $dbh,
                        $trig->{trigger_name},
                        $EVAL_ERROR);
                    next TRIGGERS;
                }
                else {
                    $result = 'OK';
                }
            }
            if ( $trig->{trigger_body} ne q{} )  { # if the body is em
+pty, skip the creation
                #
                # for future versions of MySQL. Starting 5.0.17 a DEFI
+NER clause
                # can be used.
                #
                $trig->{trigger_body} =~ s{^\s* create}{CREATE /*!5001
+7 DEFINER=CURRENT_USER*/ }xi;

                #
                # This is the main point. The trigger is created here
                #
                eval {
                    $dbh->do($trig->{trigger_body});
                };
                if ($EVAL_ERROR) {
                    $result = $EVAL_ERROR;
                }
                else {
                    $result = 'OK';
                }
            }
        }
        #
        # if the initial check failed, we report that such query was
        # not accepted
        #
        else {
            $result = 'SQL command not recognized as a CREATE TRIGGER'
+;
        }
        #
        # finally, we report the results
        # to the trigger_answer table
        set_result( $dbh, $trig->{trigger_name}, $result);
    }
}

sub set_result {
    my ($dbh,$trigger_name, $result) = @_;
    $result =~ s/^ .* do failed: \s* //x;
    $result =~ s/at \s+ line \s+ \d+ \s+ at \s+ \S+ \s+ line \s+ \d+ \
+W*$//x;
    safe_do(
        $dbh,
        qq{insert into trigger_answer (trigger_name, result) values (?
+, ?)
           on duplicate key update result = ?},
        undef,
        $trigger_name, $result, $result);

    safe_do(
        $dbh,
        qq{update trigger_request set done = 1 where trigger_name = ?}
+,
        undef ,
        $trigger_name);
    if ($DEBUG) {
        print "RESULT: $result\n";
    }
}

sub use_db {
    my ($dbh,$db) = @_;
    # prepared statements do not support "use database_name"
    my $save_prepare_option = $dbh->{mysql_emulated_prepare};
    $dbh->{mysql_emulated_prepare}=1;
    eval { $dbh->do(qq{use $db}) };
    if ($EVAL_ERROR) {
        croak "unable to change to database $db\n";
    }
    $dbh->{mysql_emulated_prepare}= $save_prepare_option;
}

sub safe_selectcol_arrayref {
    my ($dbh, $query, $options, @params) = @_;
    my $result;
    # This should be the normal call
    # my $result = $dbh->selectcol_arrayref($query);
    #
    # the following ugly hack is a workaround for a bug in
    # DBD::mysql 3.0002_4 (http://bugs.mysql.com/bug.php?id=15546)
    #
    eval {
        if (@params) {
            $result = $dbh->selectall_arrayref($query, $options, @para
+ms);
        }
        else {
            $result = $dbh->selectall_arrayref($query, $options);
        }
    };
    if ($EVAL_ERROR) {
        croak "error executing query: $query\n$EVAL_ERROR\n";
    }
    return [ map {$_->[0] } @$result ];
}

sub safe_do {
    my ($dbh, $query, $options, @params) = @_;
    my $result;
    eval {
        if (@params) {
            $result = $dbh->do($query,$options,@params);
        }
        else {
            $result = $dbh->do($query,$options);
        }
        if ($EVAL_ERROR) {
            croak "error executing query: $query\n$EVAL_ERROR\n";
        }
    };
    return $result;
}

sub safe_selectall_arrayref {
    my ($dbh, $query, $options, @params) = @_;
    my $result;
    eval {
        if (@params) {
            $result = $dbh->selectall_arrayref($query,$options,@params
+);
        }
        else {
            $result = $dbh->selectall_arrayref($query,$options);
        }
        if ($EVAL_ERROR) {
            croak "error executing query: $query\n$EVAL_ERROR\n";
        }
    };
    return $result;
}

via http://www.perlmonks.org/?node_id=515527