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
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
libdatabase where you define all your functions, procedures, and tables.
- You have a
appdatabase that supports web connections.
- You don’t want to allow web users to connect directly to the
libdatabase 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
lib databases, the
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.
- You create and test the function as the
devuser in the
/* 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";
- You create and test the wrapper function as the
devuser in the
/* 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";
If you’re wondering why a
GRANT wasn’t required from the
lib database to the
app database, it’s because the
dev user has access to both databases and defined both objects.
- You can test the wrapper function as the
webuser in the
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.