How to create a MySQL database USER and ASSIGN PRIVILEGE

Creating a database user and system user are two different things. In this post I will be telling you the steps required to create a MySQL database user and assigning privilege.

For installing and creating database please follow this link

Create MySQL Database

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.
Commands end with ; or g.
Your MySQL connection id is 1112 Server version: 5.0.77 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> create user deepak@'localhost' identified by 'my_password'; 
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES on test.* to deepak@'%'; 
Query OK, 0 rows affected (0.00 sec)
NOTE: The host definition of '%' or '' (null) refers to any host.
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Now we will verify if the user deepak has got all the privilege assigned to him. Let us try to insert some values in the tables for the database he has been given permission.

# mysql -u deepak -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1111
Server version: 5.0.77 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mycomp             |
+--------------------+
2 rows in set (0.00 sec)
mysql> use mycomp;
Database changed
mysql> INSERT INTO employee VALUES ('3','Anita Chaudhary','22');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from employee;
+----------+-----------------+------+
| SerialNo |          Name   |  Age |
+----------+-----------------+------+
| 1        | Deepak Prasad   |   23 |
| 2        |  Amit Dubey     |   24 |
| 3        | Anita Chaudhary |   22 |
+----------+-----------------+------+
3 rows in set (0.00 sec)