Bug #41049 does syntax "grant" case insensitive?

• Submitted: 26 Nov 2008 11:24
• Modified: 17 Jun 2010 22:52
• Reporter: l j
• Status: Closed
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.0.51b-log, all
• OS: Any (FreeBSD 6.0, Linux)
• Assigned to: Sergei Glukhov
• CPU Architecture: Any
• Tags: grant case insensitive

[26 Nov 2008 11:24] l j

Description:
when i grant privileges as fellow to new user,but he can also read and update table 'location',he has the same privileges with table 'Location' and 'location',does syntax "grant' case insensitive?

mysql> show grants;
+------------------------------------------------------------------------------------------------------------+
| Grants for resume_test@%                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'resume_test'@'%' IDENTIFIED BY PASSWORD '*3D8488EDE916F2DEAD37A48C3DF45C6A58CBE748' | 
| GRANT SELECT, INSERT, UPDATE, DELETE ON `resume`.`Location` TO 'resume_test'@'%' WITH GRANT OPTION         | 
| GRANT SELECT, INSERT, UPDATE, DELETE ON `resume`.`JobGroup` TO 'resume_test'@'%' WITH GRANT OPTION         | 
| GRANT SELECT, INSERT, UPDATE, DELETE ON `resume`.`rRequest` TO 'resume_test'@'%' WITH GRANT OPTION         | 
+------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_resume |
+------------------+
| JobGroup         | 
| Location         | 
| location         | 
| rRequest         | 
+------------------+
4 rows in set (0.00 sec)

How to repeat:
when in one database,there are tables with same name without case defferences.if grant privileges to one table, the user also has the same privileges to other table with the same name without case defferences.

[27 Nov 2008 7:14] Sveta Smirnova

Thank you for the report.

Verified as described.

[28 Jan 2009 15:40] 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/64336

2730 Sergey Glukhov	2009-01-28
      Bug#41049 does syntax "grant" case insensitive?
      The problem:
      column_priv_hash uses utf8_general_ci collation
      for the key comparison. The key consists of user name,
      db name and table name. Thus user with privileges on table t1
      is able to perform the same operation on T1
      (the similar situation with user name & db name).
      So collation which is used for column_priv_hash
      should be case sensitive.
      The fix:
      replace system_charset_info with my_charset_utf8_bin
      
      Note: 
      It's safe for lower-case-table-names mode too because
      db name & table name are converted into lower case
      (see GRANT_NAME::GRANT_NAME).

[13 Feb 2009 11:39] 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/66181

2730 Sergey Glukhov	2009-02-13
      Bug#41049 does syntax "grant" case insensitive?
      Problem 1:
        column_priv_hash uses utf8_general_ci collation
        for the key comparison. The key consists of user name,
        db name and table name. Thus user with privileges on table t1
        is able to perform the same operation on T1
        (the similar situation with user name & db name, see acl_cache).
        So collation which is used for column_priv_hash and acl_cache
        should be case sensitive.
        The fix:
          replace system_charset_info with my_charset_utf8_bin for
          column_priv_hash and acl_cache
      Problem 2:
        The same situation with proc_priv_hash, func_priv_hash,
        the only difference is that Routine name is case insensitive.
        So the fix is to use my_charset_utf8_bin for
        proc_priv_hash & func_priv_hash and convert routine name into lower
        case before writing the element into the hash and
        before looking up the key.
        Additional fix: mysql.procs_priv Routine_name field collation
        is changed to utf8_general_ci.
        It's necessary for REVOKE command
        (to find a field by routine hash element values).
      
        Note: 
          It's safe for lower-case-table-names mode too because
          db name & table name are converted into lower case
          (see GRANT_NAME::GRANT_NAME).

[2 Oct 2009 9:44] 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/85495

