My-SQL Hive User creation and grants on it

[#1]

Server 1 :- 192.168.154.111 (centos)
Server 2 :- 192.168.154.113 (centos2) 

1) First install the client on server from where you want to connect 

Configure Repository and install client 

[root@centos2 ~]# yum install mysql-client

2) My Sql software installed on centos2

[root@centos2 ~]# yum install mysql-server

3)Connected to Mysql on Centos2, Following user exist before creating Hive user and Database.
#mysql -u root -p password
mysql> Select host,user from mysql.user;
+-----------------+-------+
| host            | user  |
+-----------------+-------+
| localhost       | root  |
+-----------------+-------+
8 rows in set (0.00 sec)
Creating hive user and database 
mysql> create user 'hive'@'192.168.154.111' identified by 'hive';
Query OK, 0 rows affected (0.05 sec)
mysql> create user 'hive'@'localhost' identified by 'hive';
Query OK, 0 rows affected (0.00 sec)
mysql> create database hive;
Query OK, 1 row affected (0.00 sec)
mysql> grant ALL ON hive.* TO 'hive'@'192.168.154.111';
Query OK, 0 rows affected (0.00 sec)
mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> flush hosts;
Query OK, 0 rows affected (0.00 sec)
mysql> grant ALL ON hive.* TO 'hive'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> Select host,user from mysql.user;
+-----------------+-------+
| host            | user  |
+-----------------+-------+
| %               | oozie |
| 127.0.0.1       | oozie |
| 127.0.0.1       | root  |
| 192.168.154.111 | hive  |
| 192.168.154.111 | oozie |
| 192.168.154.113 | oozie |
| ::1             | root  |
| localhost       | hive  |
| localhost       | oozie |
| localhost       | root  |
+-----------------+-------+
10 rows in set (0.00 sec)

4) Verify you able to connect server MySQL server using the client ( Client Must be installed from which server you connecting), Verify user from ambari-server 192.168.154.111(Client) and 192.168.154.113(server) is MySQL Server IP address
[root@centos ~]# mysql -u hive -h 192.168.154.113 -p
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user();
+----------------------+
| user()               |
+----------------------+
| [email protected] |
+----------------------+
1 row in set (0.01 sec)
mysql> exit;
[root@centos ~]#

5) Above Client not able to connect to MY sql server , we need to grant permission as below.
mysql> grant ALL ON hive.* TO 'hive'@'%' identified by 'hive';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for hive;
+-----------------------------------------------------------------------------------------------| Grants for hive@%                                                                                   -----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hive'@'%' IDENTIFIED BY PASSWORD '*4DF1D66463C18D44E3B001A8FB1BBFBEA13E27FC' |
| GRANT ALL PRIVILEGES ON `hive`.* TO 'hive'@'%'                                                      |+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Once more verification from Ambari-server, is Hive username and password is working fine
[root@centos]#/usr/lib/hive/bin/schematool -initSchema -dbType mysql -userName hive -passWord hive
Metastore connection URL:        jdbc:mysql://centos2.test.com/hive?createDatabaseIfNotExist=true
Metastore Connection Driver :    com.mysql.jdbc.Driver
Metastore connection User:       hive
Starting metastore schema initialization to 0.13.0
Initialization script hive-schema-0.13.0.mysql.sql
Initialization script completed
schemaTool completeted
[root@centos ~]#