Friday, December 09, 2005

Creating triggers in MSQL 5.0 without SUPER privilege

Trigger grants blues

Triggers are a great feature, and used with care they can prove very useful.
Unfortunately, since they arrived quite late in the schedule, there is no specific privilege designed to grant users the power to create a trigger. Instead, as of 5.0.17, users require the SUPER privilege to create triggers. Which is kind of unconvenient, because the SUPER privilege also allows users to do more things than creating triggers, such as killing threads and setting global variables.
This it is understandable why a system administrator would be quite reluctant to give the SUPER privilege to (possibly) unknown users who have otherwise full control of one database. Think, for example, to a ISP, or to the great database provider db4free, for which this page is dedicated. In these services, you can get an account, which leads to full grants to one database. Within that database you can do almost anything, except creating triggers.
Now, if triggers are really needed and important, your only resort would be asking the system administrator to create it, and then to modify it if it turns out to be wrong, and to drop it when you no longer need it. That's probably a task that the system administrator is not willing to burden himself with.

What then?

I remember that a few years ago I had a similar need. I was the administrator of a large human resources and finance database for a international organization. I was the only one who could physically create new users, but only the Chief of Personnel (CPO) or her deputies could authorize such creation. I did not want to give root privileges to the CPO, because, as much as I trusted her judgement, she had no technical computer skills, and besides, I did not feel confortable sharing my root password with anyone or creating a user with root prvileges.
For a few weeks I was creating users, and it was a tedious work, because there were about twenty different profiles, depending on the read/write accesses on personnel and financial matters. Then I worked out a solution that made everybody's life easier.
I created a table of user creation requests, with a front end for the CPO and her deputies, where they could fill in the request for a new user or for modifying/removing existing ones. The table contained the user name and department, and the profile to which (s)he was to be allowed.
A cron job every 30 minutes checked such table, and it fulfilled the request using a template for each profile, generatng a password for the user, who was then informed by e-mail about the new account, with the CPO in CC.
The system worked very well, and it had the side effect that my vacations could be planned with more freedom than before.
Remembering this experience, I thought that the same device could be applied to triggers, and here it is how it works.

Bending the rules

