Hi Jorg,
Thank you for the feedback.
The strategy for monitoring in Db2 is that monitoring interfaces are exposed as SQL functions within the engine and their security administration is likewise managed within the engine (i.e. via GRANT/REVOKE). All new monitoring interfaces since version 9.7 (e.g. MON_ table functions) adhere to this strategy and have no dependency on the system groups.
There are however a set of legacy monitor interfaces that still exist (those prefixed with SNAP) which are built on top of the older snapshot infrastructure. These interfaces will continue to require membership in one of the system groups due to the snapshot dependency. There are no enhancements being done in the snapshot area.
When writing sql to monitor your database, you should avoid using the legacy snapshot interfaces when possible. There are a few known gaps in this area (e.g. SNAP_GET_TAB_REORG which you mention above) where there is currently no MON* equivalent. In such cases if that information is required you are correct that you still remain in a mixed mode authorization wise.
While the development team is aware of this gap, I would encourage you to consider opening an AHA idea (https://ibm-data-and-ai.ideas.ibm.com/) describing the problem above and indicating specifically which legacy SNAP* interfaces are preventing you from moving off snapshot entirely. This allows other users to up-vote the idea and will help the offering management team when prioritizing features/changes for upcoming releases of Db2.
Regards,
Scott
------------------------------
Scott Walkty
------------------------------
Original Message:
Sent: Tue February 07, 2023 04:59 AM
From: Jörg Burdorf
Subject: Db2 Security Problem using DMC and monitoring views or table functions
Hi all!
To use all monitor functions from the DMC the user that connected to the database that will be monitored, must have a lot of grants to funktions , views and tables. That is totaly managed in the DB2 System.
But additionaly some functions such as SYSPROC.SNAP_GET_TAB_REORG need to be member in one of the system groups (SYSMON.SYSCTRL SYSMAINT SYSADM).
Here starts the problem:
the membership of these groups are managed by operatingsystem administrators, so the DBA cannot define by himself the access privileges to monitor the database.
This problem is not only when you use DMC, also if you use self written sql to monitor your database!
Now the question:
is there a way out of this 2 path security administration trap in the future?
regards
Joerg
------------------------------
Jörg Burdorf
------------------------------