Bug #48872 Privileges for stored functions ignored if function name is mixed case

• Submitted: 18 Nov 2009 16:40
• Modified: 17 Jun 2010 22:49
• Reporter: Roland Volkmann
• Status: Closed
• Category: MySQL Server: Security: Privileges
• Severity: S2 (Serious)
• Version: 5.1.41, 5.1.42-bzr, 5.0
• OS: Any (XP / Win2003-Server, Mac OS X)
• Assigned to: Georgi Kodinov
• CPU Architecture: Any
• Tags: case, functions, privileges, regression, sensitivity

[18 Nov 2009 16:40] Roland Volkmann

Description:
On stored functions with function names written with upper and lower case letters (mixed case), granted execution rights are ignored. With version 5.1.40 this problem doesn't exist. Table names written in mixed letters are not related.

For test case I used the following server variables:

default-character-set = latin1
default-collation = latin1_german2_ci
lc_time_names = "de_DE"
default-storage-engine = INNODB
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI"

How to repeat:
c:\Programme\MySQL\MySQL Server 5.1\bin>mysql --user=root --password=******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table "TestTab" (id integer);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into testtab values (1);
Query OK, 1 row affected (0.00 sec)

mysql> delimiter ;;
mysql> create function f_Test() returns int(11) begin return 123; end;;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> create user 'tester' identified by 'secret';
Query OK, 0 rows affected (0.08 sec)

mysql> grant select on table "TestTab" to 'tester';
Query OK, 0 rows affected (0.00 sec)

mysql> grant execute on function "f_Test" to 'tester';
Query OK, 0 rows affected (0.00 sec)

mysql> select f_Test();
+----------+
| f_Test() |
+----------+
|      123 |
+----------+
1 row in set (0.00 sec)

mysql> select * from TestTab;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

===========================================================
now logon with the above created new user:
===========================================================

c:\Programme\MySQL\MySQL Server 5.1\bin>mysql --user=tester --password=secret
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> select * from TestTab;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select f_Test();
ERROR 1370 (42000): execute command denied to user 'tester'@'%' for routine 'tes
t.f_Test'
mysql> select "f_Test"();
ERROR 1370 (42000): execute command denied to user 'tester'@'%' for routine 'tes
t.f_Test'
mysql>

[19 Nov 2009 15:25] Valeriy Kravchuk

Verified just as described with 5.1.42 from bzr on Mac OS X:

77-52-1-11:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table `TestTab` (id integer);
Query OK, 0 rows affected (0.41 sec)

mysql> insert into testtab values (1);
Query OK, 1 row affected (0.00 sec)

mysql> delimiter //
mysql> create function `f_Test`() returns int(11) begin return 123; end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> create user 'tester'@'localhost' identified by 'secret';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on table `TestTab` to 'tester'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant execute on function `f_Test` to 'tester'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select f_Test();
+----------+
| f_Test() |
+----------+
|      123 |
+----------+
1 row in set (0.00 sec)

mysql> select * from TestTab;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> exit
Bye
77-52-1-11:5.1 openxs$ bin/mysql -utester -psecret test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> select * from TestTab;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select f_Test();
ERROR 1370 (42000): execute command denied to user 'tester'@'localhost' for routine 'test.f_Test'

[19 Nov 2009 15:30] Valeriy Kravchuk

And yes, this is a regression. On the same Mac, with 5.1.37:

...
77-52-1-11:mysql openxs$ bin/mysql -utester -psecret test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.37 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from TestTab;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> select f_Test();
+----------+
| f_Test() |
+----------+
|      123 | 
+----------+
1 row in set (0.00 sec)

Test case to copy/paste:

1. As root:

create table `TestTab` (id integer);
insert into testtab values (1);
delimiter //
create function `f_Test`() returns int(11) begin return 123; end//
delimiter ;
create user 'tester'@'localhost' identified by 'secret';
grant select on table `TestTab` to 'tester'@'localhost';
grant execute on function `f_Test` to 'tester'@'localhost';
select f_Test();
select * from TestTab;

2. As tester:

select current_user();
select * from TestTab;
select f_Test();

[19 Nov 2009 18:06] Roland Volkmann

Stored Procedures are not effected.

[20 Nov 2009 23:08] Roland Volkmann

The same problem exists with version 5.0.88 while 5.0.87 works fine.

