MySQL Bugs: #43779: Only the root user can create and use stored procedures

Bug #43779	Only the root user can create and use stored procedures
Submitted:	21 Mar 2009 15:46	Modified:	23 Apr 2009 18:13
Reporter:	Ziv Barber	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	50.0.67	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	stored procedures permissions

Description:
There's no way to create and use stored procedures on users other then the root user.

The following commands is always failed:

CALL
CREATE PROCEDURE
DROP PROCEDURE

Doing SHOW GRANTS FOR user@localhost; will report that we have permissions for SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES but we don't have neither ALTER ROUTINE, CREATE ROUTINE nor EXECUTE permissions for this user.
Trying to add the permissions will not effect anything.

How to repeat:
Create new user:

GRANT ALL PRIVILEGES ON db.* TO user@localhost IDENTIFIED BY 'pass';
flush privileges;

SHOW GRANTS FOR user@localhost;
(we can see that the user is not getting permissions to neither ALTER ROUTINE, CREATE ROUTINE nor EXECUTE).

GRANT ALTER ROUTINE ON db.* TO user@localhost IDENTIFIED BY 'pass';
GRANT CREATE ROUTINE ON db.* TO user@localhost IDENTIFIED BY 'pass';
GRANT EXECUTE ON db.* TO user@localhost IDENTIFIED BY 'pass';
flush privileges;
(no effect on the permissions)

Suggested fix:
I guess that it's a bug on the GRANT command since that it's not updating the permissions.

Sorry, it looks like privileges is the right category.

Workaround to fix this problem:

mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables

Thank you for the report.

According to the last comment you did not run mysql_upgrade on time and had broken privilege tables. So I close the report as "Not a Bug".

I forgot to mention that it's a clean installation and not an update to any previous version.

Thank you for the feedback.

But version 5.0.67 is old. Please try new version 5.0.77 and if problem still exists describe step-by-step how you do clean installation. Also indicate accurate package version you use (filename).

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".