Hive - Setting Up Hive Admin User - GCP Dataproc

HIVE ADMIN User setup - GCP etc 👀

We had issues assigning the hive admin user role to our admin users. We googled everything and the only solutions out their would not work. So we created our own that approach that is simple and effective.

Issue: 

When you login to Hive with beeline (only beeline supports the admin commands) and you cant get the admin permissions to work as you want to create roles and assign to users etc.

: Sample command wont work : CREATE ROLE DELIVERBIADMIN_ADMIN --- errors you are not admin  and set role admin --- does not work. 

Lets Begin with the Solution

stop hive server using the following command 

systemctl stop hive-server2

Ammend the following Hive configuration file /etc/hive/conf.dist/hive-site.xml

Insert into hive-site.xml the following tags and values

<property>

    <name>hive.server2.enable.doAs</name>

    <value>false</value>

</property>

 

<property>

    <name>hive.users.in.admin.role</name>

    <value>deliverbiadmin</value>

</property>

 

<property>

    <name>hive.security.authorization.manager</name>

 <value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory</value>

</property>

 

<property>

    <name>hive.security.authorization.enabled</name>

    <value>true</value>

</property>

 

<property>

    <name>hive.security.authenticator.manager</name>

    <value>org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator</value>

</property>

Now the fun part - Undocumented anywhere !!!!!!

The Roles for admin are controlled with the following 2 tables - So insert the following entries for our user deliverbiadmin.

Connect to the MYSQL Metastore Database for Hive - normally called hive or metastore. Connect with any tool you wish on Port 3306 MYSQL

Or from a linux prompt in GCS Dataproc Master Node you can connect using : mysql --host=127.0.0.1 --local-infile=1 --user=hive --password=hive-password

Once connected query the 2 tables below. 

The 2 tables we are interested in are  ROLES & ROLE_MAP

select * from metastore.`ROLES`;

select * from metastore.`ROLE_MAP`;

-- make sure ID for Admin role is 1 -- if not alter the insert statement

INSERT INTO metastore.`ROLE_MAP` VALUES (1,1539877824,1,'admin','ROLE','deliverbiadmin','USER',1)

------Start HIVE

systemctl start hive-server2

Thats it login to Hive and issue the command : set user admin;

use beeline to test

./usr/lib/hive/bin/beeline --color=yes -u 'jdbc:hive2://test-cluster2-m:10000/default' -n deliverbiadmin

Thats for a basic setup we are using LDAP and Kerberos for Security or you can use PAM. upto you.

Over and Sha and Krish