Tag Archives: check user detail

Create, drop and check the user in MySQL

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';

-- 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

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

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

Rename the user name

RENAME USER user_name TO new_name;
RENAME USER 'smithj'@'localhost' TO 'jane'@'localhost';

Drop the Username

DROP USER user_name;

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;