Skip to main content

Getting started with Hive with Kerberos.[FAILED: SemanticException No valid privileges]

Getting started with Hive with Kerberos.

Grant Permissions to user groups to access hive.

Login to the server and create a role. If these roles are not created then we get permission (Privileges) Issues.
Issue as below.
Error: Error while compiling statement: FAILED: SemanticException No valid privileges
 Required privileges for this query: Server=server1->action=*; (state=42000,code=40000)
Here is how to grant permissions to hive group, so that you can access it.
[sas@waepprrkb004 root]$  beeline -u "jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM"

0: jdbc:hive2://hive-server.server.com> create role admin;
1 row affected
0: jdbc:hive2://hive-server.server.com> show roles;
+--------+--+
|  role  |
+--------+--+
| admin  |
+--------+--+

0: jdbc:hive2://hive-server.server.com> GRANT ROLE admin TO GROUP hive;
0: jdbc:hive2://hive-server.server.com> GRANT ALL ON DATABASE default TO ROLE admin;
Here is the complete output after the permissions are granted.
[sas@waepprrkb004 root]$  beeline -u "jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM"
scan complete in 1ms
Connecting to jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM
Connected to: Apache Hive (version 1.1.0-cdh5.4.5)
Driver: Hive JDBC (version 1.1.0-cdh5.4.5)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.4.5 by Apache Hive
0: jdbc:hive2://hive-server.server.com> show databases;
+----------------+--+
| database_name  |
+----------------+--+
| default        |
+----------------+--+
1 row selected (0.134 seconds)
0: jdbc:hive2://hive-server.server.com> show roles;
+--------+--+
|  role  |
+--------+--+
| admin  |
+--------+--+
1 row selected (0.063 seconds)
0: jdbc:hive2://hive-server.server.com> use default;
No rows affected (0.05 seconds)
0: jdbc:hive2://hive-server.server.com> show tables;
+------------+--+
|  tab_name  |
+------------+--+
| sample_07  |
| sample_08  |
+------------+--+
2 rows selected (0.08 seconds)
0: jdbc:hive2://hive-server.server.com>

Adding New Roles and Groups in Hive.

Before we start accessing the data, we need to give users permission.
  1. Create a role.
  2. Assign role some permissions (SELECT [readonly], INSERT [rw], ALL[all]).
  3. Add a group to the newly create role.

Creating a new role.

First we create roles which we later give permissions to.
0: jdbc:hive2://hive-server.server.com> CREATE ROLE admin;
0: jdbc:hive2://hive-server.server.com> CREATE ROLE readonly;

Assign role permissions.

We are assigning permission to a role readonly to a database (default)
0: jdbc:hive2://hive-server.server.com> GRANT SELECT ON DATABASE default TO ROLE readonly;

Adding a new active directory group to role.

Now we assign the role readonly to a group server-user-access-group. Here server-user-access-group is an Active Directory group which is sync with Linux using SSSD. 
0: jdbc:hive2://hive-server.server.com> GRANT ROLE readonly TO GROUP server-user-access-group;

Adding new External tables

Grant permission to HDFS URI to access the AVRO data.
grant all on uri 'hdfs://nameservice1/data/location/hdfs/some_data/ahmed/' to role admin;
Creating external table.
create external table ahmed-data partitioned by (partition_val1 String,partition_val2 String) stored as avro location '/data/location/hdfs/some_data/ahmed/' TBLPROPERTIES ('avro.schema.url'='data/location/hdfs/some_data_schema/v1/ahmed_schema.avsc');
Alter table to partition it. 
alter table ahmed-data add partition (partition_val1="2015", partition_val2="07");

Testing Setup.

