MySQL: What effect does DEFINER have on procedures and functions?

Question

I use SQLyog to write procedures and functions in a remote MySQL database I am developing. The database has only one username/password. It is accessed via a front end executable application that I have written in Delphi and which is used by a couple of dozen different people, all using the same username.

When I use SQLYog to write a procedure using, for example,

CREATE PROCEDURE age_frequency_count(IN bin_size INT)

The resulting procedure gets the definer put in whether I like it or not, resulting in

CREATE DEFINER=<the_user_name>@<my_IP_address> PROCEDURE age_frequency_count(IN bin_size INT)

(I think this is being done by MySQL, not by the SQLYog DBMS.)

From reading the documentation and from posts here on SO, I have a rough, but rather confused idea of how the definer is used to say what permissions are needed to execute, or maybe alter, the procedure and what the procedure is allowed to do, but I would welcome some clarification.

Question

If my IP address is in the definer, can the procedure still be executed by other people who will be logging in from a different IP address (although with the same username)?

Can someone please clarify what the definer is doing? i.e. what can a connection from my IP address do that connections from other IP addresses can't?

Answer 1

I use the DEFINER clause to create a stored procedure with the security privileges of a powerful user that also has UPDATE, DELETE, SELECT, and INSERT rights to a particular database table. Then, I only grant EXECUTE on that stored procedure to a minon user (some people call it a www user, versus the more powerful wwwproxy user).

In this way, the minion can only execute designated stored procedures and has no UPDATE, DELETE, SELECT, INSERT, or other rights on a database table.

I hope that helps frame the idea behind the DEFINER clause. Use it to separate power from tasks.

You are correct, by default, MySQL uses the identity of the current user as the DEFINER when creating a stored procedure. This identity could be the identity of the front-end application (so to speak), or, like I said, you can use a proxy user that has normal table privileges. Then the application user would be the minion with only one privilege on the stored procedure, EXECUTE.

In short, if the default DEFINER user does not represent what the front end application uses to login to the database, and you want it to, then you need to change the stored procedure with ALTER, if possible.

On the other hand, the better idea would be to use the minon/proxy scenario. Application users on the Internet have no bearing on the IP that ends up in the stored procedures DEFINER clause. All that matters is the IP of where your app is logging in from to MySQL. Your app is talking to the database, not user agents on peoples' computers. However, that notion is, generally, a point of initial confusion. You are fine!

Hope that helps.