how to give the user to execute all procedures under the db (regular methods doesn't works)?

Question

I have a db and there are a few procedures, what I need to do is create a user and give him certain permissions for use including usage of procedures.

What I do for it is

CREATE USER 'user'@'user_ip' IDENTIFIED BY 'password';
GRANT CREATE, DROP, DELETE, INSERT, SELECT, UPDATE ON mydb.* TO 'user'@'user_ip';
GRANT EXECUTE ON mydb.* TO 'user'@'user_ip';
FLUSH PRIVILEGES;

then when I open the db with this user I see tables, but I don't see procedures...

What am I doing wrong?

Answer 1

I created below Procedure with root user

 DELIMITER //
 
 CREATE PROCEDURE GetAllTitles() BEGIN  SELECT *  FROM titles; END //
 
 DELIMITER ;

Then gave the proper permission as you mentioned

 CREATE USER 'my_user'@'%' IDENTIFIED BY 'Not_so_secure!1'; GRANT
 CREATE, DROP, DELETE, INSERT, SELECT, UPDATE ON employees.* TO
 'my_user'@'%'; GRANT EXECUTE ON employees.* TO 'my_user'@'%'; FLUSH
 PRIVILEGES;

Then connected with that user my_user

       mysql> select user();
      +-------------------+ 
      | user()            |
      +-------------------+ 
      | my_user@localhost |
      +-------------------+ 
      1 row in set (0.00 sec)
 

Then check the if I can see the procedure with below command connected to mysql using my_user

 mysql> SHOW PROCEDURE STATUS \G;
 *************************** 1. row ***************************
                   Db: employees
                 Name: GetAllTitles
                 Type: PROCEDURE
              Definer: root@localhost
             Modified: 2021-05-17 12:55:05
             Created: 2021-05-17 12:55:05
       Security_type: DEFINER
              Comment:  character_set_client: latin1 collation_connection: latin1_swedish_ci   Database Collation:
 latin1_swedish_ci 1 row in set (0.00 sec)

Then tried to call it's working

 CALL employees.GetAllTitles();

Make sure you are flushing the privileges