Connection Pooling vs Audit Logging
Posted
Thursday, June 26, 2008 12:17 PM
by
Nathan Zaugg
These past two weeks have been very exciting for me. I have gotten to be involved in some R&D for one of the companies that I consult for. I LOVE R&D! There is always a better way to do things and poking your head out from the sand every once in a while can be very beneficial!
Okay, so here is the story. You want to have auditing so you can log the user responsible for the change. It follows that you simply connect with that users credentials and now you have a great audit log! The problem is that if you have thousands of users (or maybe even less) you are going to start to experience a large number of connections on the server. [Image 1] This is because each user has their own connection pool that, even if it is going through a service, cannot be shared with any other user. A large number of connections is starting to really slow down your database so you decide to create a generic user account for the service. The problem is now our audit log will only show the service account as the person responsible for the change! [Figure 2]
Image 1
Image 2
So you have two ways in which you can fix this. First you can mandate that all changes to the data must happen through stored procedures. If we make sure that every stored procedure passes the user who is responsible for the DML changes then we can add our own audit records. The upside is that not only can we take full advantage of connection pooling and security is better using procs. The downside is that this can be intensive and the change log probably cannot be driven by triggers and we may have to come up with a complex and fallible process.
Alternatively, you can use a basic service account for the connection and connection pool and run the SQL 2005 / 2008 "EXECUTE AS LOGIN" command before any other DML statement. [Image 3] This is called User Context Switching and could be done automatically using a specialized command object. The only down side is that because SqlCommand is a sealed class we have to use composition rather than inheritance. This may also force us to create a compatible SqlDataAdapter but when all is said and done you have a system that is both scaleable and robust. These changes are also likely to be compatible with SQL Server 2008's CDC technology which can automatically log changes to a table.
-- TSQL TO CREATE A USER WITHOUT A LOGIN
-- AND USE USER CONTEXT SWITCHING
CREATE DATABASE [TestDB]
GO
USE [TestDB]
GO
-- Create the Service User
CREATE LOGIN [ServiceLogin] WITH PASSWORD = 'Uor80$23b91';
CREATE USER [ServiceLogin] FOR LOGIN [ServiceLogin]
GO
-- If we ran this before then we need to drop this user
DROP USER [nzaugg]
GO
-- Create a user without a login
CREATE USER [nzaugg] WITHOUT LOGIN
GO
-- Wade said this is backward, so I swaped it for him...although I'm not fully convinced!
GRANT IMPERSONATE ON [nzaugg] TO USER::[ServiceLogin]
GO
-- Switch User Context; Optionally Specify 'NO REVERT'
-- If we run this in Query Editor with 'NO REVERT' the
-- only way to go back to our original login is to reconnect!
EXECUTE AS USER = 'nzaugg' --WITH NO REVERT
GO
-- Verify that we are now user 'nzaugg'
SELECT user_name(), suser_name(), original_login()
-- If we used 'WITH NO REVERT' on our EXECUTE AS statment
-- We won't be able to revert and this will throw an exception
REVERT
GO
-- Are we still 'nzaugg'?
SELECT user_name(), suser_name(), original_login()
-- DROP THE DATABASE
DROP LOGIN [ServiceLogin]
GO
USE [master]
GO
DROP DATABASE [TestDB]
GO
Remember, in order to do this all of these users must exist in the database. They must also have rights to perform the operation in the original DML statement. This is where users without logins come in handy (see code lines 18 & 21). The optional WITH NO REVERT will be handy for logging and will further secure our database.
Image 3
EXECUTE AS MSDN Paragraph
SQL Server 2005 Books Online (September 2007)
EXECUTE AS (Transact-SQL)
Sets the execution context of a session.
By default, a session starts when a user logs in and ends when the user logs off. All operations during a session are subject to permission checks against that user. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement. In essence, the user or login account is impersonated for the duration of the session or module execution, or the context switch is explicitly reverted. For more information about execution context, see Understanding Execution Context. For more information about context switching, see Understanding Context Switching.
References:
Downloads: