Bug #895 when anonymous user has all privs on some db, all users have
Submitted: 23 Jul 2003 5:29 Modified: 21 Aug 2003 1:32
Reporter: SINISA MILIVOJEVIC Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:Any (all)
Assigned to: Sergei Golubchik CPU Architecture:Any

[23 Jul 2003 5:29] SINISA MILIVOJEVIC
Description:
I think that is because if I grant privileges on column level there is no any 
entry for this user on the database level, but anonymous user has.
So, MySQL accept connection as 'vita'@'localhost', but when MySQl server 
checks privileges when I try to do something, it finds entry in the db table 
for ''@'%' with all privileges on this database.
But I thought that if MySQl server accept connection for 'vita'@'localhost', 
it will check privileges exactly for the 'vita'@'localhost'.

How to repeat:

mysql> grant usage on *.* to user@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select(msg_nro) on telcent.mails to user@localhost;
Query OK, 0 rows affected (0.02 sec)

[/mnt/work/mysql-4.0]$ ./client/mysql telcent -u user
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.14-debug

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

mysql> select * from nazivi;
ERROR 1142: select command denied to user: 'user@localhost' for table 'nazivi'
mysql> select * from mails;
ERROR 1143: select command denied to user: 'user@localhost' for column 'date' in table 'mails'
mysql> select msg_nro from mails;
......................Works...............................

mysql> grant usage on *.* to ``@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on  telcent.* to ``@localhost;
Query OK, 0 rows affected (0.00 sec)

/mnt/work/mysql-4.0]$ ./client/mysql telcent -u user
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.14-debug

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

mysql> select * from mails;
.................Works, but should not .........................
[25 Jul 2003 12:26] Sergei Golubchik
fixed in 4.0.15
[11 Aug 2003 13:38] Sergei Golubchik
no, it's not fixed yet :)
[21 Aug 2003 1:32] Michael Widenius
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

In MySQL the anonymous users plays a very important role:
It allows everyone to get access to the test database (and other databases for global data like temporary tables) without having to explicitely add these grants to all users.

This means that when you are autenticated as a users, you also have the database privileges for the anonymous users to databases to which you don't have any privileges.

In other words, it's a bit like the anonymous users is a default ROLE that everyone has.

We will in future MySQL version add better SHOW GRANT commands to make it easier to check and maintain the anonymous users.  In MySQL 5.0 we will add ROLES and we plan to integrate the anonymous users as a default ROLE, which should make this a bit clearer.

Regards,
Monty