DBCollect/Credentials File: Difference between revisions
Created page with "Category:DBCollect = Using a credentials file = Some customers asked for a way to run {{dbcollect}} without <code>oracle</code>, or <code>sysdba</code> (OPS$ connection) privileges. Using <code>OPS$</code> connections as <code>sysdba</code> user has the huge advantage that the user does not have to enter any passwords for connecting to the databases, as SQL*Plus will connect as sysdba with OS privileges. The user (usually <code>oracle</code>) needs <code>dba</code..." |
|||
| Line 19: | Line 19: | ||
= Requirements = | = Requirements = | ||
* OS user must have access to a valid ORACLE_HOME for running | * OS user must have access to a valid ORACLE_HOME for running <code>sqlplus</code>. | ||
* A credentials file must be provided with a (readable) file containing a valid connect url for each instance. | * A credentials file must be provided with a (readable) file containing a valid connect url for each instance. | ||
* The privileges must be provided in the credentials file where each line has the form | * The privileges must be provided in the credentials file where each line has the form <code><instance_name>:<connection_url></code>. | ||
* The database user must have read access to v$, DBA_ | * The database user must have read access to v$, DBA_* and CDB_* tables (provided by the <code>SELECT ANY DICTIONARY</code> privilege | ||
* An SQLNet database login must be available with | * An SQLNet database login must be available with <code>SELECT ANY DICTIONARY</code> privileges (or read access to v$, DBA_\* and CDB_\* tables). | ||
* This requires the listener to be available and listening for the provided service. | * This requires the listener to be available and listening for the provided service. | ||
Revision as of 10:36, 9 December 2024
Using a credentials file
Some customers asked for a way to run dbcollect without oracle, or sysdba (OPS$ connection) privileges.
Using OPS$ connections as sysdba user has the huge advantage that the user does not have to enter any passwords for connecting to the databases, as SQL*Plus will connect as sysdba with OS privileges. The user (usually oracle) needs dba and asm group privileges on the OS level for this to work (but the oracle user almost always has this).
dbcollect is designed to never change files on the OS except within temporary directories, and only uses SELECT statements on system tables, so it can never break or modify a working database installation. But theoretically it is possible if there would be bugs causing this to happen.
One customer suggested to use the DBSNMP user for this instead, as it is created by default on Oracle (except disabled and not provided with a password). Other non-standard usernames can be used as well, as long as the user is allowed to login and has access to the right system tables and procedures.
A caveat with this method is that the user must connect over Oracle Net (SQL*Net) via a valid Oracle listener, using a service name that may differ from the instance name, using a valid connect string that includes the username and a valid password.
The Operating System user can be any user having read access to an ORACLE_HOME (to run sqlplus and some other procedures).The OS user can even be nobody and not have a password or login shell.
A credentials file must be provided for this to work with at least one entry for every ORACLE_SID detected on the host.
Requirements
- OS user must have access to a valid ORACLE_HOME for running
sqlplus. - A credentials file must be provided with a (readable) file containing a valid connect url for each instance.
- The privileges must be provided in the credentials file where each line has the form
<instance_name>:<connection_url>. - The database user must have read access to v$, DBA_* and CDB_* tables (provided by the
SELECT ANY DICTIONARYprivilege - An SQLNet database login must be available with
SELECT ANY DICTIONARYprivileges (or read access to v$, DBA_\* and CDB_\* tables). - This requires the listener to be available and listening for the provided service.