MySQL Bugs: #6123: 'grant usage...' command acts like 'revoke' command

Bug #6123	'grant usage...' command acts like 'revoke' command
Submitted:	16 Oct 2004 3:54	Modified:	11 Nov 2004 21:02
Reporter:	jerry marbas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	4.0.21-debug	OS:	Windows (windows 2000)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
When I used 'grant usage...' repeatedly it would insert a line into the db table in the mysql database,... then remove it.... then add it again... then remove it. It acts like revoke command every second time because it removes the record from the db table...

grant usage on books.* to sally identified by 'magic123';

-the above query adds a record to the db table in the mysql database

select host,db,user,select_priv,insert_priv from db;

-the above query shows this added record

now repeat the first query....
grant usage on books.* to sally identified by 'magic123';

and then repeat the second query....
select host,db,user,select_priv,insert_priv from db;

it toggles the record stored in the db table!

why! :D

How to repeat:
please see above

Suggested fix:
the grant usage command shouldnt behave like the revoke command by deleting records from the mysql database tables... should it?

Verified on Linux with 4.0 from bk tree
ChangeSet@1.2038, 2004-10-12 18:12:00+03:00, heikki@hundin.mysql.fi

mysql> grant usage on books.* to sally identified by 'magic123';
Query OK, 0 rows affected (0.01 sec)

mysql> select host,db,user,select_priv,insert_priv from db where user = 'sally';+------+-------+-------+-------------+-------------+
| host | db    | user  | select_priv | insert_priv |
+------+-------+-------+-------------+-------------+
| %    | books | sally | N           | N           |
+------+-------+-------+-------------+-------------+

This is a bug because USAGE is global privilege only and it should not add any rows to mysql.db

SHOW GRANTS after the above sequence returns correct information:
mysql> show grants for sally; +-----------------------------------------------------------------------------+
| Grants for sally@%                                                          |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sally'@'%' IDENTIFIED BY PASSWORD '550331e54525bfa3' |
+-----------------------------------------------------------------------------+

mysql> grant usage on books.* to sally identified by 'magic123'; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> select host,db,user,select_priv,insert_priv from db where user = 'sally';
Empty set (0.00 sec)

Like the user reported the redunant line in mysql.db disappears after second GRANT USAGE

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