Learning MySQL

Creating and Using New Users

To create a new user, you need to have permission to do so; the root user has this permission, so connect to the monitor as the root user:

$ mysql --user=root --password=the_mysql_root_password

Now create a new user called allmusic who’ll connect from the same system as the one the MySQL server is running on (localhost). We’ll grant this user all privileges on all tables in the music database (music.*) and assign the password the_password:

mysql> GRANT ALL ON music.* TO 'allmusic'@'localhost' IDENTIFIED BY 'the_password';
Query OK, 0 rows affected (0.02 sec)

This instruction creates the new user and assigns some privileges. Now, let’s discuss what we’ve done in more detail.

The GRANT statement gives privileges to users. Immediately following the keyword GRANT is the list of privileges that are given, which, in the previous case, is ALL (all simple 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 automatically 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 ...