Using% for the host when creating a MySQL user

Question

My MySQL database needs two users: appuser and support.
One of the application developers insists on creating four accounts for these users:

appuser@'%' appuser@'localhost' support@'%' support@'localhost' 

In life, I cannot understand why he thinks we need it. Wouldn't use a wildcard as the host takes care of "localhost"?

Any ideas?

(MySQL 5.5 is used here)

Answer 1

localhost special in MySQL, it means connecting through a UNIX socket (or, in my opinion, named pipes in Windows), unlike a TCP / IP socket. Using % as a host does not include localhost , so you must explicitly specify it.

Answer 2

As @nos noted 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 host user account when connecting via a socket connection instead of the standard TCP / IP connection.

The host value % does not include localhost for sockets and therefore should be specified if you want to connect using this method.

Answer 3

If you want to connect to user@'%' from localhost, use mysql -h192.168.0.1 -uuser -p .

Answer 4

I am going to give a slightly different answer to those provided so far.

If you have a line for an anonymous user from localhost in your user table ''@'localhost' then this will be considered more specific than your user with the wildcard host 'user'@'%' . This is why you must also specify 'user'@'localhost' .

You can see this in more detail at the bottom of this page .

Answer 5

Let's just check it out.

Connect as superuser, and then:

 SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------------------+ | Variable_name           | Value                        | +-------------------------+------------------------------+ | version                 | 10.0.23-MariaDB-0+deb8u1-log | 

and then

 USE mysql; 

Tune

Create user foo with password bar for testing:

 CREATE USER foo@'%' IDENTIFIED BY 'bar'; FLUSH PRIVILEGES; 

connect

To connect to a Unix Domain Socket (i.e. /var/run/mysqld/mysqld.sock I / O, which is called the /var/run/mysqld/mysqld.sock file system /var/run/mysqld/mysqld.sock or something similar), run it on the command line (use the --protocol to make doubly sure)

 mysql -pbar -ufoo mysql -pbar -ufoo --protocol=SOCKET 

It can be expected that the above matches “user came from localhost”, but definitely not “user comes from 127.0.0.1”.

To connect to the server with "127.0.0.1", run it on the command line

 mysql -pbar -ufoo --bind-address=127.0.0.1 --protocol=TCP 

If you are --protocol=TCP , the mysql command will still try to use a Unix domain socket. You can also say:

 mysql -pbar -ufoo --bind-address=127.0.0.1 --host=127.0.0.1 

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)

Check in case of doubt

To really check if the connection is through a TCP / IP or Unix Domain socket

1. get the mysql client process PID by examining the output of the ps faux command
2. run lsof -n -p<yourpid> .

You will see something like:

 mysql [PID] quux 3u IPv4 [code] 0t0 TCP 127.0.0.1:[port]->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 via connector: FAILURE
• Connect to 127.0.0.1: FAIL

Case 1: Host = '%'

 update user set host='%' where user='foo'; flush privileges; 

• Connect using a power outlet: OK
• Connection from 127.0.0.1: OK

Case 2: Host = 'localhost'

 update user set host='localhost' where user='foo';flush privileges; 

The behavior varies, and this obviously depends on skip-name-resolve . If set, lines with localhost will be ignored according to the log. You can see the following in the error log: "the user account" root @localhost "is ignored in the --skip -n ame-resolver mode". This means that there is no connection through the Unix Domain Socket. But this is not experienced. localhost now means ONLY the Unix Domain Socket and no longer matches 127.0.0.1.

skip-name-resolve off:

• Connect using a power outlet: OK
• Connection from 127.0.0.1: OK

skip-name-resolve on:

• Connect using a power outlet: OK
• Connect to 127.0.0.1: FAIL

Case 3: Host = '127.0.0.1'

 update user set host='127.0.0.1' where user='foo';flush privileges; 

• Connect via connector: FAILURE
• Connection from 127.0.0.1: OK

Case 4: Host = ''

 update user set host='' where user='foo';flush privileges; 

• Connect using a power outlet: OK
• Connection from 127.0.0.1: OK

(According to MySQL 5.7: 6.2.4 Access Control, stage 1: connection check , empty string '' also means “any host”, but sorts after “%”.)

Case 5: Host = '192.168.0.1' (optional test)

("192.168.0.1" is one of the IP addresses of my machine, change it accordingly in your case)

 update user set host='192.168.0.1' where user='foo';flush privileges; 

• Connect via connector: FAILURE
• Connect to 127.0.0.1: FAIL

but

• Connect using mysql -pbar -ufoo -h192.168.0.1 : OK (!)

The latter, because it is actually a TCP connection from 192.168.0.1 , as lsof shows:

 TCP 192.168.0.1:37059->192.168.0.1:mysql (ESTABLISHED) 

Edge Option A: Host = '0.0.0.0'

 update user set host='0.0.0.0' where user='foo';flush privileges; 

• Connect via connector: FAILURE
• Connect to 127.0.0.1: FAIL

Edge option B: Host = '255.255.255.255'

 update user set host='255.255.255.255' where user='foo';flush privileges; 

• Connect via connector: FAILURE
• Connect to 127.0.0.1: FAIL

Edge Case C: Host = '127.0.0.2'

(127.0.0.2 is a perfectly valid feedback 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 via connector: FAILURE
• Connect to 127.0.0.1: FAIL

Interestingly:

• mysql -pbar -ufoo -h127.0.0.2 connects to 127.0.0.1 and is FAILURE
• mysql -pbar -ufoo -h127.0.0.2 --bind-address=127.0.0.2 is ok

cleaning

 delete from user where user='foo';flush privileges; 

adding

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 the mysql.db table:

 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; 

Answer 6

The percent symbol means: any host, including remote and local connections.

The local host allows only local connections.

(so for starters, if you do not need remote connections to your database, you can immediately get rid of the user appuser @ '%)

So yes, they overlap, but ...

... there is a reason for installing 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 anonymous user on your local host that you can discover with:

 select Host from mysql.user where User='' and Host='localhost'; 

and if you just create a user appuser @ '%' (and you are not appuser @ 'localhost'), then when the user appuser mysql connects to the local host, an anonymous user account is used (it takes precedence over user appuser @ '%').

And the fix for this (as you might guess) is to create appuser @ 'localhost' (which is more specific to the anonymous user of the local host and will be used if your appuser connects to localhost).