Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13396

Unexpected "alter routine comand defined" during CREATE OR REPLACE PROCEDURE

    XMLWordPrintable

Details

    Description

      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.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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