Tag : setup-sql-based-authorization-hive-hortonworks-hdp-2-2

Setup SQL Based authorization in hive

In this tutorial we will see how to setup SQL Based authorization in hive.

 

Step 1 – Goto ambari UI and add/modify below properties

 

Goto service hive → configs and change autherization to SQLStdAuth

 

 

Step 2 – In Hive-site.xml, make sure you have set below properties:

 

hive.server2.enable.doAs --> false
hive.users.in.admin.role --> root (comma separated list of users)

 

Step 3 – Make sure that you have below properties set in Hiveserver2-site.xml

 

hive.security.authorization.manager --> org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdConfOnlyAuthorizerFactory
hive.security.authorization.enabled --> true
hive.security.authenticator.manager --> org.apache.hadoop.hive.ql.security.ProxyUserAuthenticator

 

Step 4 – Restart hive services from ambar UI

 

Step 5 – Testing our setup, in this we will create one readonly user and try to drop table.

 

5.1 Login to beeline using root ( as we have added root in hive.users.in.admin.role )

 

0: jdbc:hive2://localhost:10010> !connect jdbc:hive2://localhost:10010Connecting to jdbc:hive2://localhost:10010
Enter username for jdbc:hive2://localhost:10010: root
Enter password for jdbc:hive2://localhost:10010: **** 
Connected to: Apache Hive (version 1.2.1.2.3.2.0-2950)Driver: Hive JDBC (version 1.2.1.2.3.2.0-2950)Transaction isolation: 
TRANSACTION_REPEATABLE_READ1: jdbc:hive2://localhost:10010>

 

5.2 Now by default there is only one role – public, you need to run below command to set your role as ADMIN.

 

0: jdbc:hive2://localhost:10010> SHOW CURRENT ROLES;
+---------+--+
|  role  |
+---------+--+
| public  |
+---------+--+

 

5.3 Set role as admin for user root

 

 

1: jdbc:hive2://localhost:10010> set role ADMIN;
No rows affected (0.445 seconds)1: 
jdbc:hive2://localhost:10010> show roles;
+---------+--+
|  role  |
+---------+--+
| admin  |
| public  |
+---------+--+
2 rows selected (0.165 seconds)

 

5.4 Create new role readonly and add readonly_user in that group

 

0: jdbc:hive2://slave1.hortonworks.com:10010/> create role readonly;
No rows affected (0.071 seconds)

 

5.5 Verify that new role has been created successfully

 

0: jdbc:hive2://slave1.hortonworks.com:10010/> show roles;
+-----------+--+
|  role  |
+-----------+--+
| admin  |
| public  |
| readonly  |
+-----------+--+
3 rows selected (0.051 seconds)
0: jdbc:hive2://slave1.hortonworks.com:10010/>

 

5.6 Add readonly_user into readonly role

 

5: jdbc:hive2://slave1.hortonworks.com:10010> grant role readonly to user readonly_user;
No rows affected (0.088 seconds)
5: jdbc:hive2://slave1.hortonworks.com:10010>

 

5.7 Grant select privileges to role readonly

 

5: jdbc:hive2://slave1.hortonworks.com:10010> grant select on table batting to role readonly;
No rows affected (0.405 seconds)
5: jdbc:hive2://slave1.hortonworks.com:10010>

 

5.8 Verify grants for role readonly

 

0: jdbc:hive2://slave1.hortonworks.com:10010/> show grant role readonly;
+-----------+----------+------------+---------+-----------------+-----------------+------------+-------
-------+----------------+----------+--+
| database  |  table  | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |  grant_time  | grantor  |
+-----------+----------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
| default  | batting  |  |  | readonly  | ROLE  | SELECT  | false  | 1447877696000  | root  |
+-----------+----------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+--+
1 row selected (0.06 seconds)

 

5.9 Now login to beeline by user readonly_user and try to drop table batting

 

beeline> !connect jdbc:hive2://slave1.hortonworks.com:10010/
Connecting to jdbc:hive2://slave1.hortonworks.com:10010/
Enter username for jdbc:hive2://slave1.hortonworks.com:10010/: readonly_user
Enter password for jdbc:hive2://slave1.hortonworks.com:10010/: ********
Connected to: Apache Hive (version 1.2.1.2.3.2.0-2950)Driver: Hive JDBC (version 1.2.1.2.3.2.0-2950)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://slave1.hortonworks.com:10010/> drop table batting;
Error: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: Principal [name=readonly_user, type=USER] does not have following privileges for operation DROPTABLE [[OBJECT OWNERSHIP] on Object [type=TABLE_OR_VIEW, name=default.batting]] (state=42000,code=40000)

 

Note – we are getting an error here because readonly_user does not have permission to drop table batting!

 

5.10 Let’s try to access some rows from table batting

 

0: jdbc:hive2://slave1.hortonworks.com:10010/> select * from batting limit 5;
+--------------------+---------------+---------------+--+
| batting.player_id  | batting.year  | batting.runs  |
+--------------------+---------------+---------------+--+
| playerID  | NULL  | NULL  |
| aardsda01  | 2004  | 0  |
| aardsda01  | 2006  | 0  |
| aardsda01  | 2007  | 0  |
| aardsda01  | 2008  | 0  |
+--------------------+---------------+---------------+--+
5 rows selected (0.775 seconds)
0: jdbc:hive2://slave1.hortonworks.com:10010/>

 

We can see that grants are working and user can see the contents but cannot delete the table.

 

Please post comments if you need any help! :-)

 

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather