Topic
  • 2 replies
  • Latest Post - ‏2013-04-01T22:39:51Z by manuv17
manuv17
manuv17
9 Posts

Pinned topic Grant privilege to REFRESH an MQT without granting CONTROL privilege

‏2013-03-29T19:09:40Z |
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.

manuv17
Updated on 2013-04-01T22:39:51Z at 2013-04-01T22:39:51Z by manuv17
  • nivanov1
    nivanov1
    231 Posts

    Re: Grant privilege to REFRESH an MQT without granting CONTROL privilege

    ‏2013-04-01T14:23:04Z  
    If 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
    manuv17
    9 Posts

    Re: Grant privilege to REFRESH an MQT without granting CONTROL privilege

    ‏2013-04-01T22:39:51Z  
    • nivanov1
    • ‏2013-04-01T14:23:04Z
    If 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.
    Hi 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.

    manuv17