I have a web service that connects to a MySQL database from a dll, both of them are on the same machine. If I use the root user and password it will connect no problem but if I use a user that I created it comes back with
Access denied for user '####'@'%' (using password: YES).
These are the commands I used to create the user
CREATE USER '####'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'%';
My understanding from these commands is that the user can connect to the database from any location. Since I kept getting that error message every time I connected I then thought that '%' didn't include 'localhost' so I then created the same user with these commands
CREATE USER '####'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'localhost';
This also didn't work, just the same error message only this time with 'localhost' in place of '%', then I thought that since the root user works I'll copy all the user hosts it has, so I used these commands
CREATE USER '####'@'127.0.0.1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'127.0.0.1';
CREATE USER '####'@'::1' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO '####'@'::1';
I'm connecting to the same database for both users, I'm 100% sure the password is correct for both of them and I'm connecting by localhost. I can connect to the database with no problems through MySQL Workbench and the MySQL Client using the created user. There must be some permission I haven't given to the created user, but I don't know what, could anyone help me solve this?
I issued FLUSH PRIVILEGES; after creating all the users. skip-networking is off and I granted all privileges to the new user and I was able to access the database. I've created an exe program to connect to the same database, with the privileges removed and it worked. I don't understand why the exe works but not the dll. I'm going to try and grant privileges one by one, which might narrow down the problem.
