MySQL : Grant all on DB
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 9
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>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.01 sec)
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
mysql> use prajeeth_com;
ERROR 1044 (42000): Access denied for user 'prajeeth_coments'@'localhost' to database 'prajeeth_com'
mysql>
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 10
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> 
mysql> select user from mysql.user;
+------------------+
| user             |
+------------------+
| root             |
| root             |
| bloguser         |
| mysql_dbadmin    |
| prajeeth_coments |
| root             |
+------------------+
6 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blogcontent        |
| mysql              |
| performance_schema |
| prajeeth_com       |
| sakila             |
| test               |
| world              |
+--------------------+
8 rows in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use prajeeth_com
Database changed

mysql> select database();
+--------------+
| database()   |
+--------------+
| prajeeth_com |
+--------------+
1 row in set (0.00 sec)
mysql> select Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv from mysql.db;
+-----------+---------+----------+-------------+-------------+-------------+-------------+-------------+-----------+
| Host      | Db      | User     | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
+-----------+---------+----------+-------------+-------------+-------------+-------------+-------------+-----------+
| %         | test    |          | Y           | Y           | Y           | Y           | Y           | Y         |
| %         | test\_% |          | Y           | Y           | Y           | Y           | Y           | Y         |
| localhost | test    | bloguser | Y           | Y           | Y           | Y           | Y           | Y         |
+-----------+---------+----------+-------------+-------------+-------------+-------------+-------------+-----------+
3 rows in set (0.00 sec)
mysql> grant all on prajeeth_com.* to 'prajeeth_coments'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv from mysql.db;
+-----------+--------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+
| Host      | Db           | User             | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
+-----------+--------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+
| %         | test         |                  | Y           | Y           | Y           | Y           | Y           | Y         |
| %         | test\_%      |                  | Y           | Y           | Y           | Y           | Y           | Y         |
| localhost | test         | bloguser         | Y           | Y           | Y           | Y           | Y           | Y         |
| localhost | prajeeth_com | prajeeth_coments | Y           | Y           | Y           | Y           | Y           | Y         |
+-----------+--------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+
4 rows in set (0.00 sec)
mysql> select user();
+----------------------------+
| user()                     |
+----------------------------+
| prajeeth_coments@localhost |
+----------------------------+
1 row in set (0.00 sec)
mysql> use prajeeth_com;
Database changed

mysql> create table tbl_comments (
    -> comment_id       INT (10),
    -> orig_comment_id   INT (10),
    -> article_id        VARCHAR (50),
    -> comment_date      TIMESTAMP,
    -> user_name         VARCHAR (100),
    -> user_email        VARCHAR (100),
    -> comment_type      CHAR (1),
    -> comment_text      TEXT
    -> );
Query OK, 0 rows affected (0.25 sec)
mysql> alter table tbl_comments add primary key (comment_id);
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tbl_comments;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| comment_id      | int(10)      | NO   | PRI | 0                 |                             |
| orig_comment_id | int(10)      | YES  |     | NULL              |                             |
| article_id      | varchar(50)  | YES  |     | NULL              |                             |
| comment_date    | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_name       | varchar(100) | YES  |     | NULL              |                             |
| user_email      | varchar(100) | YES  |     | NULL              |                             |
| comment_type    | char(1)      | YES  |     | NULL              |                             |
| comment_text    | text         | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.01 sec)
mysql> grant all on prajeeth_com.* to 'prajeeth_coments'@'localhost';
Query OK, 0 rows affected (0.02 sec)

mysql> show grants for 'prajeeth_coments'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for prajeeth_coments@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'prajeeth_coments'@'localhost' IDENTIFIED BY PASSWORD '*F8B714D48E3B442DBE12E4CCCDB94C6D271512C2' |
| GRANT ALL PRIVILEGES ON `prajeeth_com`.* TO 'prajeeth_coments'@'localhost'                                              |
+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
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 11
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| prajeeth_com       |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> select current_user();
+----------------------------+
| current_user()             |
+----------------------------+
| prajeeth_coments@localhost |
+----------------------------+
1 row in set (0.00 sec)
mysql> select user, select_priv, insert_priv, update_priv, delete_priv, create_priv from mysql.user;
+------------------+-------------+-------------+-------------+-------------+-------------+
| user             | select_priv | insert_priv | update_priv | delete_priv | create_priv |
+------------------+-------------+-------------+-------------+-------------+-------------+
| root             | Y           | Y           | Y           | Y           | Y           |
| root             | Y           | Y           | Y           | Y           | Y           |
| root             | Y           | Y           | Y           | Y           | Y           |
| bloguser         | N           | N           | N           | N           | N           |
| mysql_dbadmin    | Y           | Y           | Y           | Y           | Y           |
| prajeeth_coments | N           | N           | N           | N           | N           |
+------------------+-------------+-------------+-------------+-------------+-------------+
6 rows in set (0.00 sec)

As user PRAJEETH_COMENTS


mysql> select database();
+--------------+
| database()   |
+--------------+
| prajeeth_com |
+--------------+
1 row in set (0.00 sec)

mysql> select host, db, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv from mysql.db;
+-----------+--------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+
| host      | db           | user             | select_priv | insert_priv | update_priv | delete_priv | create_priv | drop_priv |
+-----------+--------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+
| %         | test         |                  | Y           | Y           | Y           | Y           | Y           | Y         |
| %         | test\_%      |                  | Y           | Y           | Y           | Y           | Y           | Y         |
| localhost | test         | bloguser         | Y           | Y           | Y           | Y           | Y           | Y         |
| localhost | prajeeth_com | prajeeth_coments | Y           | Y           | Y           | Y           | Y           | Y         |
+-----------+--------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+
4 rows in set (0.00 sec)
Comments