MySQL Create User

MySQL Create User Syntax:

CREATE USER IF NOT EXISTS user_name [auth_option] [, user [auth_option]] …
[REQUIRE {NONE | tls_option [[AND] tls_option] …}]
[WITH resource_option [resource_option] …]
[password_option | lock_option] …

Examples:

1. Create user without password.

CREATE USER IF NOT EXISTS admin

2. Create user with password.

CREATE USER ‘admin1’@’localhost’
IDENTIFIED BY ‘my_password’ PASSWORD EXPIRE;

To perform any action, user has to change the password once login.

3. Create user with password and password should expires for every 90days.

CREATE USER ‘admin2’@’localhost’
IDENTIFIED BY ‘my_password’
PASSWORD EXPIRE INTERVAL 90 DAY;

4. Create user with password and password never expires

CREATE USER ‘admin3’@’localhost’ PASSWORD EXPIRE NEVER;

MySQL Create User Syntax Options

user_name:
Specify Account Name

auth_option: {
IDENTIFIED BY ‘auth_string’
| IDENTIFIED BY PASSWORD ‘hash_string’
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY ‘auth_string’
| IDENTIFIED WITH auth_plugin AS ‘hash_string’
}

tls_option: {
SSL
| X509
| CIPHER ‘cipher’
| ISSUER ‘issuer’
| SUBJECT ‘subject’
}

resource_option: {
MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}

password_option: {
PASSWORD EXPIRE
| PASSWORD EXPIRE DEFAULT
| PASSWORD EXPIRE NEVER
| PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
ACCOUNT LOCK
| ACCOUNT UNLOCK
}