2816 Sergey Glukhov	2009-10-02
      Bug#41049 does syntax "grant" case insensitive?
      Problem 1:
      column_priv_hash uses utf8_general_ci collation
      for the key comparison. The key consists of user name,
      db name and table name. Thus user with privileges on table t1
      is able to perform the same operation on T1
      (the similar situation with user name & db name, see acl_cache).
      So collation which is used for column_priv_hash and acl_cache
      should be case sensitive.
      The fix:
      replace system_charset_info with my_charset_utf8_bin for
      column_priv_hash and acl_cache
      Problem 2:
      The same situation with proc_priv_hash, func_priv_hash,
      the only difference is that Routine name is case insensitive.
      So the fix is to use my_charset_utf8_bin for
      proc_priv_hash & func_priv_hash and convert routine name into lower
      case before writing the element into the hash and
      before looking up the key.
      Additional fix: mysql.procs_priv Routine_name field collation
      is changed to utf8_general_ci.
      It's necessary for REVOKE command
      (to find a field by routine hash element values).
      Note: 
      It's safe for lower-case-table-names mode too because
      db name & table name are converted into lower case
      (see GRANT_NAME::GRANT_NAME).
     @ mysql-test/include/have_case_insensitive_fs.inc
        test case
     @ mysql-test/r/case_insensitive_fs.require
        test case
     @ mysql-test/r/grant_lowercase_fs.result
        test result
     @ mysql-test/r/lowercase_fs_off.result
        test case
     @ mysql-test/r/ps_grant.result
        result fix
     @ mysql-test/r/system_mysql_db.result
        changed Routine_name field collation to case insensitive
     @ mysql-test/t/grant_lowercase_fs.test
        test case
     @ mysql-test/t/lowercase_fs_off.test
        test case
     @ scripts/mysql_system_tables.sql
        changed Routine_name field collation to case insensitive
     @ scripts/mysql_system_tables_fix.sql
        changed Routine_name field collation to case insensitive
     @ sql/sql_acl.cc
        Problem 1:
        column_priv_hash uses utf8_general_ci collation
        for the key comparison. The key consists of user name,
        db name and table name. Thus user with privileges on table t1
        is able to perform the same operation on T1
        (the similar situation with user name & db name, see acl_cache).
        So collation which is used for column_priv_hash and acl_cache
        should be case sensitive.
        The fix:
        replace system_charset_info with my_charset_utf8_bin for
        column_priv_hash and acl_cache
        Problem 2:
        The same situation with proc_priv_hash, func_priv_hash,
        the only difference is that Routine name is case insensitive.
        So the fix is to use my_charset_utf8_bin for
        proc_priv_hash & func_priv_hash and convert routine name into lower
        case before writing the element into the hash and
        before looking up the key.
        Additional fix: mysql.procs_priv Routine_name field collation
        is changed to utf8_general_ci.
        It's necessary for REVOKE command
        (to find a field by routine hash element values).
        Note: 
        It's safe for lower-case-table-names mode too because
        db name & table name are converted into lower case
        (see GRANT_NAME::GRANT_NAME).

[27 Oct 2009 9:08] 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/88266