Assuming an organization like db4free, we have several databases, each one assigned to one user, who does not have powers on any other DB. Currently, these users can't create triggers. Each user who wants to use triggers should create a table named trigger_request with three mandatory fields: trigger_name, which is the name of the trigger, trigger_body, containing the query to create the trigger, and done, a numeric field with default to zero.
CREATE TABLE `trigger_request` (
`trigger_name` varchar(50) NOT NULL,
`trigger_body` text,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`done` int(11) default '0',
PRIMARY KEY (`trigger_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
There is a cron job every N minutes (the interval depending on how many users want to use this feature). My personal choice to implement this feature is Perl, but it can be easily done in any language. When the addtriggers program runs, it checks every database with a trigger_request table (the INFORMATION_SCHEMA helps here), and reads the table to see if there is a record where done is set to zero. For each record, it attempts to create the trigger, and reports the result to the user, by means of another table, which is created if needed, called trigger_answer
CREATE TABLE `trigger_answer` (
`trigger_name` varchar(50) NOT NULL,
`TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`result` text,
PRIMARY KEY (`trigger_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Safety checks

Of course, the program I made does not execute blindly any query that is passed to it. Some sanitizing checks are performed before the query is given to the DBMS. First of all, it checks that it is a CREATE TRIGGER query, and it is created within the same database. Then, it checks that there are no references to tables in a different database.
If the query does not pass these checks, the user is informed that something unacceptable was passed. When the program is satisfied that nothing fishy was tried, it executes the query, reporting the result in trigger_answer.
Let's see a practical example. Assume that I have a database called "charmer" and I put the following in my trigger_request table.
select * from trigger_request\G
*************************** 1. row ***************************
trigger_name: city_bi
trigger_body: create trigger city_bi /*THIS SHOULD RETURN AN ERROR*/
ts: 2005-12-09 12:58:01
done: 0
*************************** 2. row ***************************
trigger_name: city_ai
trigger_body: create trigger city_ai after insert on City for each row set @XY = 1
/*this should be OK*/
ts: 2005-12-09 12:58:01
done: 0
*************************** 3. row ***************************
trigger_name: city_bu
trigger_body: grant all on *.* to TheWiz identified by "gotcha" with grant option
/*THIS SHOULD BE REJECTED*/
ts: 2005-12-09 12:58:01
done: 0
*************************** 4. row ***************************
trigger_name: city_au
trigger_body: create trigger city_au after update on world.City for each row set @city_au = 1
/*THIS SHOULD BE REJECTED*/
ts: 2005-12-09 12:58:01
done: 0
*************************** 5. row ***************************
trigger_name: city_bd
trigger_body: create trigger city_bd before delete on charmer.City for each row set @city_bd = 1
/*THIS SHOULD BE OK*/
ts: 2005-12-09 12:58:01
done: 0
5 rows in set (0.00 sec)
Running addtriggers against this dataset will produce two triggers and tree errors. The first record is accepted, because starts with "CREATE TRIGGER trigger_name", but will prduce an error because the SQL is incomplete. The second record is OK, and will execute without fuss. The third record is rejected because it is not a "CREATE TRIGGER" statement. The fourth one is rejected because it references a table in a different database, and the fifth one is accepted because, even though it references a table with the "database.table" syntax, it is using the authorized database name.
Thus, the user will see the following in trigger_answer
select * from trigger_answer\G
*************************** 1. row ***************************
trigger_name: city_bi
TS: 2005-12-09 13:03:05
result: You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near ''
*************************** 2. row ***************************
trigger_name: city_ai
TS: 2005-12-09 13:03:05
result: OK
*************************** 3. row ***************************
trigger_name: city_bu
TS: 2005-12-09 13:03:05
result: SQL command not recognized as a CREATE TRIGGER
*************************** 4. row ***************************
trigger_name: city_au
TS: 2005-12-09 13:03:05
result: REJECTED: Attempt at using database world from database charmer
*************************** 5. row ***************************
trigger_name: city_bd
TS: 2005-12-09 13:03:05
result: OK
5 rows in set (0.00 sec)
The source code for addtriggers is quite long. You can see it online at MySQL General Purpose Stored Routines Library CVS tree.

(Update) Modifying triggers


To drop a trigger, set the trigger's body in trigger_request to NULL or to an empty string. To modify an existing trigger, change the trigger's body, and set the done column to zero. The program will first drop the trigger (because it sees that it exists, from the INFORMATION SCHEMA tables) and will execute the trigger creation query.

A final plea

Does anybody see any security risks in this paradigm? Any suggestion to strenghten the checking mechanism in the cron job program would be welcome.

6 comments:

Giuseppe Maxia said...

Trigger modification:
I forgot to mention it in the text. You should know by reading the code, but now I amended the description as well.
To drop a trigger, it's enough to set the trigger_body to NULL or to an empty string.
To modify a trigger, change the trigger body and set the "done" field to zero again.

There are already timestamps in both tables (request and answer). As for the user, it should not be necessary, because this paradigm applies to situations where you have just one user per database.

As for the log, yes, it's possible to keep one, but I'd say to keep it outside the user's database, and in that case it should have the user's name in one column.

The system I am currently designing does not use e-mail. It is up to the user to check the trigger_answer table to see what happened to his/her requests.

Susana GarcĂ­a Chueca said...

Do you know any provider using this or similar system for triggers creation in mysql? I need to use them!! This issue will be fixed in 5.1 version but still it's in release candidate.

db4free.net is a developer/experimental site only? Right?

Thank you and excuse my english!
Susana.

Unknown said...

Instead of running addtrigger as a cron job, could you not run it as a trigger from the trigger_request table, so that it runs immediately when a modification is made to the trigger request table?

Regards

Giuseppe Maxia said...

@Wineza,
you can't create a trigger from a trigger.

Anonymous said...

Still got the error below
DBD::mysql::db do failed: Access denied; you need the SUPER privilege for this operation at .... line 179.

The username en password to connect to mysql are from the shared hosting and don't have the SUPER privilege. I don't have them with SUPER privilege.

Why do I get the error? The cronjob is nog executed as root?

Giuseppe Maxia said...

@Anonymous
See the code. It says:
It is intended to be run as a cron job from a user with SUPER privileges and full access to all the concerned databases.