MySQL user permission on stored procedure

Question

I've created a simple stored procedure:

mysql> CREATE FUNCTION hello (s CHAR(20))
 -> RETURNS CHAR(50) DETERMINISTIC
 -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

But failed to run it:

mysql> SELECT hello('world');
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.hello'

Is it possible that my user name is an empty string? How do I create users and grant privileges? Can I grant a user all the privileges on all entities within a database?

Answer 1

As the error message itself says that user do not have the execute permissions.

mysql> SELECT hello('world');
ERROR 1370 (42000): execute command denied to user ''@'localhost' for routine 'test.hello'

You need to grant the Execute Permission to that user.For that you need to login as root user and grant the permission as

 grant execute on db.* to user@localhost;

For your other queries :

1. Yes It is possible that your username is an empty string but it is not safe to create the users like this.

2. For creating and granting privileges in brief have a look at This Link.

3. Yes you can grant all the privileges on all entities within a database. for this you can execute a command like

Login as root user and issue a command

GRANT ALL ON DB_NAME.* TO 'USER'@'HOST' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Answer 2

FYI for future visitors: strangely, MySQL can also return this error for a simple syntax issue such as having a space in count (case or sum (case. See https://stackoverflow.com/a/70608332/1703887