Hive SQL Standard Authorization Setup

This confluence page https://cwiki.apache.org/confluence/display/Hive/SQL+Standard+Based+Hive+Authorization will walk you through SQL standard authorization in Hive.  The bottom of the page gives advice on setting it up (and it works well on hive 2.3.5 as I just tried it).

As of 6/15/2019, the set up config noted (tested on 2.3.5) is:

Set the following in hive-site.xml:

• hive.server2.enable.doAs to false.
• hive.users.in.admin.role to the list of comma-separated users who need to be added to admin role. Note that a user who belongs to the admin role needs to run the “set role” command before getting the privileges of the admin role, as this role is not in current roles by default.
• Add org.apache.hadoop.hive.ql.security.authorization.MetaStoreAuthzAPIAuthorizerEmbedOnly to hive.security.metastore.authorization.manager. (It takes a comma separated list, so you can add it along with StorageBasedAuthorization parameter, if you want to enable that as well).
  This setting disallows any of the authorization api calls to be invoked in a remote metastore. HiveServer2 can be configured to use embedded metastore, and that will allow it to invoke metastore authorization api. Hive cli and any other remote metastore users would be denied authorization when they try to make authorization api calls. This restricts the authorization api to privileged HiveServer2 process. You should also ensure that the metastore rdbms access is restricted to the metastore server and hiverserver2.
• hive.security.authorization.manager to org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdConfOnlyAuthorizerFactory. This will ensure that any table or views created by hive-cli have default privileges granted for the owner.

Set the following in hiveserver2-site.xml:

• hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory

• hive.security.authorization.enabled=true
• hive.security.authenticator.manager=org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator
• hive.metastore.uris=’ ‘

This indeed works well.  After doing this, I was able to set the admin role for my noted user.  This let me create more roles, apply grants on them, and assign them to other users.  Users correctly were rejected from doing queries when they did not have the appropriate grants.

Note that I did not set up any kind of password authentication yet, I was just providing a user name to the hive JDBC connection with no password.  But the grants were properly respected based on the user name.

create role testrole;
grant role testrole to user testuser;
grant select on testdb.sample_data to role testrole;
show grant on table testdb.sample_data;
show current roles;
show principals testrole;

-- Fails for testuser before testdb.sample_data grant is given.
select * from testdb.sample_data;

No Column Level Grants/Restrictions

Unfortunately, I could not find any valid syntax for applying column level permissions, even though columns are shown in the grant outputs.  I also cannot find anything online to imply that permissions can be column level.  So, I think this feature is not supported.  You have to live with table level permissions.

That probably makes sense given there are 2 vendor products in the space for this (Apache Ranger and Apache Sentry).