MySQL user loses privileges

blacky

2017-10-23T22:22:55

I have really weird problem that I can't wrap my mind around. I'm using Debian Jessie for our servers. I've added testing repos and upgraded mysql version from 5.5 to 5.7.2 then setup private network between 2 servers:

Server A - 192.168.0.1 as database server

Server B - 192.168.0.2 as application server

I've created user and gave him all required privileges (i've tested multiple hosts and multiple variations of global and database privileges). Everything is working fine but randomly after couple days the user starts getting "Access denied" and can not log in to the database. Communication between servers works just fine, servers can ping each other and connection to mysql is not blocked as its sending Access Denied as response. I've flushed privileges many times and the user has no limits by default.

I've turned off firewall and checked system logs and I don't see anything even remotely related to my problem. I've tried all steps suggested by mysql docs where they suggest possible solutions to "Access Denied". I've tried with different app/database. I've tried many different users with both wildcard and classic hosts definitions, with different permissions and after couple days my application server is still getting "Access denied". If I log in locally on the database server and perform "Show grants" and "Show databases" everything seems fine - user has correct host and privileges yet can not log in remotely.

Application stack used for our apps is PHP with Symfony2/3. Is there anything I can do about it? Is there any software that could help me debug?

Copyright License：
Author:「blacky」,Reproduced under the CC 4.0 BY-SA copyright license with link to original source & disclaimer.
Link to：https://stackoverflow.com/questions/46891733/mysql-user-loses-privileges

About “MySQL user loses privileges” questions

MySQL user loses privileges

I have really weird problem that I can't wrap my mind around. I'm using Debian Jessie for our servers. I've added testing repos and upgraded mysql version from 5.5 to 5.7.2 then setup private network

MySQL Grant User Privileges Error

I have a question regarding how to grant privileges MySQL user accounts. I have a MySQL user account, and in my example code I would like to grant privileges to the whole database. When I run the c...

Grant privileges to user in MySQL

From the control panel of my website I have created a new MySQL(5) database Test and a new user admin with password 123. I have tried assigning privileges to the user admin using: GRANT ALL PRIVIL...

mysql privileges

After executing the following mysql statement to create database and giving privileges to user can't access mysql create database my_db; GRANT ALL PRIVILEGES ON my_db.* TO 'user'@'%' IDENTIFIED BY '

Creating a user and granting all privileges on mySQL

I'm trying to create a database on mySQL and I keep getting a syntax error when trying to create a user and grant all privileges. create database name -> grant all privileges on name....

Backup all privileges related to specific user of MySQL

I want to backup all privileges related to specific user (for example u_1) from a MySQL database and restore that in another server. As described here, The 'mysql' database contains users/privileges/

Mysql root user has no privileges

I've searched a lot but nothing works for me. I have a fresh installation of a LAMP server, installed via tasksel command. My system runs php 7.4 and MySql 8 For some reason root user (that in my...

How to GRANT ALL PRIVILEGES to user on MYSQL

I am struggling with granting privileges to mysql user. I tried following command: GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'userpassword' WITH GRANT OPTION; But it returns follow...

Grant Privileges to an user (remotely root logged) in MySQL

I have this: mysql> SELECT CURRENT_USER(); +----------------+ | CURRENT_USER() | +----------------+ | root@% | +----------------+ 1 row in set (0.00 sec) mysql>

how to revoke privileges for a MySQL user

There is a MySQL user with database level privileges, as shown in the screen shot below. However, when an attempt is made to revoke privileges for this user, an error 1141 is the result. Why is t...