Minimum database privileges required for Db2

This topic describes the privileges that are required for the operation credentials and data collection credentials for Db2 Data Management Console on Db2. The data collection credential consists of the ID and password provided to Data Management Console to collect monitoring data from a connected database. The ID used for this credential would typically be a functional ID or other non-instance-owner user created for Data Management Console to use. The operation credential consists of the ID and password provided to Data Management Console to perform typical administration tasks, query connected databases, and tuning functions.

Basic Monitoring

For the simplest case, where some privileges are already granted to PUBLIC or to the group the collection user belongs to, this set of grants cover the requirements.

In the following examples, the user of the data collection credential ID, named DMCUSR1, belongs to the OS group GSICMNT. When you configure Data Management Console to manage your database configuration, you need to modify the commands to match your specific database and business needs.

The data collection credential ID must meet the following requirements:

They must be SQLADM on the database. This privilege provides access to most of the required functions.
```
GRANT SQLADM ON DATABASE TO USER DMCUSR1 ;
```
They must be WLMADM on the database. This privilege is required to create console workload(s).
```
GRANT WLMADM ON DATABASE TO USER DMCUSR1 ;
```
The must belong to the instance authority SYSMAINT_GROUP. A few monitoring calls use admin views that use the snapshot monitor to capture information about the database and any connected applications. As a result, additional privileges are required.
```
SYSMAINT group name (SYSMAINT_GROUP) = GSICMNT
```

Event Monitors

To edit event monitors, users require more privileges. Event monitors can be enabled in the monitoring profile for the database by enabling Repository Persistence and making the appropriate configurations. Users must note that event monitoring is only available in the Enterprise edition of Db2 Data Management Console.

The following two options are for granting privileges. Either can be used:

Event monitor option 1 - the Db2 Data Management Console data collection credential ID must have IMPLICIT_SCHEMA and CREATETAB privilege:
```
GRANT IMPLICIT_SCHEMA    ON DATABASE  TO USER  DMCUSR1 ;
```
```
GRANT CREATETAB          ON DATABASE  TO USER  DMCUSR1 ;
```
Event monitor option 2 - If administrators do not want to grant IMPLICIT_SCHEMA, they can manually pre-create the schema that Db2 Data Management Console uses, so that users inherit CREATETAB for that schema only:
```
CREATE SCHEMA IBM_RTMON AUTHORIZATION  DMCUSR1;
```

Furthermore, options 1 and 2 also require that the user of the data collection credential ID have use of the table space where the event monitor tables will live, as the activity event monitor requires a dedicated table space in order to function.

Users must create a separate 32 K table space, just for the event monitor tables, by using the following statement.

GRANT USE OF TABLESPACE   TS4MONITOR   TO USER   DMCUSR1 ;

If other privileges are revoked from PUBLIC, or if the database is created as RESTRICTIVE, then the user needs some additional grants. The user of the data collection credential ID must ensure that these privileges are added to the default workload and the following internal packages:

GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO USER DMCUSR1 ;

GRANT EXECUTE ON PACKAGE  NULLID.SYSSH200 TO  USER   DMCUSR1 ;

GRANT EXECUTE ON PACKAGE  NULLID.SYSSH100 TO  USER   DMCUSR1 ;

GRANT EXECUTE ON PACKAGE  NULLID.SYSSN200 TO  USER   DMCUSR1 ;

The previous set of grants provide the user of the data collection credential ID with the necessary privileges to successfully collect monitoring data.

The following set of statements is a compact version of the previous grant statements that are described:
– quickgrants.sql

GRANT SQLADM ON DATABASE TO USER DMCUSR1 ;
GRANT WLMADM ON DATABASE TO USER DMCUSR1 ;

The user must add the DBM_CFG parameter for SYSMAINT_GROUP.

UPDATE DBM_CFG USING SYSMAINT_GROUP  GSICMNT ;

The following example uses event monitor option 1:

GRANT IMPLICIT_SCHEMA     ON DATABASE   TO USER   DMCUSR1 ;

GRANT CREATETAB           ON DATABASE   TO USER   DMCUSR1 ;

The following example uses event monitor option 2:

CREATE SCHEMA IBM_RTMON AUTHORIZATION DMCUSR1 ;

For either of the event monitoring options, the user needs to create a separate table space and make it available to the collection user. The following statement is used to create a table space:

GRANT USE OF TABLESPACE   TS4MONITOR  TO USER   DMCUSR1 ;

The following statements are used, in the event these previous grants are not granted to public:

GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD TO USER DMCUSR1 ;

GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO USER DMCUSR1 ;

GRANT EXECUTE ON PACKAGE NULLID.SYSSH100 TO USER DMCUSR1 ;

GRANT EXECUTE ON PACKAGE  NULLID.SYSSN200 TO  USER   DMCUSR1 ;

Further explicit grants without using SQLADM

Users requiring further explicit grants without the use of SQLADM can access them by using the following set
minexpgrants.sql

Note: Use of these explicit grants will require more maintenance on the part of the user.

Minimum explicit grants for monitoring only:

GRANT CONNECT ON DATABASE TO USER  DMCUSR1 ;

Wildcard grants to all the procedures and functions:

GRANT EXECUTE ON FUNCTION  SYSPROC.*   to user DMCUSR1;
GRANT EXECUTE ON PROCEDURE SYSPROC.*   to user DMCUSR1;
GRANT EXECUTE ON PROCEDURE SYSIBM.SQLCAMESSAGECCSID  to  user  DMCUSR1;

Grants on workloads:

GRANT USAGE ON WORKLOAD SYSDEFAULTUSERWORKLOAD      to user  dmcusr1  ;

Grants on packages:

GRANT EXECUTE ON PACKAGE NULLID.SYSSH200 TO USER DMCUSR1;

GRANT EXECUTE ON PACKAGE NULLID.SYSSH100 TO USER DMCUSR1;

GRANT EXECUTE ON PACKAGE NULLID.SYSSN200 TO USER DMCUSR1 ;

Explicit grants:

GRANT SELECT ON  sysibm.sysdummy1 to USER  DMCUSR1   ;

GRANT SELECT ON  sysibm.sysversions to USER  DMCUSR1   ;

Explicit grant to catalog tables (no view). This is used by the Track Changes functions.

GRANT SELECT ON  SYSIBM.SYSUSERAUTH     TO USER   DMCUSR1 ;

Catalog Views:

Users must explicitly grant SELECT on all the catalog and administrative views. They must generate the grants by putting the following SQL statements into a file, running it, and redirecting the output to a file to run the resulting grants. This ensures that the grants are in sync with the catalog.
gengrants.sql

Generate the necessary SQL for the grant commands for the catalog and admin views. The result is in the following format:

grant select on table syscat.xxx

Include the following SELECT statement in your file:


select  'GRANT SELECT ON TABLE '|| trim(tabschema) ||'.'|| upper(trim(tabname)) ||' TO USER  DMCUSR1;' as t
from syscat.tables 
where type = 'V' and tabschema in( 'SYSIBMADM', 'SYSCAT') order by tabschema, tabname;

For example, if you create a file that is named gengrants.sql, containing the previous script, you would run it like this:

db2 –tvf gengrants.sql > gennedgrants.sql

db2 –tvf gennedgrants.sql > gennedgrants.out

EXPLAIN

EXPLAIN requires additional privileges. When a user starts an EXPLAIN task, the task runs using the operation credential ID.

The users of the Data Management Console operation credential ID require access to the EXPLAIN tables. SELECT, INSERT, UPDATE, and DELETE privileges must be granted to the operation credential ID for access to all the EXPLAIN tables.

gengrants.sql

(assuming tables are already created by some other method)

GRANT SELECT, INSERT, UPDATE, DELETE ON  SYSTOOLS.ADVISE_INDEX  to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  SYSTOOLS.ADVISE_INSTANCE  to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  SYSTOOLS.ADVISE_MQT to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  SYSTOOLS.ADVISE_PARTITION to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  SYSTOOLS.ADVISE_TABLE to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON  SYSTOOLS.ADVISE_WORKLOAD to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTOOLS.EXPLAIN_ACTUALS to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTOOLS.EXPLAIN_ARGUMENT to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTOOLS.EXPLAIN_DIAGNOSTIC to user DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTOOLS.EXPLAIN_DIAGNOSTIC_DATA to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE  ON  SYSTOOLS.EXPLAIN_INSTANCE to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE  ON  SYSTOOLS.EXPLAIN_OBJECT to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE  ON  SYSTOOLS.EXPLAIN_OPERATOR to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE  ON  SYSTOOLS.EXPLAIN_PREDICATE to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTOOLS.EXPLAIN_STATEMENT to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE  ON  SYSTOOLS.EXPLAIN_STREAM to user  DMCUSR1 ;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYSTOOLS.OBJECT_METRICS to user  DMCUSR1;

Administration tasks under Explore database or SQL editor: The operation credential ID is used for such tasks.