Create, drop and check the user in MySQL

Check user detail in MySQL

SELECT * FROM mysql.user

Create a user in MySQL

CREATE USER user_name IDENTIFIED BY [ PASSWORD ] 'password_value';

Example:
-- Create one single user
CREATE USER 'sunny'@'localhost' IDENTIFIED BY 'sunny123';

-- Create multiple user
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1',
'user2'@'localhost' IDENTIFIED BY 'password2';

-- Use HASH value password
CREATE USER 'user1'@'localhost' IDENTIFIED BY '*3567ACB56FDD54AF2628CC9054ADCB2DF9DFE24';


Change the password of User

Syntax:
SET PASSWORD [ FOR user_name ] =
{ PASSWORD('plaintext_password1')
| OLD_PASSWORD('plaintext_password2')
| 'encrypted_password'
};

Example:
SET PASSWORD FOR 'smithj'@'localhost' = PASSWORD('autumn');


Rename the user name

Syntax:
RENAME USER user_name TO new_name;
Example:
RENAME USER 'smithj'@'localhost' TO 'jane'@'localhost';


Drop the Username

DROP USER user_name;

Example
DROP USER 'user1'@'localhost';

Display all grant information for a user

SHOW GRANTS [ FOR username ]

SHOW GRANTS FOR 'sunny'@'%';

SHOW GRANTS FOR 'root'@'localhost';

Check privileges type for user

select * from information_schema.user_privileges;