************************************************************
* 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