MySQL - CREATE USER

The MySQL CREATE USER statement is used to create a database account that allows the specified user to log into the MySQL database.

Syntax

The syntax of using CREATE USER statement in MySQL is given below:

CREATE USER 
  user_name IDENTIFIED BY 'password_value'
  [PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]];

Parameters

`user_name`	`Required.` Specify the name of the database account that need to be created.
`password_value`	`Required.` Specify the password to assign to user_name.
`PASSWORD EXPIRE`	`Optional.` Specify it for following: PASSWORD EXPIRE - Immediately marks the password expired PASSWORD EXPIRE DEFAULT - Sets the global expiration policy to the account PASSWORD EXPIRE NEVER - Overrides the global policy and disables password expiration for the account PASSWORD EXPIRE INTERVAL N DAY - Overrides the global policy and sets the password lifetime to N days for the account

Example: Create a user

In the example below, the CREATE USER statement is used to create a new user called john in the MySQL database with password 'password1'.

CREATE USER
  'john'@'localhost' IDENTIFIED BY 'password1';

Example: Create more than one user

In the example below, two users are created in the MySQL database. First is john with password 'password1'. Second is marry with password 'password2'.

CREATE USER
  'john'@'localhost' IDENTIFIED BY 'password1',
  'marry'@'localhost' IDENTIFIED BY 'password2';

Example: Using Hash value for password

In the example above, plaintext password is used. The password can be provided using its hash value (see the PASSWORD() function). For example - to create a new user called john with password 'password1', the following statement can be used.

CREATE USER
  'john'@'localhost' IDENTIFIED BY '*668425423DB5193AF921380129F465A6425216D0';

CREATE USER
  'john'@'localhost' IDENTIFIED BY PASSWORD('password1');

Example: Set the password expiry time

The PASSWORD EXPIRE INTERVAL N DAY clause can be used to set the password lifetime to N days for the given account. In the example below, the user's password will be valid for 180 days.

CREATE USER
  'john'@'localhost' IDENTIFIED BY 'password1'
  PASSWORD EXPIRE INTERVAL 180 DAY;