Using for host when creating a MySQL user

Question

My MySQL database needs two users  appuser and support  One of the application developers insists that I create four accounts for these users   appuser     appuser  localhost  support     support  localhost    For the life of me I can t figure out why he thinks we need this   Wouldn t using the wildcard as the host take care of the  localhost    Any ideas    Using MySQL 5 5 here

User · Accepted Answer

localhost is special in MySQL  it means a connection over a UNIX socket  or named pipes on Windows  I believe  as opposed to a TCP IP socket  Using   as the host does not include localhost  hence the need to explicitly specify it

User · Answer

If you want connect to user     from localhost use mysql -h192 168 0 1 -uuser -p

User · Answer

Let s just test   Connect as superuser  and then   SHOW VARIABLES LIKE   version      ------------------------- ------------------------------     Variable name                       Value                                                   ------------------------- ------------------------------     version                                   10 0 23-MariaDB-0 deb8u1-log      and then  USE mysql    Setup  Create a user foo with password bar for testing   CREATE USER foo     IDENTIFIED BY  bar   FLUSH PRIVILEGES    Connect  To connect to the Unix Domain Socket  i e  the I O pipe that is named by the filesystem entry  var run mysqld mysqld sock or some such   run this on the command line  use the --protocol option to make doubly sure   mysql -pbar -ufoo mysql -pbar -ufoo --protocol SOCKET   One expects that the above matches  user comes from localhost  but certainly not  user comes from 127 0 0 1    To connect to the server from  127 0 0 1  instead  run this on the command line  mysql -pbar -ufoo --bind-address 127 0 0 1 --protocol TCP   If you leave out --protocol TCP  the mysql command will still try to use the Unix domain socket  You can also say   mysql -pbar -ufoo --bind-address 127 0 0 1 --host 127 0 0 1   The two connection attempts in one line   export MYSQL PWD bar    mysql -ufoo --protocol SOCKET --execute  SELECT 1     mysql -ufoo --bind-address 127 0 0 1 --host 127 0 0 1 --execute  SELECT 1     the password is set in the environment so that it is passed to the mysql process   Verification In Case Of Doubt  To really check whether the connection goes via a TCP IP socket or a Unix Domain socket   get the PID of the mysql client process by examining the output of ps faux run lsof -n -p lt yourpid gt      You will see something like   mysql  PID  quux 3u IPv4  code  0t0 TCP 127 0 0 1  port - gt 127 0 0 1 mysql  ESTABLISHED    or  mysql  PID  quux 3u unix  code  0t0  code  socket   So   Case 0  Host    10 10 10 10   null test   update user set host  10 10 10 10  where user  foo   flush privileges     Connect using socket  FAILURE  Connect from 127 0 0 1  FAILURE   Case 1  Host        update user set host     where user  foo   flush privileges     Connect using socket  OK  Connect from 127 0 0 1  OK   Case 2  Host    localhost   update user set host  localhost  where user  foo  flush privileges    Behaviour varies and this apparently depends on skip-name-resolve  If set  causes lines with localhost to be ignored according to the log  The following can be seen in the error log    user  entry  root localhost  ignored in --skip-name-resolve mode    This means no connecting through the Unix Domain Socket  But this is empirically not the case  localhost now means ONLY the Unix Domain Socket  and no longer matched 127 0 0 1   skip-name-resolve is off    Connect using socket  OK  Connect from 127 0 0 1  OK   skip-name-resolve is on    Connect using socket  OK  Connect from 127 0 0 1  FAILURE   Case 3  Host    127 0 0 1   update user set host  127 0 0 1  where user  foo  flush privileges     Connect using socket  FAILURE  Connect from 127 0 0 1  OK   Case 4  Host       update user set host    where user  foo  flush privileges     Connect using socket  OK Connect from 127 0 0 1  OK    According to MySQL 5 7  6 2 4 Access Control  Stage 1  Connection Verification  The empty string    also means    any host    but sorts after         Case 5  Host    192 168 0 1   extra test     192 168 0 1  is one of my machine s IP addresses  change appropriately in your case   update user set host  192 168 0 1  where user  foo  flush privileges     Connect using socket  FAILURE Connect from 127 0 0 1  FAILURE   but    Connect using mysql -pbar -ufoo -h192 168 0 1  OK       The latter because this is actually TCP connection coming from 192 168 0 1  as revealed by lsof   TCP 192 168 0 1 37059- gt 192 168 0 1 mysql  ESTABLISHED    Edge Case A  Host    0 0 0 0   update user set host  0 0 0 0  where user  foo  flush privileges     Connect using socket  FAILURE Connect from 127 0 0 1  FAILURE   Edge Case B  Host    255 255 255 255   update user set host  255 255 255 255  where user  foo  flush privileges     Connect using socket  FAILURE Connect from 127 0 0 1  FAILURE   Edge Case C  Host    127 0 0 2    127 0 0 2 is perfectly valid loopback address equivalent to 127 0 0 1 as defined in RFC6890   update user set host  127 0 0 2  where user  foo  flush privileges     Connect using socket  FAILURE Connect from 127 0 0 1  FAILURE   Interestingly    mysql -pbar -ufoo -h127 0 0 2 connects from 127 0 0 1 and is FAILURE mysql -pbar -ufoo -h127 0 0 2 --bind-address 127 0 0 2 is OK   Cleanup  delete from user where user  foo  flush privileges    Addendum  To see what is actually in the mysql user table  which is one of the permission tables  use   SELECT SUBSTR password 1 6  as password  user  host  Super priv AS su  Grant priv as gr  CONCAT Select priv  Lock tables priv  AS selock  CONCAT Insert priv  Update priv  Delete priv  Create priv  Drop priv  AS modif  CONCAT References priv  Index priv  Alter priv  AS ria  CONCAT Create tmp table priv  Create view priv  Show view priv  AS views  CONCAT Create routine priv  Alter routine priv  Execute priv  Event priv  Trigger priv  AS funcs  CONCAT Repl slave priv  Repl client priv  AS replic  CONCAT Shutdown priv  Process priv  File priv  Show db priv  Reload priv  Create user priv  AS admin FROM user ORDER BY user  host    this gives    ---------- ---------- ----------- ---- ---- -------- ------- ----- ------- ------- -------- --------        password   user       host        su   gr   selock   modif   ria   views   funcs   replic   admin         ---------- ---------- ----------- ---- ---- -------- ------- ----- ------- ------- -------- --------         E8D46     foo                    N    N    NN       NNNNN   NNN   NNN     NNNNN   NN       NNNNNN     Similarly for table mysql db   SELECT host db user          Grant priv as gr         CONCAT Select priv  Lock tables priv  AS selock          CONCAT Insert priv  Update priv  Delete priv  Create priv  Drop priv  AS modif          CONCAT References priv  Index priv  Alter priv  AS ria          CONCAT Create tmp table priv  Create view priv  Show view priv  AS views          CONCAT Create routine priv  Alter routine priv  Execute priv  AS funcs         FROM db ORDER BY user  db  host