2825 Sergey Glukhov	2009-10-27
      Bug#41049 does syntax "grant" case insensitive?
      Problem 1:
      column_priv_hash uses utf8_general_ci collation
      for the key comparison. The key consists of user name,
      db name and table name. Thus user with privileges on table t1
      is able to perform the same operation on T1
      (the similar situation with user name & db name, see acl_cache).
      So collation which is used for column_priv_hash and acl_cache
      should be case sensitive.
      The fix:
      replace system_charset_info with my_charset_utf8_bin for
      column_priv_hash and acl_cache
      Problem 2:
      The same situation with proc_priv_hash, func_priv_hash,
      the only difference is that Routine name is case insensitive.
      So the fix is to use my_charset_utf8_bin for
      proc_priv_hash & func_priv_hash and convert routine name into lower
      case before writing the element into the hash and
      before looking up the key.
      Additional fix: mysql.procs_priv Routine_name field collation
      is changed to utf8_general_ci.
      It's necessary for REVOKE command
      (to find a field by routine hash element values).
      Note: 
      It's safe for lower-case-table-names mode too because
      db name & table name are converted into lower case
      (see GRANT_NAME::GRANT_NAME).
     @ mysql-test/include/have_case_insensitive_fs.inc
        test case
     @ mysql-test/r/case_insensitive_fs.require
        test case
     @ mysql-test/r/grant_lowercase_fs.result
        test result
     @ mysql-test/r/lowercase_fs_off.result
        test result
     @ mysql-test/r/ps_grant.result
        test result
     @ mysql-test/r/system_mysql_db.result
        changed Routine_name field collation to case insensitive
     @ mysql-test/t/grant_lowercase_fs.test
        test case
     @ mysql-test/t/lowercase_fs_off.test
        test case
     @ scripts/mysql_system_tables.sql
        changed Routine_name field collation to case insensitive
     @ scripts/mysql_system_tables_fix.sql
        changed Routine_name field collation to case insensitive
     @ sql/sql_acl.cc
        Problem 1:
        column_priv_hash uses utf8_general_ci collation
        for the key comparison. The key consists of user name,
        db name and table name. Thus user with privileges on table t1
        is able to perform the same operation on T1
        (the similar situation with user name & db name, see acl_cache).
        So collation which is used for column_priv_hash and acl_cache
        should be case sensitive.
        The fix:
        replace system_charset_info with my_charset_utf8_bin for
        column_priv_hash and acl_cache
        Problem 2:
        The same situation with proc_priv_hash, func_priv_hash,
        the only difference is that Routine name is case insensitive.
        So the fix is to use my_charset_utf8_bin for
        proc_priv_hash & func_priv_hash and convert routine name into lower
        case before writing the element into the hash and
        before looking up the key.
        Additional fix: mysql.procs_priv Routine_name field collation
        is changed to utf8_general_ci.
        It's necessary for REVOKE command
        (to find a field by routine hash element values).
        Note: 
        It's safe for lower-case-table-names mode too because
        db name & table name are converted into lower case
        (see GRANT_NAME::GRANT_NAME).

[28 Oct 2009 10:14] 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/88466

2828 Sergey Glukhov	2009-10-28
      Bug#41049 does syntax "grant" case insensitive?
      test result fix
     @ mysql-test/suite/funcs_1/r/is_columns_mysql.result
        test result fix
     @ mysql-test/suite/funcs_1/r/is_statistics.result
        test result fix

[4 Nov 2009 9:16] Bugs System

Pushed into 5.0.88 (revid:[e-mail]-20091104091355-hpz6dwgkrfmokj3k) (version source revid:[e-mail]-20091028091533-v3mmlzmlqnc31u6j) (merge vers: 5.0.88) (pib:13)

[4 Nov 2009 9:26] Bugs System

Pushed into 5.1.41 (revid:[e-mail]-20091104092152-qz96bzlf2o1japwc) (version source revid:[e-mail]-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)

[11 Nov 2009 6:53] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091110093407-rw5g8dys2baqkt67) (version source revid:[e-mail]-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)

[11 Nov 2009 7:01] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091109115615-nuohp02h8mdrz8m2) (version source revid:[e-mail]-20091105092355-jzukkw6wnd7hmgrj) (merge vers: 5.5.0-beta) (pib:13)

[24 Nov 2009 20:51] Paul DuBois

Noted in 5.0.88, 5.1.41, 5.5.0, 6.0.14 changelogs.

In some cases, the server did not recognize lettercase differences 
between GRANT attributes such as table name or user name. For
example, a user was able to perform operations on a table with
privileges of another user with the same user name but in a different
lettercase. 

In consequence of this bug fix, the collation for the Routine_name
column of the mysql.proc table is changed from utf8_bin to
utf8_general_ci.

[2 Dec 2009 8:01] Bugs System

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

[2 Dec 2009 8:06] Bugs System

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

[16 Dec 2009 8:40] 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:46] 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:53] 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 10:35] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:51] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 11:06] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:20] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

[21 Dec 2009 20:02] James Day

The fix for this bug introduced bug #48872, not properly respecting grants for mixed case function names. This was fixed for versions 5.0.89, 5.1.42, 5.5.1 and 6.0.14.

[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:45] 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:35] 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:40] 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:45] 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:14] 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:30] 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:47] 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)

[17 Jun 2010 11:50] 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:28] 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:15] 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)