Database Reference
In-Depth Information
2. The
SELECT
output shows two anonymous accounts. Remove each using a
DROP
USER
statement with the corresponding account name:
mysql>
DROP USER ''@'localhost';
mysql>
DROP USER ''@'%.example.com';
23.11. Modifying “Any Host” and “Many Host” Accounts
Problem
You want to ensure that MySQL accounts cannot be used from an overly broad set of
hosts.
Solution
Find and fix accounts containing
%
or
_
in the host part.
Discussion
The host part of MySQL account names can contain the SQL pattern characters
%
and
_
(see
Recipe 5.8
). These names match client connection attempts from any host that
matches the pattern. For example, the account
'user1'@'%'
permits
user1
to connect
from any host whatsoever, and
'user2'@'%.example.com'
permits
user2
to connect
from any host in the
example.com
domain.
Patterns in the host part of account names provide a convenience that enables a DBA
to create an account that permits connections from multiple hosts. They correspond‐
ingly increase security risks by increasing the number of hosts from which intruders
can attempt to connect. If you consider this a concern, identify the accounts and either
remove them or change the host part to be more specific.
There are several ways to find accounts with
%
or
_
in the host part. Here are two:
WHERE Host LIKE '%\%%' OR Host LIKE '%\_%';
WHERE Host REGEXP '[%_]';
The
LIKE
expression is more complex because we must look for each pattern character
separately and escape it to search for literal instances. The
REGEXP
expression requires
no escaping because those characters are not special in regular expressions, and a char‐
acter class permits both to be found with a single pattern. So let's use that expression:
1. Identify pattern-host accounts in the
mysql.user
table like this:
mysql>
SELECT User, Host FROM mysql.user WHERE Host REGEXP '[%_]';
+-------+---------------+
| User | Host |
+-------+---------------+
