Query fails with error "HiveAuthzPluginException Unsupported privilege type All" after enabling SQL standard authorization

Document created by Hao Zhu Employee on Feb 7, 2016
Version 1Show Document
  • View in full screen mode

Author: Hao Zhu

 

Original Publication Date:July 9, 2015

 

Environment :

Hive 0.13

Symptom

After enabling Hive SQL standard authorization for HiveServer2, query fails with error "HiveAuthzPluginException Unsupported privilege type All".

 

Sample stacktrace in hive log is:

 

FAILED: HiveAuthzPluginException Unsupported privilege type All
NoViableAltException(26@[])

  at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1002)

  at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:199)

  at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)

  at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:406)

  at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:324)

  at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:980)

  at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:973)

  at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:99)

  at org.apache.hive.service.cli.operation.SQLOperation.run(SQLOperation.java:172)

  at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:244)

  at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:228)

  at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:239)

  at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:358)

  at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1373)

  at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1358)

  at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)

  at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)

  at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:55)

  at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:206)

  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

  at java.lang.Thread.run(Thread.java:745)

FAILED: ParseException line 1:0 cannot recognize input near 'quit' '<EOF>' '<EOF>'

Root Cause

Before enabling SQL standard authorization, users may use legacy default authorization. It causes the metadata tables have "ALL" as the object privilege. For example, the backend MySQL metadata has below entries:

 

mysql> select * from DB_PRIVS;

+-------------+-------------+-------+--------------+---------+--------------+----------------+----------------+---------+

| DB_GRANT_ID | CREATE_TIME | DB_ID | GRANT_OPTION | GRANTOR | GRANTOR_TYPE | PRINCIPAL_NAME | PRINCIPAL_TYPE | DB_PRIV |

+-------------+-------------+-------+--------------+---------+--------------+----------------+----------------+---------+

| 1 | 1436221223 | 96 | 0 | xxxxxx | USER | xxxxxx | USER | All  |

| 6 | 1436394243 | 96 | 0 | xxxxxxx | USER | xxx | ROLE | All  |

+-------------+-------------+-------+--------------+---------+--------------+----------------+----------------+---------+

2 rows in set (0.00 sec)

 

mysql> select * from TBL_PRIVS ;

+--------------+-------------+--------------+---------+--------------+----------------+----------------+----------+--------+

| TBL_GRANT_ID | CREATE_TIME | GRANT_OPTION | GRANTOR | GRANTOR_TYPE | PRINCIPAL_NAME | PRINCIPAL_TYPE | TBL_PRIV | TBL_ID |

+--------------+-------------+--------------+---------+--------------+----------------+----------------+----------+--------+

| 6 | 1436226692 | 0 | xxxxxx | USER | xxxxxx | USER | All  | 466 |

| 8 | 1436228448 | 0 | xxxxxx | USER | xxxxxx | USER | All  | 396 |

| 9 | 1436228718 | 0 | xxxxxxx | USER | xxxxxxx | USER | All  | 466 |

| 10 | 1436306667 | 0 | xxxxxxx | USER | xxxxxx | ROLE | All  | 463 |

| 11 | 1436307904 | 0 | xxxxxxx | USER | xxxxxxx | ROLE | All  | 463 |

+--------------+-------------+--------------+---------+--------------+----------------+----------------+----------+--------+

5 rows in set (0.00 sec)

 

However in SQL standard authorization, ALL privilege will get translated into 4 privileges -- SELECT,INSERT,UPDATE and DELETE. So metadata tables should never store "ALL".

Solution

1. Check below 5 metadata tables to see if there is any "All" privilege.

DB_PRIVS

PART_COL_PRIVS

PART_PRIVS

TBL_COL_PRIVS

TBL_PRIVS

 

2. Backup the problematic tables.

For example:

create table backup_for_TBL_PRIVS as select * from TBL_PRIVS ; 
create table backup_for_DB_PRIVS as select * from DB_PRIVS ;

3. If existing privileges can be cleaned, then delete all rows from the problematic tables:

delete from TBL_PRIVS; delete from DB_PRIVS;

Or if users want to keep the the existing privileges, then just delete the rows with "All" privilege:

delete from TBL_PRIVS where TBL_PRIV like 'All%'; 
delete from DB_PRIVS where DB_PRIV like 'All%';

4. Test the failed query again.

 

Note: Please always backup the metadata tables before manual modification as the rollback plan.

Attachments

    Outcomes