As part of new security standards in our organization, we have been asked to revoke CONTROL privileges on tables from application user ids (only instance owner should have CONTROL privilege).
We are facing an issue with one table, which is an MQT. Application team wants to refresh it as part of their process. Is there any workaround/script to grant them the ability to REFRESH the MQT without having to grant them CONTROL privilege.
One workaround we are thinking of is to write a shell script that refreshes the MQT as instance owner, but there is a risk of the instance password getting exposed.
OS - AIX 6
DB2 - V9.5 FP9 ESE
Please let me know your thoughts.
This topic has been locked.
2 replies Latest Post - 2013-04-01T22:39:51Z by manuv17
Pinned topic Grant privilege to REFRESH an MQT without granting CONTROL privilege
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-04-01T22:39:51Z at 2013-04-01T22:39:51Z by manuv17
nivanov1 100000H9H7231 PostsACCEPTED ANSWER
Re: Grant privilege to REFRESH an MQT without granting CONTROL privilege2013-04-01T14:23:04Z in response to manuv17If you upgrade to DB2 9.7 you can grant the DATAACCESS authority to the application ID, which allows you to refresh MQTs and does not include the CONTROL authority.
manuv17 060001CQ469 PostsACCEPTED ANSWER
Re: Grant privilege to REFRESH an MQT without granting CONTROL privilege2013-04-01T22:39:51Z in response to nivanov1Hi Nivanov1
TY for the reply. The DATAACCESS authority in V97 does seem to solve the problem of CONTROL privilege. However, this would mean that the user id would have SELECT, INSERT, UPDATE and DELETE on all the tables in the database as well. We do not want that to happen as multiple schemas exist within the database and we do not want this user-id to access tables other than its own.
We might have to GRANT the privilege to resolve this issue; since there is no other alternative.