I have a problem with Mysql-3.23.39 and users access rights. It's
the second time I notice this problem since I installed .39 on two
of my servers. But yesterday, the bug was even more flagrant.
I had a user which had all rights on a database when connecting from
localhost and from a local network say 10.0.0.% . This way
everything worked fine, user being able to do inserts, updates,
deletes, ... on all tables of the database.
Then I wanted to grant rights to this user from a remote IP not
in the local network. But, for security reason I wanted to give only
SELECT right to the user from this remote IP. So I did something
GRANT SELECT on database.* to user@'remoteIP' identified by 'password'
Here starts the problems !
Just after I did this GRANT, I noticed that some of my programs
which are using this user to log had no more the rights to update,
delete or insert some tables :
Query: UPDATE livequote_PAR SET last=36.28,tot_volume=33208,tradedate=concat(CURDATE(), '
9:26:29') WHERE symbole='1rPEN'
Erreur : update command denied to user: 'user@localhost' for table 'livequote_PAR'
While as you can see, these programs are running, and so connecting,
from localhost ...
And more strange, not all the programs were affected by the GRANT.
I did some more tests and then I found that when I use this user to
connect from localhost, sometimes it is allowed to update tables,
sometimes not. It depends on the connection. Once I connect and I
will not be allowed to update tables, then I reconnect and I will
have rights to updates tables ... and so on.
When I check "mysql" database and tables "user","db"... everything
is ok. It's telling that the user from localhost and local network
has all rights, and that the same user from a given remote IP is
allowed only to SELECT.
What's happening ?