Creating Users

CREATE USER user@host IDENTIFIED BY 'password'; 

To create a new user, you must have sufficient administrator rights. Specifically, you must have either the CREATE USER privilege or INSERT privilege on the mysql database. These permissions are covered in the "Tailoring User Permissions" phrase later in this chapter.

The following example creates a new user named zak who can only connect to MySQL from the IP address 123.456.78.90 and the password given. A login attempt using username zak from any other host will be rejected, regardless of whether the password supplied is correct.

CREATE USER 'zak'@'123.456.78.90' IDENTIFIED BY 'phrasebook'; 

The host specifier can be an IP address, a local hostname, or a fully qualified domain name. To create a user that can connect to a MySQL database running on the local machine, use localhost:

CREATE USER 'zak'@'localhost' IDENTIFIED BY 'localuser'; 

Note

For web scripts using a MySQL database that runs on the same machine as the web server, you always open a connection to localhost in the script.

Tip

Typically you allow database access from a web script using a single MySQL username and password, even if your application performs additional user authentication.

The previous two examples can both be executed on the same MySQL database, and the result is that two separate users are created. Although both are named zak, one can only log in from the remote host and only using the password phrasebook, whereas the second can log in only from the local machine, with the password localuser.

The CREATE USER command was added in MySQL version 5.0.2. In earlier versions, users could be created automatically when assigning permissions using the GRANT command (discussed in the "Tailoring User Permissions" phrase later in this chapter) or by manually inserting records into the mysql database.

The mysql database contains three tablesuser, host, and dbwhich contain the database permissions.

The user table contains the usernames and passwords of everyone who has access to any part of the MySQL database. The values of Host and User together specify the remote user, and Password contains an encrypted password string.

As an administrator, you can insert records directly into these tables, but must use the PASSWORD() function to generate the correctly encrypted password string. The following example is equivalent to the previous CREATE USER example:

INSERT INTO user (Host, User, Password) VALUES ('123.456.78.90', 'zak',          PASSWORD('phrasebook')); FLUSH PRIVILEGES; 

The FLUSH PRIVILEGES command is required to tell MySQL to reload the privilege data after you make a change. This is not required with the CREATE USER command.