Mysql – how to GRANT EXECUTE on MySQL

MySQLpermissions

I updated the timezone on an AWS MySql instance, and tech support told me I also had to runt his command for each MySql user:

GRANT EXECUTE ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'localhost';

I tried running without selecting a database and got this error:

ERROR 1046 (3D000): No database selected

So then I ran:

use db2;
GRANT EXECUTE ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'localhpst';

And I got this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'localhost'' at line 1

I looked at the docs (http://dev.mysql.com/doc/refman/5.6/en/grant.html) and I don't see what would be wrong – here I want to grant access to a procedure and that seems to correspond to the docs:

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

And also this is how we created the procedure:

DELIMITER |
CREATE PROCEDURE mysql.store_time_zone ()
IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN
SET SESSION time_zone = 'America/New_York';
END IF |
DELIMITER ;

Where did I go wrong? Thanks for any suggestions.

Best Answer

It is impossible to run this command:

GRANT EXECUTE ON `mysql`.`store_time_zone` TO 'user'@'%';

Here is why:

  • The EXECUTE grant exists at the global level
  • The EXECUTE grant exists at the database level
  • The EXECUTE grant does not exist at the table level
  • The EXECUTE grant does not exist at the column level

Here is how you can prove it:

User grants for MySQL exist in four(4) MyISAM tables

  • mysql.user (Global grants)
  • mysql.db (Database level grants)
  • mysql.tables_priv (Table level grants)
  • mysql.columns_priv (Column level grants)

If you run this query

mysql> select concat(table_schema,'.',table_name)
    -> from information_schema.columns
    -> where table_schema='mysql'
    -> and column_name='execute_priv';
+-------------------------------------+
| concat(table_schema,'.',table_name) |
+-------------------------------------+
| mysql.db                            |
| mysql.user                          |
+-------------------------------------+
2 rows in set (0.03 sec)

mysql>

Only mysql.user and mysql.db have columns in the mysql schema representing the EXECUTE privilege.

Therefore, these commands would work

# Global level EXECUTE privilege
GRANT EXECUTE ON *.* TO 'user'@'%';
# Database level EXECUTE privilege
GRANT EXECUTE ON `mysql`.* TO 'user'@'%';
Related Question