2

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.

  • Haha I've figured it out! Phew! I'll write it in the answer. Thanks for your help and time @dwjv. – Sconz2 Jan 25 '16 at 11:00
1

I've eventually figured it out, after gaining a few grey hairs.

I had to give the new user the FILE privilege because my dll was using LOAD DATA INFILE which requires it to work. Here is the info on the FILE privilege.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.