DBCollect/Credentials File

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 DICTIONARY privilege
• An SQLNet database login must be available with SELECT ANY DICTIONARY privileges (or read access to v$, DBA_\* and CDB_\* tables).
• This requires the listener to be available and listening for the provided service.

The DBSNMP user is predefined with these privileges. When using DBSNMP for this purpose, it must be unlocked and have a valid password on each instance.

The ORACLE_HOME will be retrieved from /etc/oratab but can be provided with --orahome if oratab is not valid or available for the OS user.

The database user/password must be provided in the connectstring as such:

<instance>:<enabled Y|N>:<url>

Where url is something like <dbuser/dbpassword>@//<fqdn>/<service name>

For example:

orcl:Y:dbsnmp/topsecret@//example.local/orcl
orcl2:N:johndoe/topsecret@//test.local/orcl2

Note that dbcollect will try to connect to each enabled and running instance, and will fail if any of the provided credentials are invalid or missing, or the connection cannot be made for whatever reason. There is a 2 second timeout for hanging connections.

The OS user does not even need to have a valid OS login, it can be executed as root using the ```runuser``` command:

``` echo '/usr/local/bin/dbcollect -o --dbcreds /tmp/creds' | runuser nobody -s /bin/bash

``` An example wrapper script is provided in the [contrib](https://github.com/outrunnl/dbcollect/tree/master/scripts) directory.