CREATE USER

This MySQL statement is fairly straightforward: it's used to create a user, but doesn't allow privileges to be granted with it. For that, you would use the GRANT statement.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE USER 'user'[@'host']
[IDENTIFIED BY [PASSWORD] 'password'] [, ...]

Explanation

This statement creates new user accounts on the MySQL server. The user name is given within quotes, followed by the at-sign (@), and a host IP address or host name within quotes. For accessing MySQL locally, use the host of localhost. To access MySQL on the localhost is acceptable, use the percent-sign wildcard as the host (%); in this case, a user with the specified user name can connect from any system. If no host and @ is given, the percent-sign is assumed.

The user password is given in plain text within quotes, preceded by the IDENTIFIED BY clause. You don't need to use the PASSWORD( ) function to encrypt the password. This is done automatically. However, if you wish to provide the hash value of the password, precede the password with IDENTIFIED BY PASSWORD. If the password clause is not given, a blank password is assumed and will be accepted. This is a potential security problem and should never be done. If you do this by mistake, use the SET PASSWORD statement to set the password.

Multiple user accounts may be specified in a comma-separated list.

The CREATE USER statement was introduced in version 5.0.2 of MySQL. For previous versions, use the GRANT statement. This new statement operates similarly to the GRANT statement, except that you cannot specify user privileges with the CREATE USER statement. As a result, the process is to create a user with the CREATE USER statement and then to grant the user privileges with the GRANT statement. This two step process is a more logical process, especially to a newcomer to MySQL. However, you can still use just the GRANT statement to create and set privileges for a new user.

This statement requires CREATE USER privilege or INSERT privilege for the mysql database, which contains user account information and privileges. To remove a user, use the DROP USER statement and possibly also the REVOKE statement.

Examples

As an example of this situation, suppose that at our fictitious college, one of the music teachers, Sonia Oram, has called us saying that she wants a list of students for one of her classes so that she can call them to invite them to a concert. She wants the names and telephone numbers only for her first period Monday morning class.

The way most databases store this data, the course number would be a unique key and would make it easy to retrieve the other data without a subquery. But Sonia doesn't know the course number, so we enter an SQL statement like this:

SELECT CONCAT(name_first, ' ', name_last) AS student,
phone_home, phone_dorm
FROM students
JOIN course_rosters USING (student_id)
WHERE course_id =
  (SELECT course_id 
   FROM course_schedule
   JOIN teachers USING (teacher_id)
   WHERE semester_code = '2007AU'
   AND class_time = 'monday_01'
   AND name_first = 'Sonia'
   AND name_last = 'Oram');

Notice in the subquery that we're joining the course_schedule table with teachers to be able to give the teacher's first and last name in the WHERE clause of the subquery. We're also indicating in the WHERE clause a specific semester (Autumn 2007) and time slot (Monday, first period). The results of these specifics should be one course identification number, because a teacher won't teach more than one class during a particular class period. That single course number will be used by the WHERE clause of the main query to return the list of students on the class roster for the course, along with their telephone numbers.

If by chance more than one value is returned by the subquery in the

Iious example, MySQL will return an error:

Despite our supposition, it is possible that a teacher might teach more than one class at a time: perhaps the teacher is teaching a course in violin and another in viola, but each class had so few students that the department head put them together. In such a situation, the teacher would want the data for both course numbers. To use multiple fields derived from a subquery in a WHERE clause like this, we would have to use something other than the = operator, such as IN. For that kind of situation, see the next section.