(DI-2302) Prerequisites for Oracle Connect User

Owned by SNP
Feb 02, 2023
2 min read

This page describes the prerequisites for a connect user for a standalone Oracle database monitoring.

Prerequisites for Oracle Connect User

If you monitor the Oracle database via the external DB connection (DBCON) defined in the SAP system, the connect user must exist within the monitored Oracle database. Additionally, this database connect user used in this external DB connection must have "read" authorization to the following tables and views:

CREATE SESSION
PRODUCT_COMPONENT_VERSION
V$OSSTAT
V$INSTANCE
V$DATABASE
GV$SYSSTAT
V$METRICNAME

GV$SYSMETRIC_HISTORY
GV$SGAINFO
GV$PGASTAT
GV$OSSTAT
GV$INSTANCE
SELECT_CATALOG_ROLE

More information can be found in the chapter How to grant authorizations for Oracle Connect User.

It is recommended to use a specific and restricted connect user for every system for Oracle database monitoring. Due to password management and other security reasons, it is not recommended to use SAP system users outside of their originally intended scope.

How to grant authorizations for Oracle Connect User

This chapter shows the list of DBA commands to grant “read” authorizations for connect users. These DBA commands must be executed as SYSDBA user.

In the following examples, the user name CBMON_CC1 is used as a connect user. This user must be created in the Oracle database.

CREATE USER CBMON_CC1 IDENTIFIED BY <password>;

GRANT CREATE SESSION TO CBMON_CC1;
GRANT SELECT ON PRODUCT_COMPONENT_VERSION TO CBMON_CC1;
GRANT SELECT ON SYS.V_$OSSTAT TO CBMON_CC1;
GRANT SELECT ON SYS.V_$INSTANCE TO CBMON_CC1;
GRANT SELECT ON SYS.V_$DATABASE TO CBMON_CC1;
GRANT SELECT ON SYS.GV_$SYSSTAT TO CBMON_CC1;
GRANT SELECT ON SYS.V_$METRICNAME TO CBMON_CC1;
GRANT SELECT ON SYS.GV_$SYSMETRIC_HISTORY TO CBMON_CC1;
GRANT SELECT ON SYS.GV_$SGAINFO TO CBMON_CC1;
GRANT SELECT ON SYS.GV_$PGASTAT TO CBMON_CC1;
GRANT SELECT ON SYS.GV_$OSSTAT TO CBMON_CC1;
GRANT SELECT ON SYS.GV_$INSTANCE TO CBMON_CC1;
GRANT SELECT_CATALOG_ROLE TO CBMON_CC1;

If SQL collector is used for custom KPIs, it may require additional privileges to avoid the typical Oracle error “ORA-00942: table or view does not exist“ due to missing permissions.

How to test authorizations for Oracle Connect User

When you add a new system for Oracle database monitoring, the accessibility of the above-mentioned tables and views is checked automatically. However, you might also check the accessibility with a simple SQL statement by typing the following statement in the standard SAP tool (transaction ST04 or DB50) or via the report “ADBC_QUERY”:

SELECT * FROM all_objects WHERE object_name IN ( 'PRODUCT_COMPONENT_VERSION', 'V$DATABASE', 'V$OSSTAT', 'V$INSTANCE', 'GV$INSTANCE', 'GV$OSSTAT', 'GV$PGASTAT', 'GV$SGAINFO', 'V$METRICNAME', 'GV$SYSMETRIC_HISTORY', 'GV$SYSSTAT' )

The list of accessible tables and views will be displayed after executing the SQL statement. For the missing ones, you need to grant the authorization as mentioned in the chapter (DI-2302) Prerequisites for Oracle Connect User.