MySQL Procedures can not be edited or executed by newly created database users

Created:

2016-11-16 12:54:40 UTC

Modified:

2017-04-24 12:07:54 UTC

0

Was this article helpful?


Have more questions?

Submit a request

MySQL Procedures can not be edited or executed by newly created database users

Applicable to:

  • Plesk 12.5 for Linux

Symptoms

Unable to execute MySQL procedure under customer account, for example:

delimiter //

CREATE PROCEDURE testtest (IN name varchar(50),IN user_name varchar(50),IN branch varchar(50))
BEGIN
insert into odintest (name,user_name,branch) values (in_name,in_user_name,in_branch);

END//

delimiter ;

The following error occurred:

#1044 - Access denied for user 'admin_procedure'@'%' to database 'admin_procedure'

Cause

Software issue with id #PPPM-3592 .

Resolution

As workaround you can execute the following on your server:

~# plesk db
mysql> SHOW GRANTS FOR database_user;
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM database_user;
mysql> SHOW GRANTS FOR database_user;
mysql> GRANT ALL PRIVILEGES ON `your_database`.* TO 'database_user'@'%';
mysql> SHOW GRANTS FOR database_user;
mysql> flush privileges;

It should look like the following:

mysql> SHOW GRANTS FOR test;
+-----------------------------------------------------------------------------------------------------+

| Grants for test@% |
+-----------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT ALL PRIVILEGES ON `admin_procedure`.* TO 'test'@'%' |
Have more questions? Submit a request
Please sign in to leave a comment.