| By product |
| Other |
|
Knowledgebase Tips and How-to Articles for ApexSQL Tools How to audit user names in ASP.Net application Applies to ApexSQL Audit Summary This KB article explains how to modify an ASP.Net application so that it can give ApexSQL Audit its usernames/application information. Description To define the application’s user name within the audit trigger, use SQL connection’s CONTEXT_INFO functionality. The following query will set the user name for current connection: ------------------------------------------------------ DECLARE @BinVar varbinary(128) SET @BinVar = CAST (N'User Name' AS varbinary(128) ) SET CONTEXT_INFO @BinVar ------------------------------------------------------ In the audit trigger, this user name can be obtained from its context by the following query: ------------------------------------------------------ SELECT CAST(CONTEXT_INFO() AS NVARCHAR(64) ) ------------------------------------------------------ The typical function to execute queries or stored procedure calls in ASP.Net applications looks like below: ------------------------------------------------------ public static int ExecuteNonQuery(string connectionString, CommandTypecommandType, string commandText, params SqlParameter[]commandParameters){if ( connectionString == null connectionString.Length== 0 ) throw new ArgumentNullException( "connectionString" ); // Create & open a SqlConnection, and dispose it after we are done using (SqlConnection connection = new SqlConnection(connectionString)){connection.Open(); // Call the overloaded method that takes a ready connection instead of the connection stringreturn ExecuteNonQuery(connection, commandType, commandText,commandParameters);}} ------------------------------------------------------ Instead of directly creating a SqlConnection, create a function that will set up the newly created connection. Note that in below example, the “username” parameter is the desired application user name we want ApexSQL Audit to log in the audit triggers: ------------------------------------------------------ public static SqlConnection NewSqlConnection(string connectionString,string username){if( connectionString == null connectionString.Length == 0 )throw new ArgumentNullException( "connectionString" );SqlConnection connection = new SqlConnection(connectionString);connection.Open();// Set username for the created connection in CONTEXT_INFO SqlCommand command = connection.CreateCommand();command.CommandText = string.Format("DECLARE @BinVar varbinary(128)" +" SET @BinVar = CAST(N'{0}' AS varbinary(128) ) "+"SET CONTEXT_INFO @BinVar ", username);command.ExecuteNonQuery();return connection;} ------------------------------------------------------ Change the first method using our helper function: ------------------------------------------------------ public static int ExecuteNonQuery(string connectionString, string username, CommandType commandType, string commandText, params SqlParameter[] commandParameters){// Create & open a SqlConnection, and dispose it after we are done using (SqlConnection connection = NewSqlConnection(connectionString, username)){// Call the overloaded method that takes a ready connection instead of the connection stringreturn ExecuteNonQuery(connection, commandType,commandText, commandParameters);}} ------------------------------------------------------ Call Function NewSqlConnection when new connection is created. It will set up the user name and guarantee that the command will always be executed from the appropriate user. user name will now be shown in the trigger’s context: ------------------------------------------------------ INSERT INTO {%AUDIT.PRINT DATABASENAME%}.{%AUDIT.PRINT TRANSACTIONSTABLENAME%} ( TABLE_NAME, TABLE_SCHEMA, AUDIT_ACTION_ID, HOST_NAME, APP_NAME, MODIFIED_BY, MODIFIED_DATE, AFFECTED_ROWS, [DATABASE] ) VALUES( '{%Audit.Print Replace(objTable.Name, "'", "''")%}', '{%Audit.Print Replace(objTable.Owner, "'", "''")%}', 2, -- ACTION ID For INSERT CASE WHEN LEN(HOST_NAME()) < 1 THEN ' ' ELSE HOST_NAME() END, CASE WHEN LEN(APP_NAME()) < 1 THEN ' ' ELSE APP_NAME() END, CAST(CONTEXT_INFO() AS NVARCHAR(64) ), GETDATE(), @ROWS_COUNT, '{%Audit.Print Audit.Database.Name%}' ) ------------------------------------------------------ To define the application’s user name along with application's name, function's name and client pc name within the audit trigger, use the following: ------------------------------------------------------ public static SqlConnection NewSqlConnection(string connectionString,string username,string app_name,string client_pc_name,string func_name){if( connectionString == null connectionString.Length == 0 )throw new ArgumentNullException( "connectionString" );SqlConnection connection = new SqlConnection(connectionString);connection.Open();// Set username for the created connection in CONTEXT_INFO SqlCommand command = connection.CreateCommand();command.CommandText = string.Format("DECLARE @BinVar varbinary(128)" +" SET @BinVar = CAST(N'{0}/{1}/{2}/{3}' AS varbinary(128) ) "+"SET CONTEXT_INFO @BinVar ", username, app_name, client_pc_name, func_name);command.ExecuteNonQuery();return connection;} ------------------------------------------------------ This will create one long string with all required data separated with the slash (/) sign. NOTE: Only 128 characters to store the data are available. In above approach interface in all functions has to be changed. To make changes only in the data access layer, create an additional helper class. ------------------------------------------------------ internal sealed class SqlHelper{private string username = string.Empty;public SqlHelper(string username){this.username = username;}public SqlConnection NewSqlConnection(stringconnectionString){if( connectionString == nullconnectionString.Length == 0 ) throw newArgumentNullException( "connectionString" );SqlConnection connection = new SqlConnection(connectionString);connection.Open();// Set username for the created connection in CONTEXT_INFOSqlCommand command = connection.CreateCommand();command.CommandText = string.Format("DECLARE @BinVar varbinary(128)" +" SET @BinVar = CAST(N'{0}' ASvarbinary(128) ) "+"SET CONTEXT_INFO @BinVar ", username);command.ExecuteNonQuery();return connection;}} ------------------------------------------------------ And this will not change the interface: ------------------------------------------------------ public int ExecuteNonQuery(string connectionString, CommandTypecommandType, string commandText, params SqlParameter[]commandParameters){// Create & open a SqlConnection, and dispose of it after we are doneusing (SqlConnection connection = NewSqlConnection(connectionString)){// Call the overload that takes a connection in place of the connection stringreturn ExecuteNonQuery(connection, commandType,commandText, commandParameters);}} ------------------------------------------------------ Connection provider should be stored in the session variables or created for each usage with the username (which is usually stored in the session variables). Last updated November 24, 2011 Labels: Apex SQL Audit |
