MacLochlainns Weblog

A friend wanted to know how to create a synonym to a library of stored functions and procedures in MySQL. I had to deliver the sad news that you can’t create a synonym in MySQL 5.0, 5.1, or 5.5. You need to write a wrapper function or procedure.

A synonym (available in Oracle) would eliminate the need to call a function from another database through the component selector (or period). That’s because it maps a name to the reference and name instead of requiring you to include it with each call. The following shows a call from outside of the lib database:

SELECT lib.demo('Hello World!') AS "Statement";

He asked how to do it, so here’s how you do it below. Assume the following for this example:

• You have a lib database where you define all your functions, procedures, and tables.
• You have a app database that supports web connections.
• You don’t want to allow web users to connect directly to the lib database because access to the tables should only be available through the functions and procedures.

This base function is simplified to avoid interaction with table data but illustrate the technique of definer rights functions. The function takes a string of up to 20 characters and returns it enclosed in double quotes. To mimic these behaviors, as the root user, you should create the app and lib databases, the dev and web users, and grant privileges to the dev user to act in the lib database and the web user to act in the app database. The example below does this as the root user, but in real life don’t use a trivial password like the example:

/* Create the two databases. */
CREATE DATABASE app;
CREATE DATABASE lib;
 
/* Create the two users, the developer can only connect locally. */
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev';
CREATE USER 'web'@'%' IDENTIFIED BY 'web';
 
/* Grant privileges to be a definer in both databases. */
GRANT ALL ON app.* TO 'dev'@'localhost';
GRANT ALL ON lib.* TO 'dev'@'localhost';
 
/* Grant privileges to any function or privilege in the APP database to the WEB user. */
GRANT EXECUTE ON app.* TO 'web'@'%';

After creating and granting all the appropriate privileges, here are the steps to create the test case.

1. You create and test the function as the dev user in the lib database.

/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */
DELIMITER $$
 
/* Create a function that echoes back the string with double quotes. */
CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
  RETURN CONCAT('"',str,'"');
END;
$$
 
/* Reset the DELIMITER value. */
DELIMITER ;
 
/* Query the function. */
SELECT demo('Ciao amico!') AS "Statement";

2. You create and test the wrapper function as the dev user in the app database.

/* Set the delimiter to something other than a semi-colon so they can be typed in statements. */
DELIMITER $$
 
/* Create a function that echoes back the string with double quotes. */
CREATE FUNCTION demo(str VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
  RETURN lib.demo(str);
END;
$$
 
/* Reset the DELIMITER value. */
DELIMITER ;
 
/* Query the function. */
SELECT demo('Ciao amico!') AS "Statement";

3. You can test the wrapper function as the web user in the app database.

SELECT demo('Yes, it works!') AS "Statement";

This is the closest to a synonym for a function or procedure that is possible. I know this solves his problem and hope it solves a couple others too.