MySQL Users

When anything tries to connect to MySQL, we call it a client, whereas MySQL is the database server. To manage who, how, and for what purpose clients can connect, MySQL has a sophisticated user privilege system. Its settings are stored in grant tables in the database called mysql.

In Day 2, you saw how to connect to MySQL as the its root user. root can do anything on a MySQL database, but for any kind of database development work this is simply too much power to grant to ordinary users, whether they be real people or software applications.

MySQL's privilege system enables you to create users and assign them privileges. You can let users do things on a global level, or just within a given database, or you can exercise precise control at more granular levels.

Not only can you control what users have access to but how they can access it; for example, the privilege system lets you specify whether a user can just read from a database, or whether he can make changes to it.

Here's how to create a user called tony who can connect from localhost using password mypass. He will have access to the database you created previously in this lesson, called store.

mysql> GRANT ALL ON store.* TO tony@localhost IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.14 sec)

In the preceding line, ALL means “all privileges”: the user is permitted to take any action, such as reading or deleting data, or even modifying tables, but only on tables in the store database. He cannot access any other database.

This user can now connect to the MySQL database and select the store database like this, all in one line:

# mysql -u tony -p store
Enter password:

That's all you need to know about the privilege system for now. However, you will learn about it in more detail in Day 14, “Security.”

Should you want to change a user's password, you can do this most easily from the command line:

# mysqladmin -u tony -p password newpass
Enter password:

This sets the password to newpass, although you will need to enter the existing password (which you previously set to mypass) at the prompt.