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
– Powerlord

2009-09-11 17:50:14 +00:00
Commented Sep 11, 2009 at 17:50 — Powerlord
– Powerlord, Commented Sep 11, 2009 at 17:50

rmoestl · Accepted Answer · 2017-11-15 09:10:09Z

195

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, 2017 at 9:10

rmoestl

3,1855 gold badges27 silver badges40 bronze badges

answered Sep 11, 2009 at 17:47

chaos

125k34 gold badges306 silver badges310 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Brian G Over a year ago

I do have that in fact. is there a better way without impacting my production.

chaos Over a year ago

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.

Brian G Over a year ago

Any more information on this. I am almost positive this is my issue. but I cannot find any more information on it.

chaos Over a year ago

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.

BigBlast Over a year ago

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).

|

Sebas · Accepted Answer · 2016-01-21 00:46:16Z

48

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, 2016 at 0:46

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

answered Jun 21, 2013 at 14:19

user2509314

5014 silver badges4 bronze badges

1 Comment

Phil Over a year ago

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.

cortopy · Accepted Answer · 2015-06-03 21:05:33Z

24

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 3, 2015 at 21:05

cortopy

2,9072 gold badges28 silver badges31 bronze badges

6 Comments

ccjjmartin Over a year ago

I didn't have to remove the special characters just had to use the -p option to prompt me for the password.

jfla Over a year ago

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.

AnkitK Over a year ago

Any/all/some of the following characters were culprit & ? ^ \

Mehdi Nellen Over a year ago

I figured out spaces and the following characters should be avoided " ' $ , [ ] * ? { } ~ # % \ < > | ^ ;

BadHorsie Over a year ago

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.

|

zinking · Accepted Answer · 2013-04-27 10:41:46Z

23

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

answered Apr 27, 2013 at 10:41

zinking

5,7255 gold badges52 silver badges82 bronze badges

2 Comments

blackirishman Over a year ago

This worked for me as well, but can you explain the logic?

logicbloke Over a year ago

@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.

Russell Silva · Accepted Answer · 2012-12-20 22:56:24Z

7

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.

answered Dec 20, 2012 at 22:56

Russell Silva

2,8423 gold badges28 silver badges37 bronze badges

1 Comment

jweyrich Over a year ago

<sarcasm>What a beautiful design decision!</sarcasm>

S.Vignesh · Accepted Answer · 2021-06-30 10:40:57Z

6

Finally this worked for me:

MariaDB [(none)]> drop user ''@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

answered Jun 30, 2021 at 10:40

S.Vignesh

1311 silver badge6 bronze badges

Comments

Asaph · Accepted Answer · 2009-09-11 17:47:57Z

3

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, 2009 at 17:47

Asaph

164k25 gold badges204 silver badges204 bronze badges

Comments

Sirko · Accepted Answer · 2014-10-29 09:46:29Z

3

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.

edited Oct 29, 2014 at 9:46

Sirko

74.3k19 gold badges157 silver badges194 bronze badges

answered Apr 29, 2013 at 2:58

Chenxiao

3831 gold badge3 silver badges15 bronze badges

1 Comment

Himalaya Garg Over a year ago

'username'@'%' means 'username' can login to DB from any client machine. 'username'@'localhost' means 'username' can only login from same machine where DB is installed.

jtate · Accepted Answer · 2019-12-18 21:17:24Z

3

Change to native password using this command:

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

edited Dec 18, 2019 at 21:17

jtate

2,7067 gold badges28 silver badges37 bronze badges

answered Dec 18, 2019 at 20:11

user1717662

513 bronze badges

Comments

Ari · Accepted Answer · 2017-09-16 20:25:39Z

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, 2017 at 20:25

Ari

3592 silver badges6 bronze badges

1 Comment

10us Over a year ago

This fixed it for me with phpmyadmin 4.5.4.1deb2ubuntu2. Cheers! Never had this issue with previous phpmyadmin versions that I know off.

Eugene · Accepted Answer · 2022-12-09 23:16:52Z

1

stupidest thing ... special characters in the root password are okay but NOT for a user?!?!?! Removed any !@#$%^&*()_+~ characters from the user password and it worked.

answered Dec 9, 2022 at 23:16

Eugene

441 bronze badge

Comments

Sebas · Accepted Answer · 2016-01-21 00:46:48Z

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, 2016 at 0:46

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

answered Feb 26, 2014 at 15:18

obotezat

1,13316 silver badges23 bronze badges

Comments

theINtoy · Accepted Answer · 2017-04-20 19:49:09Z

0

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

answered Apr 20, 2017 at 19:49

theINtoy

3,7682 gold badges41 silver badges66 bronze badges

Comments

sreejith · Accepted Answer · 2015-09-04 07:27:50Z

-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 4, 2015 at 7:27

sreejith

291 silver badge6 bronze badges

1 Comment

Sebas Over a year ago

Dropping test database you could have burned out the whole pc, I have a feeling it would also have solved the problem...

Collectives™ on Stack Overflow

Cannot log in with created user in mysql

14 Answers 14

8 Comments

1 Comment

6 Comments

2 Comments

1 Comment

Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

8 Comments

1 Comment

6 Comments

2 Comments

1 Comment

Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related