Hive connector SQL based authorization testing

In order to test SQL based authorization manually it should be either enabled on a real hive metastore, or the embedded file based hive metastore can be used.

For this guide the second approach has been chosen due to it's simplicity.

1. Create temporary directory

# mkdir /tmp/metastore

2. Set following properties for hive connector in hive.properties

connector.name=hive-hadoop2
hive.metastore=file
hive.metastore.catalog.dir=/tmp/metastore
hive.metastore.uri=thrift://doesntmatter:9999
hive.security=sql-standard

3. Start Presto
4. Login to Presto with presto-cli as an admin user, for example as hive

# presto-cli/target/presto-cli-0.169-t-executable.jar --user hive

5. Create and switch to default schema in Hive

presto> SET ROLE admin;
presto> CREATE SCHEMA hive.default;
presto> USE hive.default;

6. Create and grant roles

presto:default> SET ROLE admin;
presto:default> CREATE ROLE role1;
presto:default> CREATE ROLE role2;
presto:default> GRANT role1 TO USER hive;
presto:default> GRANT role2 TO USER hive;

7. Check that roles are actually granted

presto:default> SET ROLE ALL;
presto:default> SHOW CURRENT ROLES;
  Role  
--------
 role1  
 public 
 role2  
(3 rows)

8. Play around with SET ROLE

presto:default> SET ROLE role1;
SET ROLE
presto:default> SHOW CURRENT ROLES;
  Role  
--------
 role1  
 public 
(2 rows)

presto:default> SET ROLE role2;
SET ROLE
presto:default> SHOW CURRENT ROLES;
  Role  
--------
 public 
 role2  
(2 rows)

presto:default> SET ROLE admin;
SET ROLE
presto:default> SHOW CURRENT ROLES;
  Role  
--------
 public 
 admin  
(2 rows)