How to create a user and set password

How to create a normal user and set a password in MySQL.

Connect to database as root user

G:\Program_Files\MySQL\MySQL Server 5.6\bin>mysql -u root -h localhost -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.11-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
Step 1

Create a user

mysql> create user 'prajeeth_coments'@'localhost' identified by 'Some_Password';
Query OK, 0 rows affected (0.00 sec)

See the user in MYSQL.USER table

mysql> select user, host, password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root             | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root             | ::1       | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| bloguser         | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| mysql_dbadmin    | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| prajeeth_coments | localhost | *F8B714D48E3B442DBE12E4CCCDB94C6D271512C2 |
+------------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)
		

Log in as the new user

    	
G:\Program_Files\MySQL\MySQL Server 5.6\bin>mysql -u prajeeth_coments -h localhost -p
Enter password: *************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.6.11-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+----------------------------+
| user()                     |
+----------------------------+
| prajeeth_coments@localhost |
+----------------------------+
1 row in set (0.00 sec)

mysql> select current_user();
+----------------------------+
| current_user()             |
+----------------------------+
| prajeeth_coments@localhost |
+----------------------------+
1 row in set (0.00 sec)
Step 2
mysql> create user 'prajeeth_coments'@'localhost';
Query OK, 0 rows affected (0.00 sec)

You can see the password column for prajeeth_coments is blank.

mysql> select user, host, password from mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root             | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root             | ::1       | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| bloguser         | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| mysql_dbadmin    | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| prajeeth_coments | localhost |                                           |
+------------------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)
		

Assign password for user prajeeth_coments

mysql> set password for 'prajeeth_coments'@'localhost' = PASSWORD('Some_Password');
Query OK, 0 rows affected (0.02 sec)

The password column for prajeeth_coments now shows the hash of the password.

mysql> select host, user, password from mysql.user;
       
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root             | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1       | root             | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| localhost | bloguser         | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| localhost | mysql_dbadmin    | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| %         | prajeeth_coments | *F8B714D48E3B442DBE12E4CCCDB94C6D271512C2 |
+-----------+------------------+-------------------------------------------+
6 rows in set (0.00 sec)
     

Try logging in with the new password

    	
G:\Program_Files\MySQL\MySQL Server 5.6\bin>mysql -u prajeeth_coments -h localhost -p
Enter password: *************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.6.11-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+----------------------------+
| user()                     |
+----------------------------+
| prajeeth_coments@localhost |
+----------------------------+
1 row in set (0.00 sec)

mysql> select current_user();
+----------------------------+
| current_user()             |
+----------------------------+
| prajeeth_coments@localhost |
+----------------------------+
1 row in set (0.00 sec)
Comments