Managing & Using MySQL, 2nd Edition

User Management

The security responsibilities of the DBA basically amount to managing MySQL users and their privileges. Just as operating systems offer an administrative or “root” user for managing other users, MySQL offers a special user called root who can create other MySQL user accounts and grant them privileges. When you use administrative commands such as mysql or mysqladmin to manage users, make yourself the MySQL root user through the -u option:

$ mysql -u root

There is no relationship between the Unix system’s root and MySQL’s root. Anyone can issue the preceding command—and for that reason, it’s critical for you to assign a password to the MySQL root user as soon as you install MySQL. The following command assigns the string P?:2002:My? as the root password:

$ mysqladmin -u root password 'P?:2002:My?'

After you execute this command, anyone who attempts to administer MySQL as the root user has to use a -p option and enter the password:

$ mysql -u root -p
Enter password: P?:2002:My?

Later in the chapter, we show you how to create other MySQL users through SQL GRANT commands. Always assign passwords to these users when you issue the GRANT commands.

While the bulk of your time will revolve around managing privileges, you will still need to put some thought into how you manage your users. In general, you will need to support three kinds of users:

• Individuals

• Applications

• Roles

Individual users are people who use MySQL to develop against it and support it. You should never have ...