Applications Systems
Register an Amazon RDS Oracle database instance for DPA monitoring without providing the credentials of a privileged user
Register an Amazon RDS Oracle database instance without entering the master user password or privileged user credentials into DPA.
First published date
Last published date
Overview
When you register an Amazon RDS Oracle database instance for monitoring, the following change is made to that instance:
- The DPA monitoring user is created or configured to enable DPA to monitor the database instance. If DPA creates or configures the monitoring user, you must give DPA temporary access to the credentials of a user with the DBA role.
To register an Amazon RDS Oracle database without entering the credentials of a privileged user, you can create the monitoring user and add the utility package yourself.
- If you have DPA 2021.1 or earlier, use the instructions in this article to register an Amazon RDS Oracle database instance without entering the credentials of a privileged user into DPA. You can use mass registration or the DPA management API to register the instance.
- If you have DPA 2021.3 and later, you can use mass registration, the API, or a UI wizard to register an Amazon RDS Oracle database instance without a privileged user:
- To use a wizard, see Register an Amazon RDS for Oracle database instance in the DPA Administrator Guide.
- To use mass registration or the API, see the following sections.
Note: You must still know the password of a user with the DBA role (such as master user) in order to run the provided scripts. But you do not have to enter it into DPA.
Product section
Resolution
How to register an Amazon RDS Oracle database instance without entering the master user password
Step 1: Create the monitoring user with the necessary permissions
- Copy the following script to a file: CreateMonUserAmazonOracle.sql
- Edit the script to update the user name and password values.
- Connect to the Amazon RDS for Oracle database, and run the script. To ensure that the connected user has all the privileges needed to create the monitoring user, SolarWinds recommends connecting as to run the script.
Step 2: Register the database instance without specifying a privileged user
If you use DPA 2020.2.1 through 2021.1.x, use mass registration or the DPA management API as described below.
If you use DPA 2021.3 and later, you can use mass registration or the API as described below. Or you can use a wizard. To use a wizard, see Register an Amazon RDS for Oracle database instance in the DPA Administrator Guide.
Use mass registration or the DPA API to register the database instance, and omit the privileged user in the registration details.
-
The following example is a segment of a JSON script that calls the DPA API to register an Amazon RDS Oracle database instance without specifying a privileged user. Change the values to reflect your monitored database and the credentials of the monitoring user created in the previous step.
{ "databaseType": "ORACLE", "serverName": "127.0.0.1", "serviceNameOrSID": "SID", "port": 1521, "monitoringUser": "dpa_mon", "monitoringUserPassword": "Pa$$word1", "displayName": "DatabaseName", "amazonRds": true } -
If you register the instance through mass registration, use the spreadsheet for multiple database types as shown in the following shows an example.
- Leave the Privileged User and Privileged User Password columns empty.
- In the Monitoring User and Monitoring User Password columns, enter the credentials of the monitoring user created in the previous step.
- In the Create Monitoring User (Y/N) column, enter N.
- Leave the SYS Password column empty.
- In the Amazon RDS (Y/N) column, enter Y.
Monitoring user validation
During the registration, DPA validates that the monitoring user has the required permissions. By default, the registration fails if any mandatory permission is missing and displays a warning if optional permissions are missing. The same validations are also done when database connection details are updated.
This behavior can be overridden by setting the advanced option MONITOR_VALIDATION_OVERRIDE to TRUE. This allows the registration to pass even if the X$KSUSE view is missing. Some mandatory requirements cannot be overridden because they are needed to perform the validations. See the "Requirements overview" section for details.
Requirements overview
The following sections describe the privileges and objects required for DPA monitoring.
System privileges granted to the DPA monitoring user
| Privilege | Mandatory | Effect on DPA monitoring if missing |
|---|---|---|
| CREATE SESSION | Yes (cannot be overridden) | DPA cannot connect to the instance. Registration fails. |
| SELECT ANY DICTIONARY | Yes (cannot be overridden) | Registration fails with the message "Instance licensing information could not be retrieved." If this privilege is removed on an already registered instance, polling fails. |
| CREATE TABLE and UNLIMITED TABLESPACE | No | The 'Oracle Stale Statistics' alert does not work. Live execution plans for queries are not available. |
| ANALYZE ANY | No | Required for table tuning when the advanced option INDEX_ANALYSIS_FLUSH_STATS (support option) is set to TRUE. |
General database objects used for DPA monitoring
The following table describes the general objects used by the DPA monitoring user.
| View name | Mandatory | Effect on DPA monitoring if missing |
|---|---|---|
| X$KSUSE | Yes (can be overridden) | Wait time for queries is not available. |
| X$KGLNA1 | No | SQL text for queries is not available. |
| X$KCCCF | No | Control file names are not available. |
| X$KGLCURSOR | No | Statistics for queries are not available. |
| X$KCFIO | No | The storage I/O data is not available. |