Recently, I had a request from client to have read only access for all production users. While trying to revoke all access other than SELECT, unfortunately explaining query through a role/privilege also got revoked.
To have a permanant solution, I created a role which will have only read only access including catalog tables only. Later gave Explain authority to the role and insert/select privilege to explain tables in a specific schema.
db2 "create role bdwread"
Please add granting privileges for whatever tables and users the required role.
db2 "grant explain on database to role bdwread"
Prepared a dynamic script to give select/insert for required role on explain tables:
db2 "select 'grant select,insert on table ' || tabschema || '.' || tabname || ' to role bdwread;' from syscat.tables where tabschema='BCULINUX' and tabname like 'EXPLAIN%' OR tabname like 'ADVISE%' WITH UR"
Also, gave access for one of in built function required :
db2 "grant execute of BCULINUX.EXPLAIN_GET_MSGS to role bdwread"
I assumed that all will work fine and asked client users to try with explain.
I got a surprise that db2expln utility failed with bind error and failed. Struggled to find with all authority/privileges why the utility is failing. Finally found that few db2*.bnd files are to be bound and given access to public as below.
db2 bind db2expln.bnd blocking all grant public
db2 bind db2exsrv.bnd blocking all grant public
db2 bind db2exdyn.bnd blocking all grant public
Binding helped to fix the issue. Just a learning for the day in routine DBA life.