bzr commit into mysql-5.1-bugteam branch (davi:2940) Bug#45100

[Davi Arnaut]

# At a local mysql-5.1-bugteam repository of davi

 2940 Davi Arnaut	2009-06-12
      Bug#45100: Incomplete DROP USER in case of SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'
      
      The SQL-mode PAD_CHAR_TO_FULL_LENGTH could prevent a DROP USER
      statement from privileges associated with the user being dropped.
      What ocurred was that reading from the User and Host fields of
      the tables tables_priv or columns_priv would yield values padded
      with spaces, causing a failure to match a specified user or host 
      ('user' != 'user     ');
      
      The solution is to disregard the PAD_CHAR_TO_FULL_LENGTH mode
      when iterating over and matching values in the privileges tables
      for a DROP USER statement.
     @ mysql-test/r/sql_mode.result
        Add test case result for Bug#45100.
     @ mysql-test/t/sql_mode.test
        Add test case for Bug#45100.
     @ sql/sql_acl.cc
        Clear MODE_PAD_CHAR_TO_FULL_LENGTH before dropping privileges.

    modified:
      mysql-test/r/sql_mode.result
      mysql-test/t/sql_mode.test
      sql/sql_acl.cc
=== modified file 'mysql-test/r/sql_mode.result'
--- a/mysql-test/r/sql_mode.result	2008-02-24 12:58:07 +0000
+++ b/mysql-test/r/sql_mode.result	2009-06-12 21:11:19 +0000
@@ -506,3 +506,24 @@ mysqltest_32753@localhost
 set session sql_mode=@OLD_SQL_MODE;
 flush privileges;
 drop user mysqltest_32753@localhost;
+DROP TABLE IF EXISTS t1,t2;
+CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD';
+CREATE USER 'user_no_PCTFL'@'localhost' identified by 'PWD';
+CREATE TABLE t1 (f1 BIGINT);
+CREATE TABLE t2 (f1 CHAR(3) NOT NULL, f2 CHAR(20));
+GRANT ALL ON t1 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost';
+GRANT SELECT(f1) ON t2 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost';
+SET @OLD_SQL_MODE = @@SESSION.SQL_MODE;
+SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH';
+DROP USER 'user_PCTFL'@'localhost';
+SET SESSION SQL_MODE = @OLD_SQL_MODE;
+DROP USER 'user_no_PCTFL'@'localhost';
+FLUSH PRIVILEGES;
+SELECT * FROM mysql.db WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
+Host	Db	User	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Create_tmp_table_priv	Lock_tables_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Execute_priv	Event_priv	Trigger_priv
+SELECT * FROM mysql.tables_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
+Host	Db	User	Table_name	Grantor	Timestamp	Table_priv	Column_priv
+SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
+Host	Db	User	Table_name	Column_name	Timestamp	Column_priv
+DROP TABLE t1;
+DROP TABLE t2;

=== modified file 'mysql-test/t/sql_mode.test'
--- a/mysql-test/t/sql_mode.test	2008-02-24 12:58:07 +0000
+++ b/mysql-test/t/sql_mode.test	2009-06-12 21:11:19 +0000
@@ -308,3 +308,39 @@ flush privileges;
 
 --connection default
 drop user mysqltest_32753@localhost;
+
+#
+# Bug#45100: Incomplete DROP USER in case of SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+
+# Generate some prerequisites
+CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD';
+CREATE USER 'user_no_PCTFL'@'localhost' identified by 'PWD';
+
+CREATE TABLE t1 (f1 BIGINT);
+CREATE TABLE t2 (f1 CHAR(3) NOT NULL, f2 CHAR(20));
+
+# Grant privilege on a TABLE
+GRANT ALL ON t1 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost';
+# Grant privilege on some COLUMN of a table
+GRANT SELECT(f1) ON t2 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost';
+
+SET @OLD_SQL_MODE = @@SESSION.SQL_MODE;
+SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH';
+DROP USER 'user_PCTFL'@'localhost';
+SET SESSION SQL_MODE = @OLD_SQL_MODE;
+DROP USER 'user_no_PCTFL'@'localhost';
+
+FLUSH PRIVILEGES;
+
+SELECT * FROM mysql.db WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
+SELECT * FROM mysql.tables_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
+SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
+
+# Cleanup
+DROP TABLE t1;
+DROP TABLE t2;

=== modified file 'sql/sql_acl.cc'
--- a/sql/sql_acl.cc	2009-06-04 05:54:23 +0000
+++ b/sql/sql_acl.cc	2009-06-12 21:11:19 +0000
@@ -5696,6 +5696,7 @@ bool mysql_drop_user(THD *thd, List <LEX
   List_iterator <LEX_USER> user_list(list);
   TABLE_LIST tables[GRANT_TABLES];
   bool some_users_deleted= FALSE;
+  ulong old_sql_mode= thd->variables.sql_mode;
   DBUG_ENTER("mysql_drop_user");
 
   /*
@@ -5709,6 +5710,8 @@ bool mysql_drop_user(THD *thd, List <LEX
   if ((result= open_grant_tables(thd, tables)))
     DBUG_RETURN(result != 1);
 
+  thd->variables.sql_mode&= ~MODE_PAD_CHAR_TO_FULL_LENGTH;
+
   rw_wrlock(&LOCK_grant);
   VOID(pthread_mutex_lock(&acl_cache->lock));
 
@@ -5741,6 +5744,7 @@ bool mysql_drop_user(THD *thd, List <LEX
 
   rw_unlock(&LOCK_grant);
   close_thread_tables(thd);
+  thd->variables.sql_mode= old_sql_mode;
   DBUG_RETURN(result);
 }
 


Attachment: [text/bzr-bundle] bzr/[e-mail]