MySQL Bugs: #45100: Incomplete DROP USER in case of SQL_MODE = 'PAD_CHAR_TO_FULL

Bug #45100	Incomplete DROP USER in case of SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'
Submitted:	26 May 2009 15:06	Modified:	13 Jul 2009 19:43
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	PAD_CHAR_TO_FULL_LENGTH

Description:
According to the manual
   http://dev.mysql.com/doc/refman/6.0/en/drop-user.html
a DROP USER should also remove all
privileges granted to this user.

Edited snip from my test protocol:
----------------------------------
CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD';
CREATE SCHEMA mysql_test1;
GRANT ALL ON mysql_test1.* TO 'user_PCTFL'@'localhost';

SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH';
DROP USER 'user_PCTFL'@'localhost';
SET SESSION SQL_MODE = '';

SELECT * FROM mysql.db 
WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
Host      Db          User       ....
localhost mysql_test1 user_PCTFL ....

This means not all privileges were removed.
The problem does not happen when running
DROP USER in SQL_MODE = ''.

My environment:
- mysql-5.1-bugteam last change 2009-05-15
  ./compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

MySQL 5.0 cannot have this problem because the
PAD_CHAR_TO_FULL_LENGTH is there not available.
I guess this bug affects
- also MySQL 6.0
- all supported OS

IMHO not removing all privileges (especially when
they are schema/table/column related) is a significant
security hole. If a new user with the same name gets
created than he will be able to exploit the non
removed permissions.

How to repeat:
Either see above or use the attached test.

Test scripts and protocols

Attachment: ml002.tgz (application/x-compressed-tar, text), 1.99 KiB.

i also had a problem a while ago with this code affecting privileges, obviously the fix was not good enough: bug #32753

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/76220

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
      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.

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/76222

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.

Queued to 5.1-bugteam

Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:davi.arnaut@sun.com-20090612211119-jrxxjz0fx90hj9lv) (merge vers: 5.1.36) (pib:6)

Noted in 5.1.36 changelog.

DROP USER could fail to drop all privileges for an account if the 
PAD_CHAR_TO_FULL_LENGTH SQL mode was enabled.

Setting report to NDI pending push into 5.4.x.

Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:davi.arnaut@sun.com-20090612212406-qk31s03b275qlxov) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.4.4 changelog.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed to 5.4.2.