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 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.
Attached my hive-site.xml for more details.
