Creating stored functions and triggers without SUPER privilege

Question

All I am trying to do is create some stored functions and triggers that act on some tables. I find that, for either of those, I get MySQL error #1419 (You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)).

I checked the user table of mysql, and find that my username is HeadAdmin and my hostname is %. I tried setting DEFINER as HeadAdmin@%, but it doesn't work. I go back to user table, try setting it that way (a Super_priv column exists), and it still doesn't work (it literally is an error to do so).

I don't know how to use that log_bin_trust_function_creators variable, let alone why it's less safe. How can I create triggers and stored functions ?

Answer 1

Setting log_bin_trust_function_creators=1 is "less safe" simply because it trusts that the users creating stored programs know what they are doing, rather than requiring that they hold the SUPER privilege (which should also imply that they know what they are doing).

The issue here is that in a replication environment, the some privilege controls are more relaxed in the replica server, potentially giving the user defining a stored program access to mechanisms for privilege escalation that wouldn't be available on the master server. You may not have a replication environment, now, but every MySQL Server is a potential replication master, so the explanation is still valid.

It's a dynamic variable, so it can be enabled at runtime, but that requires the logged-in user to have the SUPER privilege. Add log_bin_trust_function_creators=1 to the [mysqld] section of your defaults file and restart the server daemon to enable this.

---

Useful, somewhat related, tidbits:

If SELECT @@BINLOG_FORMAT; returns STATEMENT, change that to MIXED in the config file, binlog_format=MIXED. STATEMENT is an old default value that should have been deprecated long ago, and can cause more grief for you when using stored programs. MIXED avoids those issues, because MIXED can properly log statements that are not deterministic. The other alternative, ROW, is even better in my experience, but MIXED is what the default value should have been.

When defining a stored function, be sure to declare it as DETERMINISIC if it is indeed deterministic and add READS SQL DATA if it uses tables but doesn't modify them, or add NO SQL if the function only does internal calculations but doesn't use tables. A deterministic function will always return the same output if given the same input, for all invocations and across all rows within the execution of a single query. There are cases where, if the server knows about this determinism, you'll get a performance benefit.

If you are inclined to write a function that is not deterministic, consider using a procedure instead.

Answer 2

After pondering over Michael-sqlbot 's answer, I decided upon changing that log_bin_trust_function_creators variable to 1. Until now, after looking on Google, and then AWS manual, I didn't know how.

According to one of their manuals, there exists things called parameter groups (damn, AWS has a lot of stuff) which can be created, and RDS instances use. A parameter group is created by default, and from my attempt, the parameters in it are not editable (including the parameter I needed to edit). Long story short, I clicked on Parameter Groups from the RDS Dashboard menu. Then, I clicked the Create Parameter Group button, and then went through to creating it. Once that was finished, I went to Details page type in the parameter, click Edit Parameters, and select 1 for the value.

Finally, when all was said and done, I go back to Instances (from the RDS dashboard), right click the instance, hit Modify, and change the instance's selected Parameter Group to the one I just created. I wait for that to take effect, reboot the instance, and wait for the all clear.