Allowing triggers & stored procedures on MySQL

Hi everyone,

Currently we have a policy that prohibit our customers from creating
stored procedures and triggers on their DB's which I imagine must be
driving them up the walls. It's like having a car with a boot but you
are not able to use it. :)

Are there any reasons why we would'nt want customers to make use of
these built in features and what other means are available.

My reading showed that you need the "create routine" privilege and you
*may* require the super privilege if you have binary logging enabled
(and then that only becomes a potential issue if you are actually
replaying those logs (ie. either for replication or for media recovery).

I think I was reading the MySQL 5.1 manual - so maybe this is different
with 5.0?

Kind Regards
Brent Clark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Allowing triggers & stored procedures on MySQL

On 07/03/2010, at 3:30 AM, Brent Clark wrote:

> Hi everyone,
>
> Currently we have a policy that prohibit our customers from
> creating stored procedures and triggers on their DB's which I
> imagine must be driving them up the walls. It's like having a car
> with a boot but you are not able to use it. :)
>
> Are there any reasons why we would'nt want customers to make use of
> these built in features and what other means are available.
>
> My reading showed that you need the "create routine" privilege and
> you *may* require the super privilege if you have binary logging
> enabled (and then that only becomes a potential issue if you are
> actually replaying those logs (ie. either for replication or for
> media recovery).
>
> I think I was reading the MySQL 5.1 manual - so maybe this is
> different with 5.0?

In MySQL 5.0 (I get the impression that's the version you are
running) it requires SUPER to create triggers, however in 5.1 a new
"TRIGGER" privilege was introduced for that.

The requirement on SUPER for binary logging applies is the
log_bin_trust_function_creators is not set to 1. The reason for this
is to avoid random users creating non-deterministic procedures that
then replicate to a slave and causes the slave and master to get out
of sync. If binary logging is not enabled, SUPER is never required in
order to create a stored procedure. See more in http://dev.mysql.com/
doc/refman/5.0/en/stored-programs-logging.html for MySQL 5.0 or
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-loggi ng.html
for MySQL 5.1.

Best regards,
Jesper


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org