CODEWITHSUNDEEP
mysqlsql-grantcreateuser
ydaetskcoR
ydaetskcoR

Reputation: 56877

Creating MySQL users and granting privileges - unable to connect to server

I've just tried to create a bunch of users for a (currently local) database but I'm having issues connecting to the database and not too sure what I'm doing wrong.

The users exist after creation but I can't connect to the database with their credentials while using Workbench for connection testing.

I've created my users and then granted them permission straight after as so:

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL ON DATABASE.* TO 'username'@'%';

If I run SELECT * FROM mysql.user; to see the users, I can see that the users are there.

I've tried flushing privileges but it doesn't seem to make any difference.

When trying to connect to the server via Workbench as one of the users I've created I get re-prompted for my password. It then seems to either hang or tell me the password is wrong.

This is initially local for development purposes but I'll be setting this up on a server once I've got this working. Could this be down to it being ran locally?

Upvotes: 1

Views: 406

Answers (1)

ydaetskcoR
ydaetskcoR

Reputation: 56877

As mentioned in the MySQL documentation for adding users, when connecting locally a user must be created @'localhost' as well as to wildcard locations:

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. (user table sorting is discussed in Section 6.2.4, “Access Control, Stage 1: Connection Verification”.)

With a user only being denoted with a host name of '%' to allow for connection anywhere MySQL will instead use the anonymous <anonymous>''@'localhost' account because it will attempt to match the localhost location first and then not find the related user name for localhost and so use the <anonymous> user instead.

Alternatively, the anonymous user can be deleted and this should also fix the problem rather than having to duplicate users.

Upvotes: 2

Related Questions