Bug #47843 executing procedure fails for grants involving db with 'test' in the name
Submitted: 6 Oct 2009 1:49 Modified: 12 Oct 2009 22:07
Reporter: Jacek Becla Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.1.37, 5.0, 5.1, next bzr OS:Linux
Assigned to: CPU Architecture:Any

[6 Oct 2009 1:49] Jacek Becla
Description:
Executing a stored procedure or a function fails if authorization is set such that executing is granted for databases that start with "test".

Note that in that case the error number always is 1370. If the database name starts with some other word, if there is missing authorization the error number is 1045 as shown in the 'how to repeat'

How to repeat:
CREATE DATABASE test_X;
USE test_X;
DELIMITER //
CREATE FUNCTION incr (n INT)  RETURNS INT
BEGIN
  RETURN n + 1;
END
//
DELIMITER ;

-- now run this in separate window. As expected, it fails 
-- (but note the error number: 1370)
mysql -uuTest test_X -e 'select incr(3)'

ERROR 1370 (42000) at line 1: execute command denied to user ''@'localhost' for routine 'test_X.incr'

-- now grant EXECUTE to user uTest
GRANT EXECUTE ON `test%`.* TO uTest;

-- now run this in separate window. It FAILS, again with error 1370
mysql -uuTest test_X -e 'select incr(3)'

ERROR 1370 (42000) at line 1: execute command denied to user 'uTest'@'%' for routine 'test_X.incr'

== = = = = = = = = = = = = = = = =

== Running something very similar, but with database called tst_X instead of test_X works as expected.

CREATE DATABASE tst_X;
USE tst_X;
DELIMITER //
CREATE FUNCTION incr (n INT)  RETURNS INT
BEGIN
  RETURN n + 1;
END
//
DELIMITER ;

-- now run this in separate window, as expected, it fails
-- Note in this case the error number is 1045
mysql -uuTst tst_X -e 'select incr(3)'

ERROR 1045 (28000): Access denied for user 'uTst'@'localhost' (using password: NO)

-- grant execute to user uTst
GRANT EXECUTE ON `tst%`.* TO uTst;

-- now rerun the command that previously failed. 
-- It works as expected (no errors)
mysql -uTst u1 tst_X -e 'select incr(3)'
[6 Oct 2009 4:05] Valeriy Kravchuk
Thank you for the problem report. Please, send the results of:

select host, db, user from mysql.db;
[6 Oct 2009 4:36] Jacek Becla
mysql> select host, db, user from mysql.db;
+------+---------+-------+
| host | db      | user  |
+------+---------+-------+
| %    | test    |       | 
| %    | test%   | uTest | 
| %    | test\_% |       | 
| %    | tst%    | uTst  | 
+------+---------+-------+
4 rows in set (0.00 sec)
[6 Oct 2009 6:51] Sveta Smirnova
Thank you for the report.

Verified as described.

This can be treated as not a bug, because test% databases allows anonymous access and this leads to behavior which treat user uTest as anonymous user without EXECUTE privilege, but this still looks inconsistent.
[6 Oct 2009 18:26] Jacek Becla
Right, this command will fix the problem:

update db set Execute_priv = 'Y' where Db = 'test' or Db = 'test\_%';
flush privileges;

(but this behaviour is pretty confusing, so it'd be worthwhile fixing)
[12 Oct 2009 22:07] Omer Barnir
This is not a bug - test databases are predefined in the system as mentioned above. If one want's them to \behave' as any other database names, the extra permissions need to be removed.