What is the best practice for getting the current username at the data access level?

Question

We recently added auditing to our database. A colleague executed it using triggers and asked me to call a stored procedure when I entered the site. The stored procedure inserts the current username and current oracle session identifier into the table so that the trigger can match the session identifier with the username. The problem (or was) was that he assumed that the user's internet session was mapped to the database session. This is not the case, and we use the connection pool, so oracle session identifiers can be displayed for many users, not necessarily for the user who is logged into this session. Therefore, I created a utility method at my data access level that calls its procedure for each insertion, update, and deletion (ensuring that it is in the same transaction):

/// <summary> /// Performs an insert, update or delete against the database /// </summary> /// <param name="transaction"></param> /// <param name="command">The command.</param> /// <param name="transaction">A transaction, can be null. /// No override provided without a transaction, to remind developer to always consider transaction for inserts, updates and deletes</param> /// <returns>The number of rows affected by the operation</returns> public static int InsertUpdateDelete(OracleCommand command, OracleTransaction transaction) { if (command == null) throw new ArgumentNullException("command", "command is null."); OracleConnection connection = null; bool doCommit = false; try { if (transaction == null) { //We always need a transaction for the audit insert connection = GetOpenConnection(); transaction = connection.BeginTransaction(); doCommit = true; } command.Transaction = transaction; command.Connection = transaction.Connection; //TODO HttpContext requires that presentation layer is a website. So this call should NOT be in the data access layer. string username = HttpContext.Current.User.Identity.Name; if (!String.IsNullOrEmpty(username)) pInsertCurrentUserForAudit(username, command.Transaction); int recordsAffected = command.ExecuteNonQuery(); if (doCommit) transaction.Commit(); return recordsAffected; } finally { if (doCommit) { if (transaction != null) transaction.Dispose(); if (connection != null) connection.Dispose(); } } } 

This work and audit now works as needed. However, I don't like the HttpContext call:

 string username = HttpContext.Current.User.Identity.Name; 

It was the fastest way to accomplish the task, but I don’t think it should be at the data access level. What if, at some unknown time in the future, I wanted to access the database using the forms application? Did you get an error accessing the HttpContext? Is there a better way to get a username that appropriately shares issues? Passing a username as a parameter for each insert, update, and delete is an option, but it will be a lengthy task, and I was wondering if there was a more elegant way to do this.

Answer 1

What you did is definitely not the best approach (as you described above in your question). This is one of those things called a cross-cutting issue - others are things like logging, etc.)

One approach that is used is to bypass a context object that implements functionality for all such cross-cutting tasks, so that each method at each level does not need to be modified to transfer the data necessary to implement the desired functionality.

Otherwise, as you think, you will have to pass the username to the data layer higher than higher in every method that he needs. If possible, one of the alternatives is to introduce a base class for all such methods (all data-level methods) and put this method in this base class ...