Using % for host when creating a MySQL user
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.
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.
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<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 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->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 from127.0.0.1
and is FAILUREmysql -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;