MySQL® Administrator's Bible

Chapter 14. User Management

IN THIS CHAPTER

• Learning about MySQL users

• Managing user accounts

• Resetting the root password

• Debugging user account problems

Managing the users for a MySQL server is one of the most important tasks of a MySQL database administrator. Because of the flexibility of the permissions system, it is not necessarily a trivial task. There are many tips to help manage users.

Learning about MySQL Users

A user in MySQL is a combination of a username and host string. A host string can be an IP address, hostname, fully qualified domain name, or netmask. This means that even though they share a username, admin@192.168.2.10 is different from admin@'192.168.2.%', and both users can have different passwords and permissions. In the following example, we set up two users with the same username and different passwords and permissions:

shell> mysql -u root -prootpass
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 6.0.8-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT USAGE ON *.* TO admin@'192.168.2.10'
  IDENTIFIED BY 'easytoguess';
Query OK, 0 rows affected (0.22 sec)

mysql> GRANT ALL ON sakila.* TO admin@'192.168.2.20'
  IDENTIFIED BY 'anotherpassword';

Query OK, 0 rows affected (0.41 sec) mysql> select user,host,password from mysql.user where user='admin'; +-------+--------------+-------------------------------------------+ | user | host | password | +-------+--------------+-------------------------------------------+ ...