| Bug #41049 | does syntax "grant" case insensitive? | ||
|---|---|---|---|
| Submitted: | 26 Nov 2008 12:24 | Modified: | 3 Dec 3:37 |
| Reporter: | l j | ||
| Status: | Closed | ||
| Category: | Server: Privileges | Severity: | S3 (Non-critical) |
| Version: | 5.0.51b-log, all | OS: | Any (FreeBSD 6.0, Linux) |
| Assigned to: | Sergey Gluhov | Target Version: | 5.0+ |
| Tags: | grant case insensitive | ||
| Triage: | Triaged: D2 (Serious) | ||
[27 Nov 2008 8:14]
Sveta Smirnova
Thank you for the report. Verified as described.
[28 Jan 16: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 12: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 11: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 10: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 11: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 10:16]
Bugs System
Pushed into 5.0.88 (revid:joro@sun.com-20091104091355-hpz6dwgkrfmokj3k) (version source revid:sergey.glukhov@sun.com-20091028091533-v3mmlzmlqnc31u6j) (merge vers: 5.0.88) (pib:13)
[4 Nov 10:26]
Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)
[11 Nov 7:53]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 8:01]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105092355-jzukkw6wnd7hmgrj) (merge vers: 5.5.0-beta) (pib:13)
[24 Nov 21: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 9:01]
Bugs System
Pushed into 5.0.89 (revid:joro@sun.com-20091202075830-mzl79q7mc1v72pf1) (version source revid:joro@sun.com-20091127095944-autr58itccge4z9l) (merge vers: 5.0.89) (pib:13)
[2 Dec 9:06]
Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:joro@sun.com-20091127103215-xbnuf3d800lmvgyy) (merge vers: 5.1.42) (pib:13)
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.