Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Triggers on demand without SUPER privileges for MySQL 5.0

by gmax (Abbot)
on Dec 09, 2005 at 13:00 UTC ( #515527=CUFP: print w/ replies, xml ) Need Help??

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; }
 _  _ _  _  
(_|| | |(_|><
 _|   

Comment on Triggers on demand without SUPER privileges for MySQL 5.0
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://515527]
Approved by Corion
Front-paged by dbwiz
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (12)
As of 2014-10-23 12:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (125 votes), past polls