For multiple web servers accessing a single DB server, I find I have to define multiple users like
user@ipaddr2 etc. for the data - i.e. tables.
But in this case, for stored procedures, I think INVOKER is better than DEFINER because I know that these are trusted users, and I do not want to make an open
user@% account that might get accessed from some unknown IP.
Or is there a better way using SQL SECURITY CONTEXT as DEFINER?
The manual itself says this:
To minimize the risk potential for stored program and view creation and use, follow these guidelines:
For a stored routine or view, use SQL SECURITY INVOKER in the object definition when possible so that it can be used only by users with permissions appropriate for the operations performed by the object.
Definer-context objects should be written keeping in mind that they may be able to access data for which the invoking user has no privileges.
I think DEFINER context is made for these cases - the case you describe in the question - no access to data normally, but only to SP - which has superior full access to data, but will only give you an output and not access to the data.
In short, INVOKER should be used rather than DEFINER, if only few privileged users must be allowed to run SPs, while DEFINER should be used if anyone can run the SP which operates on important data.