node-oracledb

18. End-to-end Tracing, Mid-tier Authentication, and Auditing

The Connection properties action,
module, and clientId set
metadata for
end-to-end tracing.
The values can be tracked in database views, shown in audit trails,
and seen in tools such as Enterprise Manager.

The clientId property can also be used by applications that do their
own mid-tier authentication but connect to the database using the one
database schema. By setting clientId to the application's
authenticated username, the database is aware of who the actual end
user is. This can, for example, be used by Oracle
Virtual Private Database
policies to automatically restrict data access by that user.

Applications should set the properties because they can greatly help
to identify and resolve unnecessary database resource usage, or
improper access.

The attributes are set on a connection object and
sent to the database on the next 'round-trip' from node-oracledb, for
example, with execute():

oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/orcl"
  },
  function(err, connection)
  {
    if (err) { console.error(err.message); return;    }
    connection.clientId = "Chris";
    connection.module = "End-to-end example";
    connection.action = "Query departments";
    connection.execute("SELECT . . .",
      function(err, result)
      {
        . . .

While the connection is open the attribute values can be seen, for example with SQL*Plus:

SQL> SELECT username, client_identifier, action, module FROM v$session WHERE username = 'HR';
USERNAME   CLIENT_IDENTIFIER    ACTION               MODULE
---------- -------------------- -------------------- --------------------
HR         Chris                Query departments    End-to-end example

The values can also be manually set by calling
DBMS_APPLICATION_INFO
procedures or
DBMS_SESSION.SET_IDENTIFIER,
however these cause explicit round-trips, reducing scalability.

In general, applications should be consistent about how, and when,
they set the end-to-end tracing attributes so that current values are
recorded by the database.

Idle connections released back to a connection pool will retain the
previous attribute values of that connection. This avoids the overhead
of a round-trip to reset the values. The Oracle design assumption is
that pools are actively used and have few idle connections. After
getting a connection from a pool, an application that uses end-to-end
tracing should set new values appropriately.

When a Connection object is displayed, such as with console.log(),
the end-to-end tracing attributes will show as null even if values
have been set and are being sent to the database. This is for
architectural, efficiency and consistency reasons. When an already
established connection is retrieved from a local pool, node-oracledb
is not able to efficiently retrieve values previously established in
the connection. The same occurs if the values are set by a call to
PL/SQL code - there is no efficient way for node-oracledb to know the
values have changed.

The attribute values are commonly useful to DBAs. However, if knowing
the current values is useful in an application, the application should
save the values as part of its application state whenever the
node-oracledb attributes are set. Applications can also find the
current values by querying the Oracle data dictionary or using PL/SQL
procedures such as DBMS_APPLICATION_INFO.READ_MODULE() with the
understanding that these require round-trips to the database.