Bug #2546 Table grant fails to be saved into tables_priv table
Submitted: 28 Jan 2004 10:26 Modified: 6 Feb 2004 10:53
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1 OS:Any (any)
Assigned to: Sinisa Milivojevic CPU Architecture:Any

[28 Jan 2004 10:26] Sinisa Milivojevic
Description:
If user has some global grants and SELECT grants on a database. 

Then when table grant on the table n the same database is made, it is shown
with SHOW GRANTS.

However, on server re-start this grant is not there.

How to repeat:
mysql> show grants for some_user@localhost;
+---------------------------------------------------------------------------
---------------------------------------------------------------------+
| Grants for some_user@localhost
|
+---------------------------------------------------------------------------
---------------------------------------------------------------------+
| GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO
'some_user'@'localhost' IDENTIFIED BY PASSWORD
'*43933BDF3E95B05EC8BE52E6AEE83DB1B1E309CA' |
| GRANT SELECT ON `some_db`.* TO 'some_user'@'localhost'
|
+---------------------------------------------------------------------------
---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant all on some_db.some_table to 'some_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for some_user@localhost;
+---------------------------------------------------------------------------
--+
| Grants for some_user@localhost
|
+---------------------------------------------------------------------------
--+
| GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO
'some_user'@'localhost' |
| GRANT SELECT ON `some_db`.* TO 'some_user'@'localhost'
|
| GRANT ALL PRIVILEGES ON `some_db`.`some_table` TO 'some_user'@'localhost'
|
+---------------------------------------------------------------------------
--+
3 rows in set (0.00 sec)

mysql> exit
Bye
# /etc/init.d/mysql restart
Killing mysqld with pid 4103
Wait for mysqld to exit\c
040127 12:33:04  mysqld ended

 done
# Starting mysqld daemon with databases from /var/lib/mysql

# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.1-alpha-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants for some_user@localhost;
+---------------------------------------------------------------------------
---------------------------------------------------------------------+
| Grants for some_user@localhost
|
+---------------------------------------------------------------------------
---------------------------------------------------------------------+
| GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO
'some_user'@'localhost' IDENTIFIED BY PASSWORD
'*43933BDF3E95B05EC8BE52E6AEE83DB1B1E309CA' |
| GRANT SELECT ON `some_db`.* TO 'some_user'@'localhost'
|
+---------------------------------------------------------------------------
---------------------------------------------------------------------+
2 rows in set (0.00 sec)
[29 Jan 2004 8:24] Sinisa Milivojevic
A patch has been proposed that will fix this error.
[6 Feb 2004 10:53] Sinisa Milivojevic
A patch pushed.
[25 Feb 2004 13:03] Charles Bailey
The bug does not depend on the user having other privileges on the database in question; the server will not read tables_priv even if the user has no other privileges on that database.