#At file:///home/gluh/MySQL/mysql-5.0-bug-41049-2/ based on revid:davi.arnaut@stripped
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).
added:
mysql-test/include/have_case_insensitive_fs.inc
mysql-test/r/case_insensitive_fs.require
mysql-test/r/grant_lowercase_fs.result
mysql-test/t/grant_lowercase_fs.test
modified:
mysql-test/r/lowercase_fs_off.result
mysql-test/r/ps_grant.result
mysql-test/r/system_mysql_db.result
mysql-test/t/lowercase_fs_off.test
scripts/mysql_system_tables.sql
scripts/mysql_system_tables_fix.sql
sql/sql_acl.cc
=== added file 'mysql-test/include/have_case_insensitive_fs.inc'
--- a/mysql-test/include/have_case_insensitive_fs.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/have_case_insensitive_fs.inc 2009-10-02 08:45:03 +0000
@@ -0,0 +1,4 @@
+--require r/case_insensitive_fs.require
+--disable_query_log
+show variables like 'lower_case_file_system';
+--enable_query_log
=== added file 'mysql-test/r/case_insensitive_fs.require'
--- a/mysql-test/r/case_insensitive_fs.require 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/case_insensitive_fs.require 2009-10-02 08:45:03 +0000
@@ -0,0 +1,2 @@
+Variable_name Value
+lower_case_file_system ON
=== added file 'mysql-test/r/grant_lowercase_fs.result'
--- a/mysql-test/r/grant_lowercase_fs.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/grant_lowercase_fs.result 2009-10-02 08:45:03 +0000
@@ -0,0 +1,16 @@
+create database db1;
+GRANT CREATE ON db1.* to user_1@localhost;
+GRANT SELECT ON db1.* to USER_1@localhost;
+CREATE TABLE t1(f1 int);
+SELECT * FROM t1;
+ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't1'
+SELECT * FROM t1;
+f1
+CREATE TABLE t2(f1 int);
+ERROR 42000: CREATE command denied to user 'USER_1'@'localhost' for table 't2'
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_1@localhost;
+DROP USER user_1@localhost;
+DROP USER USER_1@localhost;
+DROP DATABASE db1;
+use test;
=== modified file 'mysql-test/r/lowercase_fs_off.result'
--- a/mysql-test/r/lowercase_fs_off.result 2006-11-14 18:45:52 +0000
+++ b/mysql-test/r/lowercase_fs_off.result 2009-10-02 08:45:03 +0000
@@ -10,3 +10,48 @@ create database D1;
ERROR 42000: Access denied for user 'sample'@'localhost' to database 'D1'
drop user 'sample'@'localhost';
drop database if exists d1;
+CREATE DATABASE d1;
+USE d1;
+CREATE TABLE T1(f1 INT);
+CREATE TABLE t1(f1 INT);
+GRANT SELECT ON T1 to user_1@localhost;
+select * from t1;
+ERROR 42000: SELECT command denied to user 'user_1'@'localhost' for table 't1'
+select * from T1;
+f1
+GRANT SELECT ON t1 to user_1@localhost;
+select * from information_schema.table_privileges;
+GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
+'user_1'@'localhost' NULL d1 T1 SELECT NO
+'user_1'@'localhost' NULL d1 t1 SELECT NO
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
+DROP USER user_1@localhost;
+DROP DATABASE d1;
+USE test;
+CREATE DATABASE db1;
+USE db1;
+CREATE PROCEDURE p1() BEGIN END;
+CREATE FUNCTION f1(i INT) RETURNS INT RETURN i+1;
+GRANT USAGE ON db1.* to user_1@localhost;
+GRANT EXECUTE ON PROCEDURE db1.P1 to user_1@localhost;
+GRANT EXECUTE ON FUNCTION db1.f1 to user_1@localhost;
+GRANT UPDATE ON db1.* to USER_1@localhost;
+call p1();
+call P1();
+select f1(1);
+f1(1)
+2
+call p1();
+ERROR 42000: execute command denied to user 'USER_1'@'localhost' for routine 'db1.p1'
+call P1();
+ERROR 42000: execute command denied to user 'USER_1'@'localhost' for routine 'db1.p1'
+select f1(1);
+ERROR 42000: execute command denied to user 'USER_1'@'localhost' for routine 'db1.f1'
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_1@localhost;
+DROP FUNCTION f1;
+DROP PROCEDURE p1;
+DROP USER user_1@localhost;
+DROP USER USER_1@localhost;
+DROP DATABASE db1;
+use test;
=== modified file 'mysql-test/r/ps_grant.result'
--- a/mysql-test/r/ps_grant.result 2005-11-28 11:31:11 +0000
+++ b/mysql-test/r/ps_grant.result 2009-10-02 08:45:03 +0000
@@ -32,19 +32,19 @@ identified by 'looser' ;
show grants for second_user@localhost ;
Grants for second_user@localhost
GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3'
-GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost'
GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost'
+GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost'
drop table mysqltest.t9 ;
show grants for second_user@localhost ;
Grants for second_user@localhost
GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3'
-GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost'
GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost'
+GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost'
show grants for second_user@localhost ;
Grants for second_user@localhost
GRANT USAGE ON *.* TO 'second_user'@'localhost' IDENTIFIED BY PASSWORD '*13843FE600B19A81E32AF50D4A6FED25875FF1F3'
-GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost'
GRANT SELECT ON `mysqltest`.`t9` TO 'second_user'@'localhost'
+GRANT SELECT ON `mysqltest`.`t1` TO 'second_user'@'localhost'
prepare s_t1 from 'select a as my_col from t1' ;
execute s_t1 ;
my_col
=== modified file 'mysql-test/r/system_mysql_db.result'
--- a/mysql-test/r/system_mysql_db.result 2005-10-13 14:16:19 +0000
+++ b/mysql-test/r/system_mysql_db.result 2009-10-02 08:45:03 +0000
@@ -151,7 +151,7 @@ procs_priv CREATE TABLE `procs_priv` (
`Host` char(60) collate utf8_bin NOT NULL default '',
`Db` char(64) collate utf8_bin NOT NULL default '',
`User` char(16) collate utf8_bin NOT NULL default '',
- `Routine_name` char(64) collate utf8_bin NOT NULL default '',
+ `Routine_name` char(64) character set utf8 NOT NULL default '',
`Routine_type` enum('FUNCTION','PROCEDURE') collate utf8_bin NOT NULL,
`Grantor` char(77) collate utf8_bin NOT NULL default '',
`Proc_priv` set('Execute','Alter Routine','Grant') character set utf8 NOT NULL default '',
=== added file 'mysql-test/t/grant_lowercase_fs.test'
--- a/mysql-test/t/grant_lowercase_fs.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/grant_lowercase_fs.test 2009-10-02 08:45:03 +0000
@@ -0,0 +1,30 @@
+-- source include/have_case_insensitive_fs.inc
+-- source include/not_embedded.inc
+
+
+#
+# Bug#41049 does syntax "grant" case insensitive?
+#
+create database db1;
+GRANT CREATE ON db1.* to user_1@localhost;
+GRANT SELECT ON db1.* to USER_1@localhost;
+
+connect (con1,localhost,user_1,,db1);
+CREATE TABLE t1(f1 int);
+--error 1142
+SELECT * FROM t1;
+connect (con2,localhost,USER_1,,db1);
+SELECT * FROM t1;
+--error 1142
+CREATE TABLE t2(f1 int);
+
+connection default;
+disconnect con1;
+disconnect con2;
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_1@localhost;
+DROP USER user_1@localhost;
+DROP USER USER_1@localhost;
+DROP DATABASE db1;
+use test;
=== modified file 'mysql-test/t/lowercase_fs_off.test'
--- a/mysql-test/t/lowercase_fs_off.test 2006-11-14 18:45:52 +0000
+++ b/mysql-test/t/lowercase_fs_off.test 2009-10-02 08:45:03 +0000
@@ -27,3 +27,65 @@ disconnect master;
connection default;
# End of 4.1 tests
+
+#
+# Bug#41049 does syntax "grant" case insensitive?
+#
+CREATE DATABASE d1;
+USE d1;
+CREATE TABLE T1(f1 INT);
+CREATE TABLE t1(f1 INT);
+GRANT SELECT ON T1 to user_1@localhost;
+
+connect (con1,localhost,user_1,,d1);
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from t1;
+select * from T1;
+connection default;
+GRANT SELECT ON t1 to user_1@localhost;
+connection con1;
+select * from information_schema.table_privileges;
+connection default;
+disconnect con1;
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
+DROP USER user_1@localhost;
+DROP DATABASE d1;
+USE test;
+
+CREATE DATABASE db1;
+USE db1;
+CREATE PROCEDURE p1() BEGIN END;
+CREATE FUNCTION f1(i INT) RETURNS INT RETURN i+1;
+
+GRANT USAGE ON db1.* to user_1@localhost;
+GRANT EXECUTE ON PROCEDURE db1.P1 to user_1@localhost;
+GRANT EXECUTE ON FUNCTION db1.f1 to user_1@localhost;
+GRANT UPDATE ON db1.* to USER_1@localhost;
+
+connect (con1,localhost,user_1,,db1);
+call p1();
+call P1();
+select f1(1);
+connect (con2,localhost,USER_1,,db1);
+--error ER_PROCACCESS_DENIED_ERROR
+call p1();
+--error ER_PROCACCESS_DENIED_ERROR
+call P1();
+--error ER_PROCACCESS_DENIED_ERROR
+select f1(1);
+
+connection default;
+disconnect con1;
+disconnect con2;
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_1@localhost;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM USER_1@localhost;
+DROP FUNCTION f1;
+DROP PROCEDURE p1;
+DROP USER user_1@localhost;
+DROP USER USER_1@localhost;
+DROP DATABASE db1;
+use test;
+
+# End of 5.0 tests
=== modified file 'scripts/mysql_system_tables.sql'
--- a/scripts/mysql_system_tables.sql 2008-12-17 20:01:34 +0000
+++ b/scripts/mysql_system_tables.sql 2009-10-02 08:45:03 +0000
@@ -57,5 +57,5 @@ CREATE TABLE IF NOT EXISTS time_zone_lea
CREATE TABLE IF NOT EXISTS proc ( db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns char(64) DEFAULT '' NOT NULL, body longblob NOT NULL, definer char(77) collate utf8_bin DEFAULT '' NOT NULL, created timestamp, modified timestamp, sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'NOT_USED', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO!
_AUTO_VAL
-CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) binary DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp(14), PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges';
+CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(16) binary DEFAULT '' NOT NULL, Routine_name char(64) COLLATE utf8_general_ci DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE') NOT NULL, Grantor char(77) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp(14), PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges';
=== modified file 'scripts/mysql_system_tables_fix.sql'
--- a/scripts/mysql_system_tables_fix.sql 2007-05-19 16:15:08 +0000
+++ b/scripts/mysql_system_tables_fix.sql 2009-10-02 08:45:03 +0000
@@ -323,6 +323,10 @@ ALTER TABLE procs_priv
MODIFY Proc_priv set('Execute','Alter Routine','Grant')
COLLATE utf8_general_ci DEFAULT '' NOT NULL;
+ALTER IGNORE TABLE procs_priv
+ MODIFY Routine_name char(64)
+ COLLATE utf8_general_ci DEFAULT '' NOT NULL;
+
ALTER TABLE procs_priv
ADD Routine_type enum('FUNCTION','PROCEDURE')
COLLATE utf8_general_ci NOT NULL AFTER Routine_name;
=== modified file 'sql/sql_acl.cc'
--- a/sql/sql_acl.cc 2009-08-28 15:51:31 +0000
+++ b/sql/sql_acl.cc 2009-10-02 08:45:03 +0000
@@ -148,8 +148,7 @@ my_bool acl_init(bool dont_read_acl_tabl
acl_cache= new hash_filo(ACL_CACHE_SIZE, 0, 0,
(hash_get_key) acl_entry_get_key,
(hash_free_key) free,
- lower_case_file_system ?
- system_charset_info : &my_charset_bin);
+ &my_charset_utf8_bin);
if (dont_read_acl_tables)
{
DBUG_RETURN(0); /* purecov: tested */
@@ -2092,8 +2091,8 @@ public:
ulong sort;
uint key_length;
GRANT_NAME(const char *h, const char *d,const char *u,
- const char *t, ulong p);
- GRANT_NAME (TABLE *form);
+ const char *t, ulong p, bool is_routine);
+ GRANT_NAME (TABLE *form, bool is_routine);
virtual ~GRANT_NAME() {};
virtual bool ok() { return privs != 0; }
};
@@ -2115,7 +2114,7 @@ public:
GRANT_NAME::GRANT_NAME(const char *h, const char *d,const char *u,
- const char *t, ulong p)
+ const char *t, ulong p, bool is_routine)
:privs(p)
{
/* Host given by user */
@@ -2127,6 +2126,9 @@ GRANT_NAME::GRANT_NAME(const char *h, co
if (lower_case_table_names)
{
my_casedn_str(files_charset_info, db);
+ }
+ if (lower_case_table_names || is_routine)
+ {
my_casedn_str(files_charset_info, tname);
}
key_length =(uint) strlen(d)+(uint) strlen(u)+(uint) strlen(t)+3;
@@ -2137,14 +2139,14 @@ GRANT_NAME::GRANT_NAME(const char *h, co
GRANT_TABLE::GRANT_TABLE(const char *h, const char *d,const char *u,
const char *t, ulong p, ulong c)
- :GRANT_NAME(h,d,u,t,p), cols(c)
+ :GRANT_NAME(h,d,u,t,p, FALSE), cols(c)
{
(void) hash_init(&hash_columns,system_charset_info,
0,0,0, (hash_get_key) get_key_column,0,0);
}
-GRANT_NAME::GRANT_NAME(TABLE *form)
+GRANT_NAME::GRANT_NAME(TABLE *form, bool is_routine)
{
update_hostname(&host, get_field(&memex, form->field[0]));
db= get_field(&memex,form->field[1]);
@@ -2162,6 +2164,9 @@ GRANT_NAME::GRANT_NAME(TABLE *form)
if (lower_case_table_names)
{
my_casedn_str(files_charset_info, db);
+ }
+ if (lower_case_table_names || is_routine)
+ {
my_casedn_str(files_charset_info, tname);
}
key_length = ((uint) strlen(db) + (uint) strlen(user) +
@@ -2174,7 +2179,7 @@ GRANT_NAME::GRANT_NAME(TABLE *form)
GRANT_TABLE::GRANT_TABLE(TABLE *form, TABLE *col_privs)
- :GRANT_NAME(form)
+ :GRANT_NAME(form, FALSE)
{
byte key[MAX_KEY_LENGTH];
@@ -3159,7 +3164,7 @@ bool mysql_routine_grant(THD *thd, TABLE
}
grant_name= new GRANT_NAME(Str->host.str, db_name,
Str->user.str, table_name,
- rights);
+ rights, TRUE);
if (!grant_name)
{
result= TRUE;
@@ -3384,13 +3389,13 @@ static my_bool grant_load(TABLE_LIST *ta
THR_MALLOC);
DBUG_ENTER("grant_load");
- (void) hash_init(&column_priv_hash,system_charset_info,
+ (void) hash_init(&column_priv_hash, &my_charset_utf8_bin,
0,0,0, (hash_get_key) get_grant_table,
(hash_free_key) free_grant_table,0);
- (void) hash_init(&proc_priv_hash,system_charset_info,
+ (void) hash_init(&proc_priv_hash, &my_charset_utf8_bin,
0,0,0, (hash_get_key) get_grant_table,
0,0);
- (void) hash_init(&func_priv_hash,system_charset_info,
+ (void) hash_init(&func_priv_hash, &my_charset_utf8_bin,
0,0,0, (hash_get_key) get_grant_table,
0,0);
init_sql_alloc(&memex, ACL_ALLOC_BLOCK_SIZE, 0);
@@ -3446,7 +3451,7 @@ static my_bool grant_load(TABLE_LIST *ta
{
GRANT_NAME *mem_check;
HASH *hash;
- if (!(mem_check=new GRANT_NAME(p_table)))
+ if (!(mem_check=new GRANT_NAME(p_table, TRUE)))
{
/* This could only happen if we are out memory */
grant_option= FALSE;
@@ -5784,7 +5789,7 @@ bool sp_revoke_privileges(THD *thd, cons
for (counter= 0, revoked= 0 ; counter < hash->records ; )
{
GRANT_NAME *grant_proc= (GRANT_NAME*) hash_element(hash, counter);
- if (!my_strcasecmp(system_charset_info, grant_proc->db, sp_db) &&
+ if (!my_strcasecmp(&my_charset_utf8_bin, grant_proc->db, sp_db) &&
!my_strcasecmp(system_charset_info, grant_proc->tname, sp_name))
{
LEX_USER lex_user;
Attachment: [text/bzr-bundle] bzr/sergey.glukhov@sun.com-20091002084503-9xzz19qhhcg0t8mx.bundle
