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

Env:

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: Always backup the metadata tables before manual modification as the rollback plan.