Managing Users and Privileges - Learning MySQL - page 300

privileges); we discuss the actual privileges later. Following the privilege list is the
required keyword ON , and the databases or tables that the privileges are for. In the
example, we grant the privileges for music.* , which means the music database and all
its tables. If the specified MySQL user account does not exist, it will be created auto-
matically by the GRANT statement.
In the example, we're assigning privileges to 'allmusic'@'localhost' , which means the
user has the name allmusic and can connect to the server only from the localhost , the
machine on which the database server is installed. There's a 16-character limit on user-
names. The at symbol ( @ ) implies that the user is trying to connect to the server from
the specified host; the MySQL user account doesn't need to correspond to any system
user account on that host, and so there is no relation to any email address. The quotes
surrounding the username and the client hostname are optional; you need them only
if either the username or the hostname has special characters, such as hyphens ( - ) or
wildcard characters. For example, you could write:
mysql> GRANT ALL ON music.* TO ali@localhost IDENTIFIED BY ' the_password ';
Query OK, 0 rows affected (0.02 sec)
However, we recommend that you use the quotes all the time to avoid any surprises.
The optional IDENTIFIED BY ' the_password ' component sets the user's password to
the_password . There's no limit on password length, but we recommend using eight or
fewer characters because this avoids problems with system libraries on some platforms.
You'll find many examples using GRANT throughout this chapter.
Let's experiment with our new user. Quit the monitor using the QUIT command. Then
run it again and connect as the user allmusic :
$ mysql --user=allmusic --password= the_password
Note that this time, we've specified the MySQL user allmusic for the user parameter
and passed this user's password to the password parameter.
You should see the mysql> prompt again. You will now be able to use the music database
by typing USE music; and pressing Enter. Try running a simple query:
mysql> SELECT * FROM album;
You should see the albums in the database.
So far, we haven't found the limits of our privileges. Let's try using the university
database:
mysql> USE university;
ERROR 1044 (42000): Access denied for user: 'allmusic'@'localhost' to database
'university'
MySQL complains that our new user doesn't have permission to access the database
university . Indeed, if we ask MySQL what databases are available, you'll see that
MySQL is secretive: