MySQL Bugs: #2178: Table- and column-specific privileges aren't loaded from corresponding tables

Bug #2178	Table- and column-specific privileges aren't loaded from corresponding tables
Submitted:	21 Dec 2003 7:23	Modified:	24 Dec 2003 0:39
Reporter:	Alex Scherbatey	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1	OS:	Any (Any)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hello!

I've got a problem with privileges in MySQL 4.1.1 on Windows XP and FreeBSD.
Immediately after creating users with table- or column-specific privileges everything works fine, but after restarting server these priveleges are not loaded. Even FLUSH PRIVILEGES doesn't help, although corresponding records in `tables_priv` and `columns_priv` exist. The only way to re-enable privileges is to launch GRANT ... query again!

Thank you for help!

Alex

How to repeat:
Create test database and user with following query, then stop the server and launch it again. Look at privileges for 'bug_test_usr'@'%' (SHOW GRANTS FOR 'bug_test_usr'@'%')

#-------------------------------------------------------------

CREATE DATABASE `bug_test_db`;

USE `bug_test_db`;

CREATE TABLE `bug_test_tbl` (
  `f1` int(10) unsigned NOT NULL default '0',
  `f2` varchar(255) binary NOT NULL default '',
  PRIMARY KEY  (`f1`)
) TYPE=MyISAM DEFAULT CHARSET=latin1;

GRANT USAGE ON *.* TO 'bug_test_usr'@'%' IDENTIFIED BY '' ;

GRANT SELECT ON `bug_test_db` . * TO 'bug_test_usr'@'%';

GRANT INSERT,
UPDATE (`f2`)
 ON `bug_test_db` . `bug_test_tbl` TO 'bug_test_usr'@'%';

Suggested fix:
???

Extending How-to-repeat secton.

After all statements Alex sent do the following:

mysql> show grants for 'bug_test_usr'@'%';
+----------------------------------------------------------------------------+
| Grants for bug_test_usr@%                                                  |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bug_test_usr'@'%'                                   |
| GRANT SELECT ON `bug_test_db`.* TO 'bug_test_usr'@'%'                      |
| GRANT INSERT, UPDATE ON `bug_test_db`.`bug_test_tbl` TO 'bug_test_usr'@'%' |
+----------------------------------------------------------------------------+

Restart mysqld, login and repeat:

mysql> show grants for 'bug_test_usr'@'%';
+-------------------------------------------------------+
| Grants for bug_test_usr@%                             |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bug_test_usr'@'%'              |
| GRANT SELECT ON `bug_test_db`.* TO 'bug_test_usr'@'%' |
+-------------------------------------------------------+

As Alex reported table privileges " are not loaded
loaded ... although corresponding records in
`tables_priv` and `columns_priv` exist."

mysql> select * from mysql.tables_priv where user = 'bug_test_usr';
+------+-------------+--------------+--------------+----------------+---------------------+------------+-------------+
| Host | Db          | User         | Table_name   | Grantor        | Timestamp           | Table_priv | Column_priv |
+------+-------------+--------------+--------------+----------------+---------------------+------------+-------------+
| %    | bug_test_db | bug_test_usr | bug_test_tbl | root@localhost | 2003-12-22 17:37:12 | Insert     | Update      |
+------+-------------+--------------+--------------+----------------+---------------------+------------+-------------+

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will come in 4.1.2.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Fix will be in 4.1.2

In case it's not already fixed on the 5.0 branch, the 5.0.0-alpha server has the same bug.  Thanks.