Creating MySQL function requires SUPER privileges

Question

I have a simple MySQL function for comparing versions:

CREATE FUNCTION `compareVersions` (
  versionA VARCHAR(50),
  versionB VARCHAR(50)) RETURNS INT DETERMINISTIC NO SQL
BEGIN
  DECLARE a1 INT;
  DECLARE b1 INT;
   DECLARE c1 INT;
    DECLARE d1 INT;
  DECLARE a2 INT;
  DECLARE b2 INT;
   DECLARE c2 INT;
    DECLARE d2 INT;  SET a1 = SUBSTRING_INDEX( `versionA` , '.', 1 );
  SET b1 = SUBSTRING_INDEX(SUBSTRING_INDEX( `versionA` , '.', 2 ),'.',-1);
  SET c1 = SUBSTRING_INDEX(SUBSTRING_INDEX( `versionA` , '.', -2 ),'.',1);
  SET d1 = SUBSTRING_INDEX( `versionA` , '.', -1 );
  SET a2 = SUBSTRING_INDEX( `versionB` , '.', 1 );
  SET b2 = SUBSTRING_INDEX(SUBSTRING_INDEX( `versionB` , '.', 2 ),'.',-1);
  SET c2 = SUBSTRING_INDEX(SUBSTRING_INDEX( `versionB` , '.', -2 ),'.',1);
  SET d2 = SUBSTRING_INDEX( `versionB` , '.', -1 ); 
  IF (a1 > a2) THEN 
    RETURN -1;
  ELSEIF ((a1 = a2) AND (b1 > b2)) THEN
    RETURN -1;
  ELSEIF ((a1 = a2) AND (b1 = b2) AND (c1 > c2)) THEN
    RETURN -1;
  ELSEIF ((a1 = a2) AND (b1 = b2) AND (c1 = c2) AND (d1 > d2)) THEN
    RETURN -1;
  ELSEIF ((a1 = a2) AND (b1 = b2) AND (c1 = c2) AND (d1 = d2)) THEN  
    RETURN 0;
  ELSE
    RETURN 1;
  END IF;
END $$

and its creation fails with

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)

This is almost the same as this question, but my function does not read any SQL data, it is simple, deterministic and I see no reason why it should require any extra privileges. It is not clear to me from the documentation if the SUPER privilege is required for creating all functions (which would be ridiculous, making stored functions unavailable to many users, everyone who does not have access to their database configuration). I do not even know if the function works, this was the first thing that came to mind, but the syntax should be correct (the delimiter is set in PHPMyAdmin). Getting all data from database and comparing them in the PHP application can be done, but I think it is easiest done this way. Is it possible? Does anybody have a better solution for comparing versions?

Allare

Does the user you're running this as have EXECUTE permissions? (or create/alter_routine permissions) – Myron

I am not certain, but I suppose so... SHOW GRANTS FOR 'user'@'localhost' results in two lines: GRANT USAGE and GRANT ALL PRIVILEGES – Allare

It seems that as of MySQL 8.0.33 this issue is somehow still not fixed... bugs.mysql.com/bug.php?id=110990 I ran into it with pretty must the same needs as the OP : Deterministic, No DB access, create a function globally or bound to a DB, Without SUPER nor setting log_bin_trust_function_creators, Keeping binary logging enabled, as ROW. I attempted to create the function : with/without my [user]@[my_ip] as definer, setting SQL SECURITY to INVOKER/DEFINER, associating function with the database managed by my user, setting all grants but super All without luck... – Scrimpy

Answer 1

I have circumvented the problem by making a long and therefore ugly database query in PHP. Maybe using stored procedure instead of function would be better. After searching I found in the documentation here this statement:

The current conditions on the use of stored functions in MySQL 5.5 can be summarized as follows. [...] To create or alter a stored function, you must have the SUPER privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE privilege that is normally required.

So indeed, if binary logging is on, you really need SUPER privilege to create stored functions. This severely limits the use of stored functions on bigger servers and in my opinion decreases the value of the whole DBMS. Looks like the classical "it is not a bug, it is a feature". I fear to think about what happens to stored functions if the database server is restarted after changing binary logging to on.

Allare

As i mentioned before, ET GLOBAL log_bin_trust_function_creators = 1; will allow you to create SP and Functions without SUPER privileges. I have multiple times created stored Procedures and functions on shared hosted systems, where i do not have root privileges. So normally a serious hoster should have enabled the above mentioned option. – Whereunto

As I mentioned, I do not have rights for this and it follows from the error that it is not set on the system I use. I cannot say anything about hosters, but the server in question is not used for hosting and if I were the admin of any database server, I would not implicitly set some setting that is labeled "potentionally unsafe" unless strongly required. – Allare

Answer 2

You can adjust the global variable for that:

/* allows to create functions as not root */
SET GLOBAL log_bin_trust_function_creators = 1;

I use this in setup-sql files for initializing a database for restoring a dump.

Whereunto

I cannot change global variables since I do not have root rights. The question is partly about this: do I need global root rights for creating stored functions? – Allare

You need the CREATE ROUTINE and optionally the ALTER ROUTINE privilege to create stored procedures. But normally you should have those privileges. Please check what SHOW GRANTS FOR CURRENT_USER(); shows up for you. – Whereunto

As written above, I have GRANT USAGE and GRANT ALL PRIVILEGES on the database. Does it include CREATE ROUTINE? I suppose it does, but the function creation fails. – Allare

No that Means that you can grant unsage privileges an gran talk privileges to other users – Whereunto

Could you please give some reference to this? The documentation says that ALL assigns all privileges available at the specified level (in my case database level) except GRANT OPTION, and CREATE ROUTINE is available at database level. – Allare

To Grant Usage and To Grant All Privileges are themselves privileges. Please have a look into the manual: dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html – Whereunto

Yes, that is the documentation I have read and unless I overlooked some exception, GRANT ALL includes CREATE ROUTINE privilege. – Allare