MySQL Bugs: #242: MySQL privileges can't work normally

Bug #242	MySQL privileges can't work normally
Submitted:	7 Apr 2003 19:03	Modified:	28 Oct 2003 4:55
Reporter:	tony chen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Windows (win98)
Assigned to:		CPU Architecture:	Any

Description:
MySQL privileges can't work normally:

#TABLE:   user

host        user   password              sele_priv  ......
-----------------------------------------------------------------
localhost   root                          Y        Y   ....  Y
%           root                          Y        Y   ..... Y
%           hotel  0e25606a4b3125f4       N        N   .....  N

#TABLE  db

host        db      user              sele_priv .......
------------------------------------------------------------------
%           test                         Y       Y   ....    Y
%           hotel   hotel                Y       Y   ....    Y

#TABLE host, column_priv, table_priv are empty

flush privileges

mysql --user=hotel -photel --host=localhost

I can access mysql database sometime and can't access sometime

How to repeat:

It appears you did an 'upgrade' install and did not run the mysql_fix_privilege_tables.sql script as instructed in the README.

I download ZIP FILES ON www.mysql.com and did a fresh install yestoday.

d:\mysql\bin> mysqld-opt

d:\mysql\bin> mysql --user=root -p
##
mysql> use mysql;
mysql> delete from user where user='';
mysql> insert into user (host,user,password) values ('%','hotel',old_password('hotel'));
mysql> insert into db values ('%','hotel','hotel','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> flush privileges;
mysql> create database hotel;
mysql> use hotel;
mysql> create table .....
mysql> \q

##
I connect MYSQL SERVER using mysql-front2.5 or mysql on same machine. 
There are two NIC (192.168.1.1 and 192.168.12.98) on my machine.
##
mysql --user=hotel -photel --host=192.168.12.98  
mysql> use mysql;  
ok!
mysql> select * from mysql;  
.....
ok! 
\q

Sometime It work normally

The problem is probably related to wrong combination
of username/password/hostname.

A test case is needed; when available, please repost.

Regards,
Jani

You've used the 'old_password()' function, which won't work, because 4.1 uses a new password scheme. 

You should also use the GRANT statement instead of manipulating the host, user and db tables when you can, because it won't make mistakes like this.

I've tested the case and I can get access all the time.
Could you please tell what do you mean by "can't get access"
Any particular error message ?

ALL BINARY EXTRACT FROM "mysql-4.1.0-alpha.zip(10.7M)"

mysql>update user set password=password('hotel') where   user='hotel' 

mysql>select * from user;
host        user   password              sele_priv  ......
------------------------------------------------------------localhost   root                          Y Y...Y     %           root                          Y Y...Y       %           hotel  *47f1aabd26adf4ad3d8deb51c8627a5fd68e51e3104a    N N ..N       

mysql>select * from db

host        db      user              sele_priv .......
------------------------------------------------------------%           test                         Y Y..Y      %           hotel   hotel                Y Y..Y      

#TABLE host, column_priv, table_priv are empty

mysql>flush privileges;
OK!
mysql>\q

TEST A:  (correct)
mysql --user=hotel -photel
mysql>use mysql;
ERROR 1044: access deninied for user: 'hotel@127.0.0.1' to database 'mysql'
mysql>\q

TEST B  (incorrect?)
mysql --user=hotel -photel --host=192.168.12.98
mysql> use mysql
ok!
mysql> select * from user
....
ok!
\q

TEST C  (incorrect?)
mysql --user=hotel -photel --host=192.168.1.1
mysql> use mysql
ok!
mysql> select * from user
....
ok!
\q

TEST D  (incorrect?)
mysql --user=hotel -photel 
mysql> use mysql
ok!
mysql> select * from user
....
ok!
\q

Please wait for about 60 minutes OR restart mysqld

TEST E: (puzzle?)
mysql --user=hotel -photel 
mysql> use mysql    
ERROR 1044: access deninied for user: 'hotel@127.0.0.1' to database 'mysql'
### it is correct
mysql> use hotel
ERROR 1044:access deninied for user: 'hotel@127.0.0.1' to database 'hotel'
## it is incorrect?

mysql>select * from user;
host       user   password              sele_priv  ......
------------------------------------------------------------localhost  root                          Y Y...Y
     
%          root                          Y Y...Y   

%          hotel 
*47f1aabd26adf4ad3d8deb51c8627a5fd68e51e3104a    N N ..N       

mysql>select * from db

host        db      user              sele_priv .......
------------------------------------------------------------%         test                         Y Y..Y     
%         hotel   hotel                Y Y..Y      

#TABLE host, column_priv, table_priv are empty

everything with the word "sometimes" and the version 4.1.0 will not apply to 4.1.1, as 4.1.1 has completely different authentication code.