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

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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.