Bug #473 Failure of GRANT to refresh grant tables

• Submitted: 21 May 2003 9:12
• Modified: 21 Jul 2003 4:44
• Reporter: Paul DuBois
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 3.23.x,4.0.x,4.1.x
• OS: Unix, at least
• Assigned to: Sergei Glukhov
• CPU Architecture: Any

[21 May 2003 9:12] Paul DuBois

Description:
Date: Wed, 21 May 2003 11:02:25 -0500 (CDT)
From: Software Development <[e-mail]>
To: [e-mail]

Stefan Hinz and I were investigating why an anonymous-user
account didn't seem to be acting as an "allow any username"
account the way it should.  It turns out that the problem
is that GRANT did not cause the grant tables to be
refreshed properly.  The how-to-repeat scenario illustrates
this:

- Begin with just the root accounts
- Use GRANT to create an anonymous-user account
- The anonymous-user account doesn't work
- Flush privileges explicitly
- Now the anonymous-user account works

How to repeat:

Here's now to duplicate the problem. I used 4.0.12 to generate
this, but the same thing occurs with 3.23.56 and 4.1.0.

The initial state of the user table is like this:

% ./mysql -u root -p mysql
Enter password: 

mysql> SELECT Host, User, Password FROM user;
+----------------+------+------------------+
| Host           | User | Password         |
+----------------+------+------------------+
| localhost      | root | 15d4360e48b6b6f8 |
| onyx.snake.net | root | 15d4360e48b6b6f8 |
+----------------+------+------------------+
2 rows in set (0.01 sec)

Create an anonymous-user account:

mysql> GRANT ALL ON *.* TO ''@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT Host, User, Password FROM user;
+----------------+------+------------------+
| Host           | User | Password         |
+----------------+------+------------------+
| localhost      | root | 15d4360e48b6b6f8 |
| onyx.snake.net | root | 15d4360e48b6b6f8 |
| localhost      |      |                  |
+----------------+------+------------------+
3 rows in set (0.01 sec)

mysql> QUIT
Bye

Try to connect using an arbitary username; it doesn't work:

% ./mysql -u ANYUSER
ERROR 1045: Access denied for user: 'ANYUSER@localhost' (Using password: NO)
% ./mysql -u ANYUSER -h 127.0.0.1
ERROR 1045: Access denied for user: 'ANYUSER@localhost' (Using password: NO)

Flush the privileges explicitly:

% ./mysql -u root -p
Enter password: 

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> QUIT
Bye

Now the anonymous-user account works!

% ./mysql -u ANYUSER

mysql> QUIT
Bye

% ./mysql -u ANYUSER -h 127.0.0.1

mysql> quit
Bye

[2 Jun 2003 6:02] Sergei Glukhov

Here is the suggested fix for 4.0.x

===== sql/sql_acl.cc 1.97 vs edited =====
*** /tmp/sql_acl.cc-1.97-24212	Thu May  8 02:00:38 2003
--- edited/sql/sql_acl.cc	Mon Jun  2 17:52:04 2003
***************
*** 515,521 ****
    for (uint i=0 ; i < acl_users.elements ; i++)
    {
      ACL_USER *acl_user=dynamic_element(&acl_users,i,ACL_USER*);
!     if (!acl_user->user || !strcmp(user,acl_user->user))
      {
        if (compare_hostname(&acl_user->host,host,ip))
        {
--- 515,521 ----
    for (uint i=0 ; i < acl_users.elements ; i++)
    {
      ACL_USER *acl_user=dynamic_element(&acl_users,i,ACL_USER*);
!     if (!acl_user->user || !acl_user->user[0] || !strcmp(user,acl_user->user))
      {
        if (compare_hostname(&acl_user->host,host,ip))
        {

[21 Jul 2003 4:44] Sergei Golubchik

fixed in 4.0.15