Managing Users and Privileges - Learning MySQL - page 315

The wildcard approach typically removes the need to create multiple users, as a given
user can connect from more than one machine. However, consider the case where the
user steph wants to connect from the client hosts localhost , steph.lloydhouse.com , and
steph.hughwilliams.com . Using the techniques we've discussed so far, you have two
choices: have three users, one for each host; or allow access from any host using
'steph'@'%' . Neither solution is ideal. Fortunately, MySQL supports yet another way
to create one user for different hosts, but this requires knowledge of the structure of
the database tables used to manage the privilege system. We present this later in the
chapter in “Managing Privileges with SQL.”
Anonymous Users
We've previously seen how we can use wildcard specifications for hosts, but wildcard
characters aren't allowed in usernames; you can't, for example, specify
'fred%'@'localhost' . However, you can have a user with an empty username that al-
lows anonymous connections and matches all usernames.
You can create an anonymous local user who can read data from the music database as
follows:
mysql> GRANT SELECT ON music.* TO ''@'localhost';
Query OK, 0 rows affected (0.00 sec)
Note that the username is specified as two single quotes, with nothing between them.
This user allows you to connect without a username or password from the localhost :
$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55 to server version: 5.0.22-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
You can use the CURRENT_USER( ) function to check which user you're logged in as:
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+
1 row in set (0.00 sec)
Here, there is nothing before the @ symbol, indicating that you're logged in as the
anonymous user ''@localhost . The MySQL server decides which user to log you in as
based on a checklist we describe in the next section.