 |
 |
************************************************************
* Description: Oracle session tracing via client identifier
* Date: 04:28 PM EST, 04/26/2018
************************************************************
<1> In real world, application usually uses a single database credential to access database via connection string:
|
|__ o. And, application user is using app credential to logon app, which are usually saved in a database table.
|
|__ o. So, it will be impossible to track or audit application users' actions in the database.
|
|__ o. Since Oracle 9i, the software provides a method for tracking app users' behavior via "CLIENT_IDENTIFIER" as a column in v$session.
|
|__ o. The CLIENT_IDENTIFIER is a predefined attribute of the built-in application context namespace, USERENV, and can be used to capture the application user name for use with
global application context, or it can be used independently.
<2> Taking C# connecting Oracle database as example:
|
|__ o. Following is the C# code uses embeded "ClientId" to tell Oracle database this variable can be saved under column CLIENT_IDENTIFIER in v$session for tracking purpose:
var_conn = new Oracle.DataAccess.Client.OracleConnection( connectionString ); #== 1. Define a connection variable
var_conn.Open(); #== 2. Grab a available connection process from connection pool
var_conn.ClientId = EmeralitClientID + "." + EmeralitUserRef; #== 3. Telling Oracle this is the CLIENT_IDENTIFIER to be stored in v$session
var_conn.dispose(); #== 4. Cleaning up the session to avoid taking physical memory resource.
<3> After Oracle database received notice from "var_conn.ClientId", it will execute below Oracle integrated procedure to set a user environment variable under session namespace:
|
|__ o. SQL> EXEC DBMS_SESSION.set_identifier('3006.amosgeng');
<4> Then, this application user's behavior in database can be tracked:
|
|__ o. Enable tracing for this app user by CLIENT_IDENTIFIER in v$session;
|
| SQL> exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE( client_id => '3006.amosgeng', binds => TRUE, waits => TRUE );
|
|
|
|
|__ o. Below query can fetch all the sessions are being traced:
|
| SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES;
|
|
|
|
|__ o. After tracing start, run following query to address the dumped trace file. The default location is defined by db parameter "user_dump":
|
| SQL> select a.client_identifier, b.tracefile from v$session a, v$process b where a.paddr=b.addr and a.client_identifier='3006.amosgeng';
|
|
|
|
|__ o. Disable tracing to avoid trace file size is overwhelm:
|
| SQL> exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE( client_id => '3006.amosgeng' );
|
|
|
|
|__ o. After Oracle database received notice from "var_conn.dispose()", it will execute below Oracle integrated procedure to cleanup session:
|
| SQL> EXEC DBMS_SESSION.clear_identifier;
|
|
|
|
|__ o. Since session cleaned, then the CLIENT_IDENTIFIER will be NULL afterwards. The username is the one in C# connection string:
SQL> SELECT USER AS username, SYS_CONTEXT('userenv', 'client_identifier') AS client_identifier FROM dual;
USERNAME CLIENT_IDENTIFIER
-------------------------------------- ---------------------------------
DB_USER_FOR_APP_CONNECTION NULL
<5> Tracing file naming convention:
|
|__ o. Usually, the trace file physical loction can be found out via "show parameter udump".
|
|__ o. The name usually consists of __.trc. For example:
ORATAX_j000_9201.trc ................. This is the trace file dumped by Oracle DBMS scheduler job j000.
ORATAX_smon_8612.trc ................. This is the trace file dumped by Oracle smon process.
ORATAX_mmon_402.trc ................. This is the trace file dumped by Oracle mmon process.
ORATAX_ora_3651.trc ................. This is the trace file dumped by Oracle user process, which turned on manually and indicated by "ora" in the middle.
<6> For C# applications, in some case, use connection pool to create database sessions is preferred:
|
|__ o. The purpose of connection pool is that, everytime when application connects to database, in physical layer, application needs to talk to listener for network hand shake,
| exchange authentication, etc which is taking time.
|
|__ o. As a solution, .NET frame provides a pool of database connection process. The process in this pool connecting to database all the time, so when a new application user
| opens a session, only needs to do is pick up one available connection prccess from the pool. So, there will be no need to exchange authentication info to save time.
|
|__ o. The pool size can be designed as needed, and when one db session done, the context info will be cleard, and connection process will be freed back to pool.
|
|__ o. Following C# code is connecting database through pool:
|
|
| Conn = new Oracle.DataAccess.Client.OracleConnection( connectionstring )
| Conn.ClientId = “3006.amosgeng";
| Conn.Open() #=== 1. Open() only reserves an available connection process from the pool.
| #=== 2. Until this point, no data packet exchange with database server.
| OracleCommand cmd = conn.CreateCommand();
| cmd.CommandText = sql;
| using (OracleDataReader rdr = cmd.ExecuteReader()) #=== 3. ExecuteReader() sends the Command Text to the connection, then database session starts from then.
|
|
|
|__ o. Only after above #3, the client_identifier has value. In prior, the value would be NULL.
<7> Reference:
|
|__ o. https://www.integrigy.com/oracle-security-blog/oracle-audit-vault-oracle-client-identifier-and-last-login
|
|__ o. https://oracle-base.com/articles/misc/dbms_session
Your Comments