Logging as hive user to giver permission to a specific group.
[root@edge-gw-server keytabs]# beeline -u "jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM"
scan complete in 2ms
Connecting to jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM
Connected to: Apache Hive (version 1.1.0-cdh5.4.5)
Driver: Hive JDBC (version 1.1.0-cdh5.4.5)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.4.5 by Apache Hive
0: jdbc:hive2://hive-server.server.com>
0: jdbc:hive2://hive-server.server.com> create role admin;
0: jdbc:hive2://hive-server.server.com> create role readonly;
Checking for roles in hive. 
0: jdbc:hive2://hive-server.server.com> show roles;
+-----------+--+
|   role    |
+-----------+--+
| admin     |
| readonly  |
+-----------+--+
2 rows selected (0.349 seconds)
Grant readonly role to server-user-access-group group. (But still we have not given permission to role readonly we do that in next step)
0: jdbc:hive2://hive-server.server.com> grant role readonly to group server-user-access-group;
No rows affected (0.04 seconds)
Assigning role readonly select permission on the default database. 
0: jdbc:hive2://hive-server.server.com> grant select on database default to role readonly;
No rows affected (0.049 seconds)
0: jdbc:hive2://hive-server.server.com> show role grant group server-user-access-group;
+-----------+---------------+-------------+----------+--+
|   role    | grant_option  | grant_time  | grantor  |
+-----------+---------------+-------------+----------+--+
| readonly  | false         | NULL        | --       |
+-----------+---------------+-------------+----------+--+
1 row selected (0.062 seconds)
0: jdbc:hive2://hive-server.server.com> !quit
Closing: 0: jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM
Now checking for permission for user ahmed-user, since the user is not part of server-user-access-group he will still not be able to access the data.
[root@edge-gw-server keytabs]# su ahmed-user
[ahmed-user@edge-gw-server keytabs]$ cd ~
[ahmed-user@edge-gw-server ~]$ kinit -kt ahmed-user_new.keytab ahmed-user@ABC.DOMAIN.COM
[ahmed-user@edge-gw-server ~]$ beeline -u "jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM"
scan complete in 2ms
Connecting to jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM
Connected to: Apache Hive (version 1.1.0-cdh5.4.5)
Driver: Hive JDBC (version 1.1.0-cdh5.4.5)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.4.5 by Apache Hive
0: jdbc:hive2://hive-server.server.com> show tables;
+-----------+--+
| tab_name  |
+-----------+--+
+-----------+--+
No rows selected (1.382 seconds)
0: jdbc:hive2://hive-server.server.com> !quit
Closing: 0: jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM
[ahmed-user@edge-gw-server ~]$ exit
exit
Now again logging into hive superuser to grant permission to a group which ahmed-user user is part of.
[root@edge-gw-server keytabs]# kinit -kt hive.keytab hive/hive-server.server.com@XYZ.DOMAIN.COM                                             
[root@edge-gw-server keytabs]# beeline -u "jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM"
scan complete in 2ms
Connecting to jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM
Connected to: Apache Hive (version 1.1.0-cdh5.4.5)
Driver: Hive JDBC (version 1.1.0-cdh5.4.5)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.4.5 by Apache Hive
0: jdbc:hive2://hive-server.server.com> grant role readonly to group ahmed-user-access-group;
No rows affected (0.332 seconds)
0: jdbc:hive2://hive-server.server.com> !quit
Closing: 0: jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM
Login as ahmed-user user. Now we can see the tables. 
[root@edge-gw-server keytabs]# su ahmed-user
[ahmed-user@edge-gw-server keytabs]$ cd ~
[ahmed-user@edge-gw-server ~]$ kinit -kt ahmed-user_new.keytab ahmed-user@ABC.DOMAIN.COM                                                                                 
[ahmed-user@edge-gw-server ~]$ beeline -u "jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM"
scan complete in 2ms
Connecting to jdbc:hive2://hive-server.server.com:10000/default;principal=hive/hive-server.server.com@XYZ.DOMAIN.COM
Connected to: Apache Hive (version 1.1.0-cdh5.4.5)
Driver: Hive JDBC (version 1.1.0-cdh5.4.5)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.4.5 by Apache Hive
0: jdbc:hive2://hive-server.server.com> show tables;
+------------+--+
|  tab_name  |
+------------+--+
| ahmed-data |
| sample_07  |
| sample_08  |
+------------+--+
8 rows selected (0.183 seconds)
0: jdbc:hive2://hive-server.server.com>
Test Complete.
​
Labels:

Comments

Post a Comment

Popular posts from this blog

Cloudera Manager - Duplicate entry 'zookeeper' for key 'NAME'.

We had recently built a cluster using cloudera API’s and had all the services running on it with Kerberos enabled. Next we had a requirement to add another kafka cluster to our already exsisting cluster in cloudera manager. Since it is a quick task to get the zookeeper and kafka up and running. We decided to get this done using the cloudera manager instead of the API’s. But we faced the Duplicate entry 'zookeeper' for key 'NAME' issue as described in the bug below. https://issues.cloudera.org/browse/DISTRO-790 I have set up two clusters that share a Cloudera Manger. The first I set up with the API and created the services with capital letter names, e.g., ZOOKEEPER, HDFS, HIVE. Now, I add the second cluster using the Wizard. Add Cluster->Select Hosts->Distribute Parcels->Select base HDFS Cluster install On the next page i get SQL errros telling that the services i want to add already exist. I suspect that the check for existing service names does n
2 comments
Read more

Zabbix History Table Clean Up

Zabbix history table gets really big, and if you are in a situation where you want to clean it up. Then we can do so, using the below steps. Stop zabbix server. Take table backup - just in case. Create a temporary table. Update the temporary table with data required, upto a specific date using epoch . Move old table to a different table name. Move updated (new temporary) table to original table which needs to be cleaned-up. Drop the old table. (Optional) Restart Zabbix Since this is not offical procedure, but it has worked for me so use it at your own risk. Here is another post which will help is reducing the size of history tables - http://zabbixzone.com/zabbix/history-and-trends/ Zabbix Version : Zabbix v2.4 Make sure MySql 5.1 is set with InnoDB as innodb_file_per_table=ON Step 1 Stop the Zabbix server sudo service zabbix-server stop Script. echo "------------------------------------------" echo " 1. Stopping Zabbix Server &quo
1 comment
Read more

Access Filter in SSSD `ldap_access_filter` [SSSD Access denied / Permission denied ]

Access Filter Setup with SSSD ldap_access_filter (string) If using access_provider = ldap , this option is mandatory. It specifies an LDAP search filter criteria that must be met for the user to be granted access on this host. If access_provider = ldap and this option is not set, it will result in all users being denied access. Use access_provider = allow to change this default behaviour. Example: access_provider = ldap ldap_access_filter = memberOf=cn=allowed_user_groups,ou=Groups,dc=example,dc=com Prerequisites yum install sssd Single LDAP Group Under domain/default in /etc/sssd/sssd.conf add: access_provider = ldap ldap_access_filter = memberOf=cn=Group Name,ou=Groups,dc=example,dc=com Multiple LDAP Groups Under domain/default in /etc/sssd/sssd.conf add: access_provider = ldap ldap_access_filter = (|(memberOf=cn=System Adminstrators,ou=Groups,dc=example,dc=com)(memberOf=cn=Database Users,ou=Groups,dc=example,dc=com)) ldap_access_filter accepts standa
Post a Comment
Read more