Error when querying a table in an encrypted zone on a cluster with Db2 Big SQL global impersonation enabled

You may encounter a SQL5105N error while querying a table which resides in an encrypted zone if you are using Db2 Big SQL global impersonation.

Symptoms

A query on table data in an encrypted zone returns the following error:
SQL5105N  The statement failed because a Db2 Big SQL component encountered an
error. Component receiving the error: "BIGSQL Native IO". Component returning
the error: "BIGSQL Native IO". Log entry identifier: "[NRL-002-675aa5528]".
SQLSTATE=58040

Causes

The problem occurs because the bigsql account is used to access the /explorezone encryption key despite impersonation being enabled. Essentially:
  • The HDFS directory /explorezone is encrypted by a key.
  • The key is accessible only to those users and groups that are allowed access to the encrypted area.
  • Ranger HDFS gives access to the same users and groups that are allowed access to the encrypted area.
  • Db2 Big SQL fails because access to the key happens as user bigsql.

Resolving the problem

As a workaround, the encryption key needs to be accessible only to the bigsql user, with the ACLs kept in HDFS (Ranger). Do the following steps to resolve the problem:
  1. Add bigsql to the list of users with decrypt / encrypt authorization, and remove any access from public.
  2. Remove access to bigsql from the HDFS Ranger policy for /explorezone.