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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.2.8
Component/s: Authentication and Privilege System, Stored routines
Labels:
- security

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

blocks

MDEV-10591 Oracle-style packages

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2017-07-28 07:46

Alexander Barkov made changes - 2017-07-28 07:47

Field	Original Value	New Value
Link		This issue blocks ~~MDEV-10591~~ [ ~~MDEV-10591~~ ]

Alexander Barkov made changes - 2017-07-28 07:47

Description

Using the root account ({{mysql -uroot}}), I create a new database {{u1}} with procedure {{p1}} and function {{f1}}:
{code:sql}
DROP DATABASE IF EXISTS u1;
CREATE DATABASE u1;
USE u1;
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
  SELECT 1;
END;
$$
CREATE FUNCTION f1() RETURNS INT
BEGIN
  RETURN 1;
END;
$$
DELIMITER ;
{code}

Also, in the same {{root}} session, I create a new user {{u1}} and grant {{ALTER ROUTINE}} privileges on {{p1}} and {{f1}}:
{code:sql}
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;
{code}

Now I connect with the new user ({{mysql --user=u1 u1}}) and replace the function {{f1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE FUNCTION f1() RETURNS INT
BEGIN
  RETURN 2;
END;
$$
DELIMITER ;
{code}
It works fine:
{noformat}
Query OK, 0 rows affected (0.02 sec)
{noformat}

Now, in the same session for the user {{u1}}, I replace the procedure {{p1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
  SELECT 1;
END;
$$
DELIMITER ;
{code}
It fails with this error:
{noformat}
ERROR 1370 (42000): alter routine command denied to user 'u1'@'localhost' for routine 'u1.p1'
{noformat}

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 {{mysql_create_routine}}, in {{sql_parse.cc}}:
{code:cpp}
  /* 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;
  }
{code}

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

Using the root account ({{mysql -uroot}}), I create a new database {{u1}} with procedure {{p1}} and function {{f1}}:
{code:sql}
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 ;
{code}

Also, in the same {{root}} session, I create a new user {{u1}} and grant {{ALTER ROUTINE}} privileges on {{p1}} and {{f1}}:
{code:sql}
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;
{code}

Now I connect with the new user ({{mysql --user=u1 u1}}) and replace the function {{f1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE FUNCTION f1() RETURNS INT
BEGIN
  RETURN 2;
END;
$$
DELIMITER ;
{code}
It works fine:
{noformat}
Query OK, 0 rows affected (0.02 sec)
{noformat}

Now, in the same session for the user {{u1}}, I replace the procedure {{p1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
  SELECT 1;
END;
$$
DELIMITER ;
{code}
It fails with this error:
{noformat}
ERROR 1370 (42000): alter routine command denied to user 'u1'@'localhost' for routine 'u1.p1'
{noformat}

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 {{mysql_create_routine}}, in {{sql_parse.cc}}:
{code:cpp}
  /* 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;
  }
{code}

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

Alexander Barkov made changes - 2017-07-28 07:48

Description

Using the root account ({{mysql -uroot}}), I create a new database {{u1}} with procedure {{p1}} and function {{f1}}:
{code:sql}
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 ;
{code}

Also, in the same {{root}} session, I create a new user {{u1}} and grant {{ALTER ROUTINE}} privileges on {{p1}} and {{f1}}:
{code:sql}
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;
{code}

Now I connect with the new user ({{mysql --user=u1 u1}}) and replace the function {{f1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE FUNCTION f1() RETURNS INT
BEGIN
  RETURN 2;
END;
$$
DELIMITER ;
{code}
It works fine:
{noformat}
Query OK, 0 rows affected (0.02 sec)
{noformat}

Now, in the same session for the user {{u1}}, I replace the procedure {{p1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
  SELECT 1;
END;
$$
DELIMITER ;
{code}
It fails with this error:
{noformat}
ERROR 1370 (42000): alter routine command denied to user 'u1'@'localhost' for routine 'u1.p1'
{noformat}

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 {{mysql_create_routine}}, in {{sql_parse.cc}}:
{code:cpp}
  /* 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;
  }
{code}

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

Using the root account ({{mysql -uroot}}), I create a new database {{u1}} with procedure {{p1}} and function {{f1}}:
{code:sql}
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 ;
{code}

Also, in the same {{root}} session, I create a new user {{u1}} and grant {{ALTER ROUTINE}} on {{p1}} and {{f1}}:
{code:sql}
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;
{code}

Now I connect with the new user ({{mysql --user=u1 u1}}) and replace the function {{f1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE FUNCTION f1() RETURNS INT
BEGIN
  RETURN 2;
END;
$$
DELIMITER ;
{code}
It works fine:
{noformat}
Query OK, 0 rows affected (0.02 sec)
{noformat}

Now, in the same session for the user {{u1}}, I replace the procedure {{p1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
  SELECT 1;
END;
$$
DELIMITER ;
{code}
It fails with this error:
{noformat}
ERROR 1370 (42000): alter routine command denied to user 'u1'@'localhost' for routine 'u1.p1'
{noformat}

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 {{mysql_create_routine}}, in {{sql_parse.cc}}:
{code:cpp}
  /* 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;
  }
{code}

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

Alexander Barkov made changes - 2017-07-28 07:56

Labels

security

Alexander Barkov made changes - 2017-07-28 07:59

Description

Using the root account ({{mysql -uroot}}), I create a new database {{u1}} with procedure {{p1}} and function {{f1}}:
{code:sql}
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 ;
{code}

Also, in the same {{root}} session, I create a new user {{u1}} and grant {{ALTER ROUTINE}} on {{p1}} and {{f1}}:
{code:sql}
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;
{code}

Now I connect with the new user ({{mysql --user=u1 u1}}) and replace the function {{f1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE FUNCTION f1() RETURNS INT
BEGIN
  RETURN 2;
END;
$$
DELIMITER ;
{code}
It works fine:
{noformat}
Query OK, 0 rows affected (0.02 sec)
{noformat}

Now, in the same session for the user {{u1}}, I replace the procedure {{p1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
  SELECT 1;
END;
$$
DELIMITER ;
{code}
It fails with this error:
{noformat}
ERROR 1370 (42000): alter routine command denied to user 'u1'@'localhost' for routine 'u1.p1'
{noformat}

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 {{mysql_create_routine}}, in {{sql_parse.cc}}:
{code:cpp}
  /* 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;
  }
{code}

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

Using the root account ({{mysql -uroot}}), I create a new database {{u1}} with procedure {{p1}} and function {{f1}}:
{code:sql}
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 ;
{code}

Also, in the same {{root}} session, I create a new user {{u1}} and grant {{ALTER ROUTINE}} on {{p1}} and {{f1}}:
{code:sql}
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;
{code}

Now I connect with the new user ({{mysql --user=u1 u1}}) and replace the function {{f1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE FUNCTION f1() RETURNS INT
BEGIN
  RETURN 2;
END;
$$
DELIMITER ;
{code}
It works fine:
{noformat}
Query OK, 0 rows affected (0.02 sec)
{noformat}

Now, in the same session for the user {{u1}}, I replace the procedure {{p1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
  SELECT 1;
END;
$$
DELIMITER ;
{code}
It fails with this error:
{noformat}
ERROR 1370 (42000): alter routine command denied to user 'u1'@'localhost' for routine 'u1.p1'
{noformat}

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}}:
{code:cpp}
  /* 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;
  }
{code}

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

Alexander Barkov made changes - 2017-07-28 08:00

Description

Using the root account ({{mysql -uroot}}), I create a new database {{u1}} with procedure {{p1}} and function {{f1}}:
{code:sql}
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 ;
{code}

Also, in the same {{root}} session, I create a new user {{u1}} and grant {{ALTER ROUTINE}} on {{p1}} and {{f1}}:
{code:sql}
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;
{code}

Now I connect with the new user ({{mysql --user=u1 u1}}) and replace the function {{f1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE FUNCTION f1() RETURNS INT
BEGIN
  RETURN 2;
END;
$$
DELIMITER ;
{code}
It works fine:
{noformat}
Query OK, 0 rows affected (0.02 sec)
{noformat}

Now, in the same session for the user {{u1}}, I replace the procedure {{p1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
  SELECT 1;
END;
$$
DELIMITER ;
{code}
It fails with this error:
{noformat}
ERROR 1370 (42000): alter routine command denied to user 'u1'@'localhost' for routine 'u1.p1'
{noformat}

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}}:
{code:cpp}
  /* 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;
  }
{code}

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

Using the root account ({{mysql -uroot}}), I create a new database {{u1}} with procedure {{p1}} and function {{f1}}:
{code:sql}
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 ;
{code}

Also, in the same {{root}} session, I create a new user {{u1}} and grant {{ALTER ROUTINE}} on {{p1}} and {{f1}}:
{code:sql}
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;
{code}

Now I connect with the new user ({{mysql --user=u1 u1}}) and replace the function {{f1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE FUNCTION f1() RETURNS INT
BEGIN
  RETURN 2;
END;
$$
DELIMITER ;
{code}
It works fine:
{noformat}
Query OK, 0 rows affected (0.02 sec)
{noformat}

Now, in the same session for the user {{u1}}, I replace the procedure {{p1}}:
{code:sql}
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1()
BEGIN
  SELECT 1;
END;
$$
DELIMITER ;
{code}
It fails with this error:
{noformat}
ERROR 1370 (42000): alter routine command denied to user 'u1'@'localhost' for routine 'u1.p1'
{noformat}

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}}:
{code:cpp}
  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;
  }
{code}

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

Alexander Barkov made changes - 2017-07-28 08:06

Affects Version/s

10.0 [ 16000 ]

Alexander Barkov made changes - 2017-07-28 08:56

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2017-07-28 08:56

issue.field.resolutiondate

2017-07-28 08:56:34.0

2017-07-28 08:56:34.367

Alexander Barkov made changes - 2017-07-28 08:56

Fix Version/s		10.2.8 [ 22544 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:45

Workflow

MariaDB v3 [ 81821 ]

MariaDB v4 [ 152537 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2017-07-28 07:46

Updated:: 2017-07-28 08:56

Resolved:: 2017-07-28 08:56

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration