[MDEV-13396] Unexpected "alter routine comand defined" during CREATE OR REPLACE PROCEDURE - Jira

XML

Word

Printable

Using the root account (mysql -uroot), I create a new database u1 with procedure p1 and function f1:

DROP DATABASE IF EXISTS u1;

CREATE DATABASE u1;

USE u1;

DELIMITER $$

CREATE PROCEDURE p1()

BEGIN

  SELECT 1;

END;

$$

CREATE FUNCTION f1() RETURNS INT

BEGIN

  RETURN 1;

END;

$$

DELIMITER ;

Also, in the same root session, I create a new user u1 and grant ALTER ROUTINE on p1 and f1:

DROP USER IF EXISTS u1@localhost;

CREATE USER u1@localhost;

GRANT CREATE ROUTINE ON u1.* TO u1@localhost;

GRANT ALTER ROUTINE ON FUNCTION u1.f1 TO u1@localhost;

GRANT ALTER ROUTINE ON PROCEDURE u1.p1 TO u1@localhost;

Now I connect with the new user (mysql --user=u1 u1) and replace the function f1:

DELIMITER $$

CREATE OR REPLACE FUNCTION f1() RETURNS INT

BEGIN

  RETURN 2;

END;

$$

DELIMITER ;

It works fine:

Query OK, 0 rows affected (0.02 sec)

Now, in the same session for the user u1, I replace the procedure p1:

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1()

BEGIN

  SELECT 1;

END;

$$

DELIMITER ;

It fails with this error:

ERROR 1370 (42000): alter routine command denied to user 'u1'@'localhost' for routine 'u1.p1'

Looks wrong. Replace for the procedure p1 should work, like replace for the function f1 does.

The reasons for the problem resides in this piece of the code, in sql_parse.cc:

  case SQLCOM_CREATE_PROCEDURE:

  case SQLCOM_CREATE_SPFUNCTION:

...

  /* Checking the drop permissions if CREATE OR REPLACE is used */

  if (lex->create_info.or_replace())

    if (check_routine_access(thd, ALTER_PROC_ACL, lex->sphead->m_db.str,

                             lex->sphead->m_name.str,

                             lex->sql_command == SQLCOM_DROP_PROCEDURE, 0))

      return true;

The test for SQLCOM_DROP_PROCEDURE is wrong. It should test for SQLCOM_CREATE_PROCEDURE.

blocks

MDEV-10591 Oracle-style packages

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.