When reading all tables, if the driver encounters one that doesn’t have permissions to read, should the driver error out and stop completely, or should it continue onto the next and give a warning?
From the ODBC traces we can see we are trying to list the available objects in the database by calling SQLTables with no filters, which means give me all the of the tables in all of the schemas. From the ODBC Trace –
Sep 19 10:25:25.119 INFO 59488 Connection::ExecuteCatalogFunction: SQLTables("HIVE", NULL, NULL, "TABLE,VIEW,SYNONYM")
As mentioned, the SQLTables command will list all of the tables in the database. The 2nd and 3rd arguments are filters, which limit what is returned. The 2nd is a schema filter and the 3rd a table filter. Since both are null we are asking for all Tables, Views, and Synonyms. Behind the scenes Hive converts the SQLTables ODBC call into a bunch of SHOW TABLES queries to build the whole list of tables. In the trace we have the failure is –
Sep 19 10:25:29.525 DEBUG 59488 HardyTCLIServiceThreadSafeClient::ExecuteStatement: TExecuteStatementReq
sessionHandle.sessionId.guid = 02eb786a3eec4b99857cbc25cf0f1d26
statement = SHOW TABLES IN `cmbase_db`
runAsync = true
This fails because the user running the query does not have permissions to read the underlying HDFS files. Like said previously, for Hive you often need to make sure permissions are OK twice, once at the Hive level and once at the HDFS level (with Knox, or Ranger there might be more levels too).
org.apache.hadoop.hive.ql.metadata.Hive:getDatabase:Hive.java:1308""org.apache.hadoop.hive.ql.metadata.Hive:databaseExists:Hive.java:1293""org.apache.hadoop.hive.ql.parse.DDLSemanticAnalyzer:validateDatabase:DDLSemanticAnalyzer.java:1980""*org.apache.hadoop.hive.metastore.api.MetaException:java.security.AccessControlException: Permission denied: user=TSSAdmin_Stage, access=READ, inode="/tenants/slfcauat/der/cmbase":hdfs:hdfs:drwxrwx---
It seems like if one of these SHOW TABLES calls fails because of a permissions issue, the whole SQLTables ODBC call fails. It seems unreasonable to fail that whole API call if the user does not have read permissions on that table. The driver should instead just not show anything for that schema. Maybe there is a reason they are doing this and have a different solution?
Can anyone confirm if this is expected behavior and if there is a different solution to this problem?