BI 4.2 - Big SQL cannot create external hadoop table with Ranger policies
External Hadoop Tables + RWX permissions (in Ranger) + No DATAACCESS Privilege = Cannot create external tables
It just works if you either provide DATAACCESS to a specific user or set HDFS ACLs to RWX.
For example, assume that user USERX is granted READ, WRITE, and EXECUTE privileges through a Ranger policy on a given location, and USERX tries to create an external table on that location. USERX will get the SQL0551N error message
SQL0551N "" does not have the privilege to perform operation
"" on object "".
SQLSTATE=42501 "" does not have the privilege to perform operation
"" on object "", SQLCODE=-551, SQLSTATE=42501
1. Provide *DATAACCESS* privilege to user - however, got access to everything https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0053934.html
* DATAACCESS is the authority that allows access to data within a specific database.
* For all tables, views, materialized query tables, and nicknames it gives these authorities and privileges:
* LOAD authority on the database
* SELECT privilege (including system catalog tables and views)
* INSERT privilege
* UPDATE privilege
* DELETE privilege
2. Grant RWX privilege on the location and its contents in HDFS
the customer expects that it is working with Apache Ranger - it works for Hadoop Tables (no external), so they expect the same behaviour for External Hadoop Tables too.