DBCollect/Logons File: Difference between revisions

From Dirty Cache Wiki
Jump to navigation Jump to search
← Older editNewer edit →
VisualWikitext
m Bart moved page DBCollect/Credentials File to DBCollect/Logons File: Reflect dbcollect changes
No edit summary
Line 1: Line 1:
[[Category:DBCollect]]
[[Category:DBCollect]]


= Using a credentials file =
= Using a logons file =


Some customers asked for a way to run {{dbcollect}} without <code>oracle</code>, or <code>sysdba</code> (OPS$ connection) privileges.
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> and <code>asm</code> group privileges on the OS level for this to work (but the oracle user almost always has this).
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>OSDBA</code> and <code>ASMDBA</code> 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 <code>SELECT</code> 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.
{{dbcollect}} is designed to never change files on the OS except within temporary directories, and only uses <code>SELECT</code> 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.
Line 14: Line 14:


The Operating System user can be any user having read access to an ORACLE_HOME (to run <code>sqlplus</code> and some other procedures).The OS user can even be <code>nobody</code> and not have a password or login shell.
The Operating System user can be any user having read access to an ORACLE_HOME (to run <code>sqlplus</code> and some other procedures).The OS user can even be <code>nobody</code> 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 =
= Requirements =


* OS user must have access to a valid ORACLE_HOME for running <code>sqlplus</code>.
* 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 logons 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 <code><instance_name>:<connection_url></code>.
* The privileges must be provided in the logons file where each line has the form <code>user/password@hostname/instance</code>.
* The database user must have read access to v$, DBA_* and CDB_* tables (provided by the <code>SELECT ANY DICTIONARY</code> privilege
* 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 <code>SELECT ANY DICTIONARY</code> privileges (or read access to v$, DBA_\* and CDB_\* tables).
* The database user must have EXECUTE permissions on <code>dbms_workload_repository</code> (to generate AWR reports)
* 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.


The <code>DBSNMP</code> user is predefined with these privileges. When using <code>DBSNMP</code> for this purpose, it must be unlocked and have a valid password on each instance.
The <code>DBSNMP</code> user is predefined with these privileges. When using <code>DBSNMP</code> for this purpose, it must be unlocked and have a valid password on each instance.
Line 34: Line 31:


<syntaxhighlight lang=bash>
<syntaxhighlight lang=bash>
<instance>:<enabled Y|N>:<url>
<instance>:<enabled Y|N>:<url>
</syntaxhighlight>
</syntaxhighlight>
Line 41: Line 39:
For example:
For example:
<syntaxhighlight lang=bash>
<syntaxhighlight lang=bash>
orcl:Y:dbsnmp/topsecret@//example.local/orcl
dbsnmp/topsecret@//example.local/orcl
orcl2:N:johndoe/topsecret@//test.local/orcl2
johndoe/topsecret@//test.local/orcl2
</syntaxhighlight>
</syntaxhighlight>
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 <code>runuser</code> command:
The OS user does not even need to have a valid OS login, it can be executed as root using the <code>runuser</code> command:
Line 51: Line 47:
<syntaxhighlight lang=bash>
<syntaxhighlight lang=bash>
echo '/usr/local/bin/dbcollect -o --dbcreds /tmp/creds' | runuser nobody -s /bin/bash
echo '/usr/local/bin/dbcollect -o --dbcreds /tmp/creds' | runuser nobody -s /bin/bash
# or, cleaner:
runuser nobody -s /bin/bash <<< "/usr/local/bin/dbcollect <options>"
</syntaxhighlight>
</syntaxhighlight>


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

Revision as of 14:43, 31 August 2025


Using a logons 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 OSDBA and ASMDBA 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.

Requirements

  • OS user must have access to a valid ORACLE_HOME for running sqlplus.
  • A logons file must be provided with a (readable) file containing a valid connect url for each instance.
  • The privileges must be provided in the logons file where each line has the form user/password@hostname/instance.
  • The database user must have read access to v$, DBA_* and CDB_* tables (provided by the SELECT ANY DICTIONARY privilege
  • The database user must have EXECUTE permissions on dbms_workload_repository (to generate AWR reports)
  • 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: 

dbsnmp/topsecret@//example.local/orcl
johndoe/topsecret@//test.local/orcl2

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
# or, cleaner:
runuser nobody -s /bin/bash <<< "/usr/local/bin/dbcollect <options>"

An example wrapper script is provided in the contrib directory.

Retrieved from "https://wiki.dirty-cache.com/index.php?title=DBCollect/Logons_File&oldid=663"