[25 Nov 2009 17:16] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91680

2853 Georgi Kodinov	2009-11-25
      Bug #48872 : Privileges for stored functions ignored if function name
        is mixed case
      
      Transcode the procedure name to lowercase when searching for it in the 
      hash. This is the missing part of the fix for bug #41049.

[27 Nov 2009 10:00] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91880

2853 Georgi Kodinov	2009-11-27
      Bug #48872 : Privileges for stored functions ignored if function name
        is mixed case
      
      Transcode the procedure name to lowercase when searching for it in the 
      hash. This is the missing part of the fix for bug #41049.

[27 Nov 2009 16:07] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91957

2856 Georgi Kodinov	2009-11-27
      Addendum to bug #48872: disable output in the test case because errors are 
      dependent on the case mode

[2 Dec 2009 8:01] Bugs System

Pushed into 5.0.89 (revid:[e-mail]-20091202075830-mzl79q7mc1v72pf1) (version source revid:[e-mail]-20091127160731-6h2fahbh4409i841) (merge vers: 5.0.89) (pib:13)

[2 Dec 2009 8:04] Bugs System

Pushed into 5.1.42 (revid:[e-mail]-20091202080033-mndu4sxwx19lz2zs) (version source revid:[e-mail]-20091127161028-qvudt6ppgy8jbaxk) (merge vers: 5.1.42) (pib:13)

[3 Dec 2009 2:40] Paul DuBois

Noted in 5.0.89, 5.1.42 changelogs.

Privileges for stored routines were ignored for mixed-case routine names.

Setting report to NDI pending push into 5.6.x+.

[16 Dec 2009 8:41] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091216083311-xorsasf5kopjxshf) (version source revid:[e-mail]-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

[16 Dec 2009 8:47] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091216082430-s0gtzibcgkv4pqul) (version source revid:[e-mail]-20091201145844-39gy4wmejbisbxac) (merge vers: 5.5.0-beta) (pib:14)

[16 Dec 2009 8:54] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20091216083231-rp8ecpnvkkbhtb27) (version source revid:[e-mail]-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

[18 Dec 2009 2:08] Paul DuBois

Noted in 5.5.1, 6.0.14 changelogs.

[17 Feb 2010 16:29] Paul DuBois

Noted in 5.0.87sp1 changelog.

[20 Feb 2010 17:11] Bugs System

Pushed into 5.0.91 (revid:[e-mail]-20100220170835-5kr6ztsg25va7qzz) (version source revid:[e-mail]-20100220170835-5kr6ztsg25va7qzz) (merge vers: 5.0.91) (pib:16)

[1 Mar 2010 8:46] Bugs System

Pushed into 5.1.45 (revid:[e-mail]-20100301083827-xnimmrjg6bh33o1o) (version source revid:[e-mail]-20100226131646-kpvzk740hxbtaexn) (merge vers: 5.1.45) (pib:16)

[2 Mar 2010 14:33] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:[e-mail]-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)

[2 Mar 2010 14:38] Bugs System

Pushed into 5.5.3-m2 (revid:[e-mail]-20100302072233-t3uqgjzdukt1pyhe) (version source revid:[e-mail]-20100301090215-63o2w2y16go8n53p) (merge vers: 5.5.3-m2) (pib:16)

[2 Mar 2010 14:43] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100302072432-k8xvfkgcggkwgi94) (version source revid:[e-mail]-20100301094536-2zc4uqyy3os8san7) (pib:16)

[12 Mar 2010 14:17] Bugs System

Pushed into 5.1.44-ndb-7.0.14 (revid:[e-mail]-20100312135944-t0z8s1da2orvl66x) (version source revid:[e-mail]-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

[12 Mar 2010 14:33] Bugs System

Pushed into 5.1.44-ndb-6.2.19 (revid:[e-mail]-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:[e-mail]-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

[12 Mar 2010 14:49] Bugs System

Pushed into 5.1.44-ndb-6.3.33 (revid:[e-mail]-20100312135724-xcw8vw2lu3mijrhn) (version source revid:[e-mail]-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

[12 Mar 2010 16:47] Paul DuBois

Fixed in earlier 5.1.x, 5.5.x.

[17 Jun 2010 11:44] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:22] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)

[17 Jun 2010 13:10] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)