Cannot log in with created user in mysql

Question

Using this command

GRANT ALL PRIVILEGES ON *.* to 'brian'@'%' identified by 'password';

I try to login with:

 mysql -u brian -ppassword

The error is:

ERROR 1045 (28000): Access denied for user 'brian'@'localhost' (using password: YES)

I am doing this as root and I did try to flush privileges.

I tried this with countless users but it does not seem to work. I can create a user with no password and login works. Command line and from phpmyadmin

Also check to see if the user was in mysql.user which it is.

Show grants for brian shows:

| GRANT ALL PRIVILEGES ON *.* TO 'brian'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |

Hmm... what does SHOW GRANTS FOR `brian`@`%`; return? – Powerlord Sep 11 '09 at 17:50 — Powerlord, Sep 11 '09 at 17:50

score 188 · Accepted Answer · edited Nov 15 '17 at 09:10

188

You probably have this perpetual MySQL problem where one of the default users in the user table is '' @ localhost, which winds up denying all localhost users later in the table. What I would do is mysqldump the mysql database and look for this entry in the User table; if found, delete it and flush privileges.

For more details see https://dev.mysql.com/doc/refman/5.5/en/connection-access.html.

It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from h1.example.net by jeffrey is first matched not by the row containing 'jeffrey' as the User column value, but by the row with no user name. As a result, jeffrey is authenticated as an anonymous user, even though he specified a user name when connecting.

edited Nov 15 '17 at 09:10

rmoestl

2,529
2
19
36

answered Sep 11 '09 at 17:47

chaos

115,791
31
292
308

1

I do have that in fact. is there a better way without impacting my production. – Brian G Sep 11 '09 at 17:51
Well, you could move your newly created entries before it, if you don't want to get rid of it, I guess. But as long as it's there, it's going to keep doing this to new users. If you're worried about getting rid of it, you should probably dig around the MySQL docs for why it's there, which I'm sure there's some weird reason for but I can't recall right now. – chaos Sep 11 '09 at 18:01
Any more information on this. I am almost positive this is my issue. but I cannot find any more information on it. – Brian G Sep 14 '09 at 13:23
http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html has something about the 'anonymous accounts' that this empty-username accounts are apparently meant to be. Nothing there seems to be indicating that there's any downside to removing them. – chaos Sep 14 '09 at 14:24
4

If you're using mysql on a Debian system like, the **BEST** way to solve this problem is by executing mysql_secure_installation (root privileges needed). – BigBlast Feb 09 '17 at 21:39
These default tables and users screw a lot of things up. I had a root user without password that I dropped. And only after that did my new user work. Not sure that was an issue but might have helped not sure. – Bruno Jun 30 '17 at 16:01
Thanks, I have seen people asking to create user specific to remote host, but atleast for the dev env. this is the best sol one can give (:cheers) – S Kr Sep 12 '17 at 16:07
Fixed by DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES; – Nordmeyer Aug 24 '20 at 09:58

score 46 · Answer 2 · edited Jan 21 '16 at 00:46

46

This is a problem caused by the anonymous users. Once I install MySQL I always run

shell> mysql_secure_installation

and select to set/change the root password, remove anonymous users, disallow remote root login, remove the test database. This will remove the anonymous user and secure your installation. It should also solve the problem you have.

edited Jan 21 '16 at 00:46

Sebas

19,640
9
48
103

answered Jun 21 '13 at 14:19

user2509314

481
4
4

If feel this is really the correct answer, at least for anything other than complete demo databases. In a way it seems that mysql is being sensible by preventing user logins before doing this (and removing anonymous users), since it is a big red flag that an installation is insecure. – Phil Aug 27 '13 at 19:59

score 22 · Answer 3 · answered Apr 27 '13 at 10:41

22

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

answered Apr 27 '13 at 10:41

zinking

5,018
4
43
76

1

This worked for me as well, but can you explain the logic? – blackirishman Oct 13 '15 at 17:24
2

@blackirishman Privilege changes aren't taking effect until you flush the old permissions and the new ones are instated. I'm sure this would be done automatically on phpmyadmin. This worked for me too! Thank you. – logicbloke Oct 22 '18 at 17:01

score 19 · Answer 4 · answered Jun 03 '15 at 21:05

19

None of the solutions provided here worked. After loads of error and trial I realised that I had special characters in the password. Changing password without special characters solved the issue

answered Jun 03 '15 at 21:05

cortopy

2,411
21
28

1

