MySQL Phrasebook

A user is a name associated with a set of authentication credentials with an associated set of permissions. During the installation of MySQL, a user (called root) is created who has full rights to all the databases within that instance of MySQL. This user's power is similar to that of the root user on Linux and should only be used when necessary.

At a minimum, each database stored within a single instance of MySQL should probably have its own administrative user account. If the database will be used by multiple users, each class of user should have its own account.

Creating a User

In this section, the mysql command-line client is used to create a user with full rights to the library database. (See Chapter 7 for instructions on loading the command-line client.)

GRANT ALL ON library.* TO librarian@localhost IDENTIFIED BY 'secret';

This command is parsed as follows:

• GRANT ALLThe GRANT keyword is immediately followed by a list of privileges to grant. Alternately, use the ALL keyword to grant all privileges (except the GRANT privilege that allows a user to grant rights to other users) to the user.

• ON library.* The ON clause is used to specify on which database(s) and tables within the database(s) privileges will be granted. In the example, you are granting all privileges (except GRANT) on all tables (noted by the asterisk *) in the library database.

• TO librarian@localhost The TO clause allows you to specify an account name at a given host to which to grant permissions.

• IDENTIFIED BY 'secret' The IDENTIFIED BY clause allows you to set a password for the accountin this case, 'secret'.

To view the new user, run the following command:

SHOW GRANTS FOR librarian@localhost;

For more information on permissions and users, see Chapter 6, "User Management and Security."