User · Answer

The percent symbol means  any host  including remote and local connections   The localhost allows only local connections    so to start off  if you don t need remote connections to your database  you can get rid of the appuser     user right away   So  yes  they are overlapping  but        there is a reason for setting both types of accounts  this is explained in the mysql docs  http   dev mysql com doc refman 5 7 en adding-users html   If you have an have an anonymous user on your localhost  which you can spot with   select Host from mysql user where User    and Host  localhost     and if you just create the user appuser      and you not the appuser  localhost    then when the appuser mysql user connects from the local host  the anonymous user account is used  it has precedence over your appuser     user    And the fix for this is  as one can guess  to create the appuser  localhost   which is more specific that the local host anonymous user and will be used if your appuser connects from the localhost

User · Answer

Going to provide a slightly different answer to those provided so far   If you have a row for an anonymous user from localhost in your users table     localhost  then this will be treated as more specific than your user with wildcard d host  user       This is why it is necessary to also provide  user   localhost    You can see this explained in more detail at the bottom of this page

User · Answer

As  nos pointed out in the comments of the currently accepted answer to this question  the accepted answer is incorrect   Yes  there IS a difference between using   and localhost for the user account host when connecting via a socket connect instead of a standard TCP IP connect   A host value of   does not include localhost for sockets and thus must be specified if you want to connect using that method

[mysql] Using % for host when creating a MySQL user

Examples related to mysql