| Bug #62291 | MySQL not applying EXECUTE privilege for stored procedure | ||
|---|---|---|---|
| Submitted: | 29 Aug 2011 10:45 | Modified: | 30 Aug 2011 8:30 |
| Reporter: | Steve Binkowski | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S2 (Serious) |
| Version: | 5.1.41-3ubuntu12.7 | OS: | Linux (Linux 2.6.32-33-server #70-Ubuntu SMP Thu Jul 7 22:28:30 UTC 2011 x86_64 GNU/Linux) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | execute, privilege, stored procedure | ||
[29 Aug 2011 15:50]
Steve Binkowski
It was brought to my attention that the output I posted did not include a FLUSH PRIVILEGES statement. Here is another snippet of output that includes it.
[code]
#> mysql -u admin -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 734
Server version: 5.1.41-3ubuntu12.7 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use MyDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> delimiter //
mysql> CREATE DEFINER = 'admin'@'%' PROCEDURE `spFetchSiteConfig`(
-> IN siteurl vARCHAR(255)
-> )
-> NOT DETERMINISTIC
-> CONTAINS SQL
-> SQL SECURITY DEFINER
-> COMMENT ''
-> BEGIN
-> SET @t_SQL = CONCAT(
-> 'SELECT c.hostname as url,a.configname,b.configvalue FROM ',
-> '(ConfigOptions a LEFT JOIN SiteConfigs b ON a.id=b.configid) INNER JOIN ',
-> 'Sites c on c.hostname=\'',siteurl,'\'');
-> PREPARE t_stmt FROM @t_SQL;
-> EXECUTE t_stmt;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call spFetchSiteConfig('dev1');
+------+---------------------+-----------------------------+
| url | configname | configvalue |
+------+---------------------+-----------------------------+
| dev1 | email_admin | email@adomainnamenstuff.com |
| dev1 | admin_name | NULL |
| dev1 | cookie_life | NULL |
| dev1 | cookie_name | NULL |
| dev1 | db_host | NULL |
| dev1 | db_user | NULL |
| dev1 | db_pass | NULL |
| dev1 | db_name | NULL |
| dev1 | force_ssl | NULL |
| dev1 | forward_404 | NULL |
| dev1 | include_path | NULL |
| dev1 | log_code | NULL |
| dev1 | log_include_session | NULL |
| dev1 | log_level | NULL |
| dev1 | log_level_email | NULL |
| dev1 | maintenance | NULL |
| dev1 | maintenance_forward | NULL |
| dev1 | maintenance_page | NULL |
| dev1 | page_404 | NULL |
| dev1 | session_gc | NULL |
| dev1 | session_life | NULL |
| dev1 | email_tech | NULL |
| dev1 | userfile_plugin | NULL |
| dev1 | userfile_config | NULL |
+------+---------------------+-----------------------------+
24 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> grant usage on *.* to MyDBreader@localhost identified by 'stuff';
Query OK, 0 rows affected (0.00 sec)
mysql> grant execute on procedure MyDB.spFetchSiteConfig to MyDBreader@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
#> mysql -u MyDBreader -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 736
Server version: 5.1.41-3ubuntu12.7 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use MyDB
Database changed
mysql> call spFetchSiteConfig('dev1');
ERROR 1370 (42000): execute command denied to user 'MyDBreader'@'localhost' for routine 'MyDB.spFetchSiteConfig'
mysql> show grants for MyDBreader@localhost;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for MyDBreader@localhost |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'MyDBreader'@'localhost' IDENTIFIED BY PASSWORD '*C16FB9B747A690B6940636502556A57C69067858' |
| GRANT EXECUTE ON PROCEDURE `MyDB`.`spfetchsiteconfig` TO 'MyDBreader'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
[/code]
[30 Aug 2011 8:21]
Steve Binkowski
Possible duplicate of http://bugs.mysql.com/bug.php?id=48872. Testing now.
[30 Aug 2011 8:30]
Steve Binkowski
Duplicate confirmed.
Description: MySQL is not applying the EXECUTE permission as directed. Please see code demonstration below. How to repeat: #> mysql -u admin -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 228 Server version: 5.1.41-3ubuntu12.7 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use MyDB; Database changed mysql> delimiter // mysql> CREATE DEFINER = 'admin'@'%' PROCEDURE `spFetchSiteConfig`( -> IN siteurl vARCHAR(255) -> ) -> NOT DETERMINISTIC -> CONTAINS SQL -> SQL SECURITY DEFINER -> COMMENT '' -> BEGIN -> SET @t_SQL = CONCAT( -> 'SELECT c.hostname as url,a.configname,b.configvalue FROM ', -> '(ConfigOptions a LEFT JOIN SiteConfigs b ON a.id=b.configid) INNER JOIN ', -> 'Sites c on c.hostname=\'',siteurl,'\''); -> PREPARE t_stmt FROM @t_SQL; -> EXECUTE t_stmt; -> END; -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> grant usage on *.* to MyDBreader@localhost identified by 'a-really-long-password'; Query OK, 0 rows affected (0.00 sec) mysql> grant execute on procedure MyDB.spFetchSiteConfig to MyDBreader@localhost; Query OK, 0 rows affected (0.00 sec) mysql> call spFetchSiteConfig(''); Empty set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> exit Bye #> mysql -u MyDBreader -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 228 Server version: 5.1.41-3ubuntu12.7 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> call MyDB.spFetchSiteConfig(''); ERROR 1370 (42000): execute command denied to user 'MyDBreader'@'localhost' for routine 'MyDB.spFetchSiteConfig' mysql> show grants for MyDBreader@localhost; +----------------------------------------------------------------------------------------------------------------------+ | Grants for MyDBreader@localhost | +----------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'MyDBreader'@'localhost' IDENTIFIED BY PASSWORD '*326DC95FDAFF8D79E5B4E7BE8DF53867E801F666' | | GRANT EXECUTE ON PROCEDURE `MyDB`.`spfetchsiteconfig` TO 'MyDBreader'@'localhost' | +----------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> use MyDB; Database changed mysql> call spFetchSiteConfig(''); ERROR 1370 (42000): execute command denied to user 'MyDBreader'@'localhost' for routine 'MyDB.spFetchSiteConfig' mysql>