My Oracle Support Banner

Variable automatic_sp_privileges Is Not Working - "Warning 1404: Failed to grant EXECUTE and ALTER ROUTINE privileges" ; "ERROR 1370 (42000): execute command denied to user" (Doc ID 2377877.1)

Last updated on MARCH 23, 2018

Applies to:

MySQL Server - Version 5.7 and later
Information in this document applies to any platform.

Symptoms

The issue can be reproduced at will with the following steps:

  1. Create test database, test user and grant CREATE ROUTINE to test user
    mysql> create database sp_test;
    Query OK, 1 row affected (0.00 sec)

    mysql> use sp_test;
    Database changed
    mysql> create user 'spuser'@'%' ;
    Query OK, 0 rows affected (0.04 sec)

    mysql> grant create routine on sp_test.* to 'spuser'@'%';
    Query OK, 0 rows affected (0.03 sec)
    mysql> show grants for 'spuser'@'%';
    +-------------------------------------------------------------------------------------------------------+
    | Grants for spuser@% |
    +-------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'spuser'@'%' |
    | GRANT CREATE ROUTINE ON `sp_test`.* TO 'spuser'@'%' |
    +-------------------------------------------------------------------------------------------------------+
    2 rows in set (0.06 sec)
  2. Checking if automatic_sp_privileges variable is enabled
    mysql> show variables like 'automatic%';

    | automatic_sp_privileges | ON |

    1 row in set (0.00 sec)
  3. Login to Database with test user and create simple stored routine
    mysql> use sp_test;
    Database changed
    mysql>
    mysql>
    mysql> DROP PROCEDURE IF EXISTS sp_test;
    ERROR 1370 (42000): alter routine command denied to user 'spuser'@'%' for routine 'sp_test.sp_test'
    mysql> DELIMITER //
    mysql> CREATE PROCEDURE sp_test (p1 INT)
      -> BEGIN
      -> SET @x=0;
      -> REPEAT
      -> SET @x=@x+1;
      -> UNTIL @x>p1 END REPEAT;
      -> SELECT @x;
      -> END //
    Query OK, 0 rows affected, 1 warning (0.12 sec)

    mysql> DELIMITER ;
    mysql> show warnings;
    Warning |1404 | Failed to grant EXECUTE and ALTER ROUTINE privileges |
    1 row in set (0.00 sec)
  4. The user is unable to execute the stored routine.
    mysql> call sp_test(10);
    ERROR 1370 (42000): execute command denied to user 'sp_test'@'%' for routine 'sp_test.sp_test'

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.