I was asked to create a mysql user that can connect to the server from any host but localhost.

I tend to believe that "%" also means localhost but according to my teacher, localhost is not part of "%" and that CREATE USER user@"%"; should do the job but my tests proof otherwise.

enter image description here

The only solution that I've found to solve the problem is with CREATE USER user@localhost ACCOUNT LOCK;

Can anybody educate me about this?

1 Answer 1


It is "standard practice" to provide a bogus connection with minimal or no permissions, then let a wildcard let others in from other places.

This might suffice:

CREATE USER user@localhost IDENTIFIED BY "I'm not going to tell you the pwd";

Or maybe

CREATE USER user@localhost;
GRANT usage TO user@localhost;

but no other grant.

Here's the logic: user@localhost is more specific than user@"%", so, when user logs in through localhost, the bogus connection will rule. Coming from some IP address or hostname, will go through the wildcard.

  • So, you're saying that in addition to issuing CREATE USER and GRANT USAGE to user@localhost, you should have GRANT long, list, of, privileges TO user@"%"? Nov 24, 2021 at 7:29
  • @Vérace-getVACCINATEDNOW - No. The Grants for user blah that only needs to access database Blah "should" go like: GRANT ALL PRIVILEGES ON Blah.* TO blah@"%"; For security reasons, only root (or equivalent) should use ON *.*.
    – Rick James
    Nov 24, 2021 at 7:34
  • Ah, you mean privileges per schema/database? Thanks! Nov 24, 2021 at 7:35
  • Privileges can be for all / database / table / column. By database is usually good both for limiting a login and for easily providing all (or nearly all) that is really needed for that login.
    – Rick James
    Nov 24, 2021 at 16:06

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.