The current authorization to issue a REFRESH TABLE on an MQT is the following (this is true up through and including v12):
The privilege set for REFRESH TABLE must include at least one of the following authorities:
•Ownership of the materialized query table
•DBADM or DBCTRL authority on the database that contains the materialized query table
•SYSADM or SYSCTRL authority
If you want to delegate this authorization to application teams, the above entitlements are too great.
Providing a grantable REFRESH TABLE authority would allow for the delegation of this authority without involving a DBA who would typically have this authority, and allow application teams to maintain their own refresh of an MQT.
|Who would benefit from this IDEA?||All persons using MQTs could allow for a granular implementation conforming with the idea of least privilege.|
How should it work?
For SYSIBM.SYSTABLES.TYPE = 'M' allow for REFRESH.
e.g. GRANT REFRESH ON TABLE SCHEMA.TABLENAME TO USER_OR_GROUP;
|Priority Justification||The introduction of the authority will allow adherence to the concept of least privilege.|
|Customer Name||Morgan Stanley|
NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "firstname.lastname@example.org" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions