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?

Does the user you're running this as have EXECUTE permissions? (or create/alter_routine permissions) — mable, Jun 25 '14 at 14:46
I am not certain, but I suppose so... SHOW GRANTS FOR 'user'@'localhost' results in two lines: GRANT USAGE and GRANT ALL PRIVILEGES — comodoro, Jun 26 '14 at 13:13

score 5 · Answer 1 · answered Jun 25 '14 at 15:19

5

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.

answered Jun 25 '14 at 15:19

solick

2,325
3
17
29

2

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? – comodoro Jun 26 '14 at 13:16
1

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. – solick Jun 26 '14 at 15:39
2

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. – comodoro Jun 27 '14 at 11:50
No that Means that you can grant unsage privileges an gran talk privileges to other users – solick Jun 28 '14 at 06:49
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. – comodoro Jun 28 '14 at 13:27
To Grant Usage and To Grant All Privileges are themselves privileges. Please have a look into the manual: http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html – solick Jun 29 '14 at 19:05
Yes, that is the documentation I have read and unless I overlooked some exception, GRANT ALL includes CREATE ROUTINE privilege. – comodoro Jul 07 '14 at 14:59

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 07 '14 at 15:13

comodoro

1,489
1
19
30

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. – solick Jul 09 '14 at 13:48
1

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. – comodoro Jul 09 '14 at 16:10

Creating MySQL function requires SUPER privileges

2 Answers2