MySQL Bugs: #29869: Users with no privileges can do operations

Bug #29869	Users with no privileges can do operations
Submitted:	18 Jul 2007 13:41	Modified:	18 Jul 2007 16:44
Reporter:	gio monte	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.0.41	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Users with no privileges (GRANT USAGE on *.* to 'foo'.'%')
are not prevented from performing operations like SELECT,INSERT,UPDATE,etc.

This happens either if user is created with no privilege, or if existing privileges are revoked with REVOKE ALL PRIVILEGES command.

How to repeat:
Install and start version 5.0.41
Login as root
Create a new user
> CREATE USER foo IDENTIFIED BY 'foo';
Query OK, 0 rows affected (0.00 sec)

> SHOW GRANTS FOR foo;
GRANT USAGE ON *.* TO 'foo'@'%' IDENTIFIED BY PASSWORD [...

(Login as foo and select database)
> mysql --user=foo --password=foo

> USE test
Database changed.

(Create a table)
> CREATE TABLE test.mytable (pk SMALLINT, PRIMARY KEY(pk));
Query OK, 0 rows affected (0.07 sec)

(Insert some data)
> INSERT INTO mytable VALUES (100,101);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

(Drop database)
> DROP DATABASE test;
Query OK, 2 rows affected (0.02 sec)

(Check privileges)
> SHOW GRANTS;
GRANT USAGE ON *.* TO 'foo'@'%' IDENTIFIED BY PASSWORD [...

Thank you for the bug report. Please read the Manual regarding default
privileges for database called test:

http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

"it has all global privileges, just like the root accounts.) The other is for connections from any host and has all privileges for the test database and for other databases with names that start with test. "