Getting started with SQLStdAuth

[#1]

In some situations, Ranger is not an option but a Hive Authorization scheme is advisable. It is possible to use SQLStdAuth for this. However it comes with a couple caveats.

Hive Wiki

1. Configuring SQLStdAuth ( HDP 2.3 )

- In Ambari, select Authorization SQLStdAuth. This will set all related configuration parameters like hive.enable.authorization and disable doAs.

- Add admin users to admin role. If you have a user you want to be admin add him to hive.users.in.admin.role=hive,hue,myadmin

2. Prepare HDFS

Since all queries now run as the hive user he needs to have read/write rights on all files in HDFS. This includes the load directories for external tables. Ideally change the owner to the warehousing folder to hive and set access right 700.

I also added hive to an ETL group and made all load folders read AND writable to this group.

3. Create roles

In Hive as an admin user

- Become Admin: SET ROLE ADMIN;

- Create roles:

CREATE ROLE BI; ( should have read rights to all tables )

CREATE ROLE ETL; ( should have read/write rights to all tables )

- Add users to roles:

GRANT ETL TO USER ETLUSER;

GRANT BI TO USER BIUSER;

- Make ETL Role owner of database to be able to create tables in the database

ALTER DATABASE DEFAULT SET OWNER ROLE ETL;

- Change table to be readable by BI

GRANT SELECT ON MYTABLE TO BI;

- Change table to be read and writable by ETL

GRANT ALL ON MYTABLE TO ETL;

NOTE: I did not find a way to make a ROLE into the owner of a table, so only the table owner or admin can drop tables but the ETL user could insert, drop partitions etc.

4. Beeline parameters

SQLStdAuth restricts access to hive config parameters to a white list. In older environments Hive scripts would be parametrized with configuration parameters. -hiveconf day=20150201.

This will not work anymore since the parameters are not in the whitelist. You can instead use

beeline --hivevar day=20150201