Db2

 View Only

  • 1.  DMC 3.1.4: Access rights and priviledges needed in database to monitor

    IBM Champion
    Posted Mon April 26, 2021 09:48 AM
    Hi,

    in a more tight customer scenario we try the get DMC 3.1.4 running.

    According to this Minimum database privileges required for Db2 KC page, I started with these permissions, but I still get
    errors in the DMC which return errors ('Can't display data' Click on refresh to continue, which doesn't help)

    GRANT CONNECT, SQLADM, IMPLICIT_SCHEMA ON DATABASE TO USER DMCMON ;
    GRANT CREATETAB ON DATABASE TO USER DMCMON ;
    GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO USER DMCMON ;
    GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO USER DMCMON ;
    GRANT EXECUTE ON PACKAGE NULLID.SYSSH100 TO USER DMCMON ;
    GRANT EXECUTE ON PACKAGE NULLID.SYSSN200 TO USER DMCMON ;
    GRANT EXECUTE ON FUNCTION SYSPROC.* to user DMCMON;
    GRANT EXECUTE ON PROCEDURE SYSIBM.SQLCAMESSAGECCSID to user DMCMON;
    GRANT SELECT ON sysibm.sysdummy1 to USER DMCMON ;
    GRANT SELECT ON sysibm.sysversions to USER DMCMON ;
    GRANT SELECT ON SYSIBM.SYSUSERAUTH TO USER DMCMON ;

    Of course DBADM with DATAACCESS could fix it all, but that is not allowed in this environment.

    What are the minimal privileges and permissions on the database to monitor are needed to get basic monitoring
    or advanced monitoring running?

    Kind regards

    ------------------------------
    Roland Schock
    Distinguished Engineer
    ARS Computer und Consulting GmbH
    ------------------------------

    #Db2


  • 2.  RE: DMC 3.1.4: Access rights and priviledges needed in database to monitor

    Posted Thu May 06, 2021 04:32 PM
    Hi Roland,

    Do you have a case opened for this issue?  It will be good if we can have trace to look at the privilege issues that happened in the monitoring database.  Please let us know the case number.  Thanks.

    ------------------------------
    Jason Sizto
    ------------------------------

    Original Message


  • 3.  RE: DMC 3.1.4: Access rights and priviledges needed in database to monitor

    IBM Champion
    Posted 9 days ago

    Hi Roland, 

    during configuration of monitoring DB2 with Instana we used these settings to monitor the Database:

    b2 "grant execute on FUNCTION SYSPROC.MON_GET_CONTAINER to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_TRANSACTION_LOG to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_DATABASE to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_TABLESPACE to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_PKG_CACHE_STMT to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_AGENT to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_HADR to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_CONNECTION to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_PKG_CACHE_STMT to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_UTILITY to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_WORKLOAD to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_UNIT_OF_WORK to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.MON_GET_INSTANCE to USER mig_ro"

    db2 "grant select on table SYSIBMADM.MON_TRANSACTION_LOG_UTILIZATION to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.PD_GET_DIAG_HIST to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.PD_GET_LOG_MSGS to USER mig_ro"

    db2 "grant select on TABLE SYSCAT.INDEXES to USER mig_ro"

    db2 "grant select on TABLE SYSCAT.TABLES to USER mig_ro"

    db2 "grant select on TABLE SYSIBMADM.MON_CURRENT_SQL to USER mig_ro"

    db2 "grant select on TABLE SYSIBMADM.DB_HISTORY to USER mig_ro"

    db2 "grant select on TABLE SYSIBMADM.DBCFG to USER mig_ro"

    db2 "grant select on TABLE SYSIBMADM.MON_CONNECTION_SUMMARY to USER mig_ro"

    db2 "grant select on TABLE SYSIBMADM.MON_DB_SUMMARY to USER mig_ro"

    db2 "grant select on TABLE SYSIBMADM.MON_LOCKWAITS to USER mig_ro"

    db2 "grant select on TABLE SYSIBMADM.SNAPTAB_REORG to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.DB_GET_CFG to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.DBM_GET_CFG to USER mig_ro"

    db2 "grant execute on FUNCTION SYSPROC.ENV_GET_INST_INFO to USER mig_ro"

    I hope you'll find this information helpful, although it's quite a while since you've asked :-)

    best regards
    Frank



    ------------------------------
    Frank Trila
    ------------------------------

    Original Message


  • 4.  RE: DMC 3.1.4: Access rights and priviledges needed in database to monitor

    IBM Champion
    Posted 9 days ago

    Hi Frank,

    thanks for your version of GRANTs. What I was really hoping for is IBM offcially documenting a list of grants needed to get DMC work without dumping errors in some log file only root on the DMC server has access to.

    At least for the Db2 command "CREATE DATABASE ... RESTRICTIVE" IBM has documented [years after they have introduced the feature] the differences to a normally created database: https://www.ibm.com/docs/en/db2/11.5?topic=ownership-default-privileges-granted-creating-database

    In a world, where people learned the hard way, that default passwords for routers can have a negative impact (Google for opening SPAM could hack your router), the developer of some pretty central software in your database farm should have an interest to keep your attack surface small.

    So I would love IBM to explicitly state in the docs of DMC, we need in the database to monitor these privileges and authorities. And I do not wat to read, just give me SYSADM and it will be OK. Well, it got better in the last years since the original post. See https://www.ibm.com/docs/en/db2-data-mgr-console/3.1.x?topic=console-min-db-privileges-required-db2, but the secrets, why DMC needs SYSMAINT but not only SYSMON is not documented. It would be so neat, if there will be a list, SYSMON is sufficient, but if you want to see this and this view, you need to grant the DMC user SYSMAINT in the database to monitor.

    It is also often unclear, if grants needed in the REPODB or in the database to monitor. 

    Just my €0.03



    ------------------------------
    Roland Schock
    IBM Champion and IBM Gold Consultant
    ------------------------------

    Original Message