I didn't have to remove the special characters just had to use the -p option to prompt me for the password. – ccjjmartin Jun 07 '16 at 04:28
1

This was it for me. I think the problem was that when I set the password, the '\' character in it wasn't interpreted literally, but when I tried to log in with the new user, it was. – jfla Aug 22 '17 at 18:37
Any/all/some of the following characters were culprit & ? ^ \ – AnkitK Sep 27 '18 at 11:00
2

I figured out spaces and the following characters should be avoided " ' $ , [ ] * ? { } ~ # % \ < > | ^ ; – Mehdi Nellen Nov 15 '18 at 18:17
Took me ages to realise this. I used an online random password generator to come up with the password which contained one of these special characters. – BadHorsie Feb 16 '20 at 23:00

score 5 · Answer 5 · answered Dec 20 '12 at 22:56

The mysql docs have this to say: (from http://dev.mysql.com/doc/refman/5.1/en/adding-users.html):

Two of the accounts have a user name of monty and a password of some_pass. Both accounts are superuser accounts with full privileges to do anything. The 'monty'@'localhost' account can be used only when connecting from the local host. The 'monty'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

It is necessary to have both accounts for monty to be able to connect from anywhere as monty. Without the localhost account, the anonymous-user account for localhost that is created by mysql_install_db would take precedence when monty connects from the local host. As a result, monty would be treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the 'monty'@'%' account and thus comes earlier in the user table sort order.

With this in mind I would recommend you create a 'brian'@'localhost' user with the same privileges.

What a beautiful design decision! – jweyrich Jun 22 '17 at 20:48 — jweyrich, Jun 22 '17 at 20:48

score 3 · Answer 6 · edited Oct 29 '14 at 09:46

I think 'Russell Silva' is right...

I created an user by

CREATE USER 'username'@'%' PASSWORD='userpassword';

But I cannot login in this account.The console told me that

ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)

So I created an user with the same username except that changing '%' to 'localhost',and I could finally login in as 'username'. It's quite weird for me though.

score 2 · Answer 7 · answered Sep 11 '09 at 17:47

2

You forgot the quotes around brian in your grant statement. Try it like this:

GRANT ALL PRIVILEGES ON *.* to 'brian'@'%' identified by 'password';

answered Sep 11 '09 at 17:47

Asaph

147,774
24
184
187

score 1 · Answer 8 · answered Sep 16 '17 at 20:25

1

In my case it was due to me clicking "SSL: REQUIRE SSL" (in phpmyadmin). When I changed it to "REQUIRE NONE" I could log in.

answered Sep 16 '17 at 20:25

Ari

166
1
4

This fixed it for me with phpmyadmin 4.5.4.1deb2ubuntu2. Cheers! Never had this issue with previous phpmyadmin versions that I know off. – 10us Sep 27 '17 at 09:06

score 1 · Answer 9 · edited Dec 18 '19 at 21:17

1

Change to native password using this command:

ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';

edited Dec 18 '19 at 21:17

jtate

2,155
6
24
31

answered Dec 18 '19 at 20:11

user1717662

21
3

score 0 · Answer 10 · edited Jan 21 '16 at 00:46

0

You can also connect from another host and then the localhost anonymous user is bypassed and you can remove it and flush privileges:

mysql -u brian -ppassword -h 'other_host_than_localhost'

edited Jan 21 '16 at 00:46

Sebas

19,640
9
48
103

answered Feb 26 '14 at 15:18

obotezat

714
8
14

score 0 · Answer 11 · answered Apr 20 '17 at 19:49

I had a similar problem attempting to connect to a Maria DB running on Ubuntu after upgrading to 17.04.

The default was to listen only on localhost, 127.0.0.1.

To make MySQL/Maria listen on all available ports and interfaces I needed to explicitly specify bind-address=0.0.0.0. I added this line to the end of the file /etc/mysql/my.cnf, i.e.

...
[client-server]

# Import all .cnf files from configuration directory

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
bind-address=0.0.0.0

Then...

sudo /etc/init.d/mysql restart

score -1 · Answer 12 · answered Sep 04 '15 at 07:27

-1

Similar problem occurred for me even after verifying i haven't entered a wrong password, i couldn't login. Below two steps solved my problem.

Dropping test database
Deleting anonymous user

answered Sep 04 '15 at 07:27

sreejith

29
1
6

`Dropping test database` you could have burned out the whole pc, I have a feeling it would also have solved the problem... – Sebas Jan 21 '16 at 00:47

Cannot log in with created user in mysql

12 Answers12

Linked