Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Easy Auditing a Shared Account

By Sergey Pustovit,

Single account security audit trail system using SET CONTEXT_INFO

This article explains how to develop security audit trail system using single account.

A lot of applications require logging changes that has been made to the database. Sometime it is really important to find fast who did what. SQL Server has pretty powerful feature to accomplish this goal - triggers. So we can develop triggers for UPDATE, DELETE, INSERT events and each time when somebody changed the record we can store result of SUSER_SNAME() function to the some field. But there is a trick. In order to do so application should connect to the database with different accounts for each user. This is how SUSER_SNAME() works - there should be either SQL Server login or Microsoft Windows NT account granted to the SQL Server.

Well, here we got an issue. A lot of service providers (ISP) doesn't allow to create SQL logins, only what we have - one single account to access DB. Also it is pretty common practice in a big/medium companies to restrict SQL Server logins usage for the same reason - security concerns. Why? Well it is a good question, there are some pros and cons and technically we can argue but this is way out of scope of this article.

So what can we do to log changes and stay with one single account limitation?

Well we can probably set login each time connection is established. This should be definitely unique to each session. Also it should be easy and convenient enough to prevent system complexity and a lot of additional coding. Also it should be flexible enough in order to be able to convert existent security audit trail systems based on triggers and SUSER_SNAME(), SUSER_SID() security functions. How to so this? Well, answer is coming…

There is a command in SQL Server 2000:


From BOL:

Associates up to 128 bytes of binary information with the current session or connection.

The great thing about this command - it set any binary value for the current context. So each connection may have its own value set.

To explain how to use this command lets create simple table to store users in our application:

	ID	int IDENTITY(1,1),
	Login	varchar(50) NOT NULL,
	FirstName	varchar(50) NULL,
	LastName	varchar(50) NULL

As you see this table contain ID column created as an identity.

It is pretty common practice of course in order to build database relationship but in our case it will play another pretty important role. Lets see how exactly little bit later.

Now lets insert some simple records to this table:

SELECT 'jsmith','John','Smith'
SELECT 'mdavis','Michael','Davis'
SELECT 'rjohns','Richard','Johns'

So each user in a table has unique identifier now.


select * from tblUsers


ID         Login           FirstName        LastName                    
---------- --------------- ---------------- ---------------------------------
1          jsmith          John             Smith
2          mdavis          Michael          Davis
3          rjohns          Richard          Johns

(3 row(s) affected)

Now lets create a stored procedure to set session context. Look at the code:

CREATE  PROCEDURE uspSetContextLogin
	@Login	varchar(50)= ''

DECLARE	@ContextInfo varbinary(128)

IF @Login <> '' 
	SELECT @ContextInfo = convert(varbinary(128),ID) 
	FROM 	tblUsers 
	WHERE Login = @Login

	IF @ContextInfo is NULL SET @ContextInfo = 0

	SELECT @ContextInfo AS ContextInfo, convert(int,@ContextInfo) AS INTContextInfo

The stored procedure uspSetContextLogin contain parameter @Login.

We'll need to pass user login to set it to the current context. The context value itself is varbinary(128). This is why it was important to have unique identifier with int data type. It is really easy to convert integer value to the varbinary. So we’re looking for the ID of user with particular login, then checking if it is exists and if so we're setting context to this value, otherwise setting context to 0.

Query: uspSetContextLogin 'rjohns'


ContextInfo		                     INTContextInfo
----------------------------------- --------------
0x00000003                          3

So, user Robert Johnson was set with login rjohnson, his unique ID – 3. Now let see how we can get login name back based on context that was already set.

Because the entire purpose of all of this to keep track of changes to the system we definitely will use it from triggers. To make it as easy as possible lets create get process as function, not as procedure.

CREATE  FUNCTION dbo.fnGetContextLogin()

RETURNS varchar(50) AS BEGIN DECLARE @Login varchar(50) SELECT @Login = Login FROM tblUsers WHERE convert(varbinary(128),ID) = (SELECT context_info FROM master..sysprocesses(Nolock)WHERE spid = @@SPID) IF @Login is NULL SET @Login = SUSER_SNAME() RETURN(@Login) END

As we see from code SQL Server store context information in system table sysprocesses. So we can get it from there using current process id information with @@SPID. It is very important that context is available for current session. Each user in each session will have its own context and will able to set and retrieve it. So, now we have the way to set and get current context.

Lets talk about original question, how to handle changes with one single account. And how setting context may help us. Well we know that we have only one account to connect to the DB. With one account we'll not able to get unique name for each user to log his changes. But what we can do, we can set context in each session right after connection has been established. Context need to be set each time we connect to the DB, so on a DB side we would able to get context. For example let say John Smith will login to the application with his login name – jsmith. First we'll establish the connection to database with some single system account, then, right after connection has been established, we'll set context:

uspSetContextLogin 'jsmith'

Well, now on a DB side we can get current login name any time with fnGetContextLogin()

SELECT fnGetContextLogin()

Lets create some simple table in order to see how we can actually use it.

CREATE TABLE tblProducts
	Name		varchar(50),
	Quantity		int,
	Price		money,
	CreatedBy	varchar(50) DEFAULT dbo.fnGetContextLogin(),
	ChangedBy	varchar(50) DEFAULT dbo.fnGetContextLogin(),
	Created		datetime 	DEFAULT getdate(),
	Changed		datetime		DEFAULT getdate()

First of all we already taken care of new records. We have added default constraint with our function dbo.fnGetContextLogin(). So every time new record will be created CreatedBy and ChangedBy fields will be set to the user of current context. Of course context should be set prior to that. We'll touch this little bit later in this article.

Lets see now how we'll handle changes. Lets add trigger for update to our table.

CREATE TRIGGER UTrig_tblProducts ON tblProducts
UPDATE tblProducts
SET 	ChangedBy = dbo.fnGetContextLogin(),
	Changed = getdate()
FROM inserted i
WHERE i.ID = tblProducts.ID

Well now we're ready to see how it will work. Lets set context first:

uspSetContextLogin ‘jsmith'

Now, without closing the connection!!!

insert into tblProducts
(name, quantity, price)
select 'TV Panasonic',121,200

select * from tblProducts

ID   Name          Quantity  Price     CreatedBy ChangedBy 	Created		              Changed       
---- ------------  --------  --------- --------- ---------- -----------------------   ---------------
1    TV Panasonic  121       200.0000  jsmith    jsmith     2005-06-07 11:14:00.883   2005-06-07 11:14:00.883

As we see the item has been created by user jsmith. Now lets change context and update the quantity:

uspSetContextLogin ‘rjohns'

UPDATE tblProducts
SET quantity = 110

select * from tblProducts

ID  Name         Quantity Price    CreatedBy ChangedBy Created		              Changed         
--- ------------ -------- -------- --------- --------- -----------------------  -----------------------
1   TV Panasonic  121     200.0000 jsmith    rjohns    2005-06-07 11:14:00.883  2005-06-07 11:23:03.687

As we see record has been changed by user rjohns. So now we got an answer, we can track changes in a table and use only one single SQL account to connect to database.

The main thing is to set context each time when we'll run query. Would this possibly affect system performance? Well it depends. First of all we definitely would not need to set context when we're running SELECTs. This additional statement though is very small and will not take so much processor time or resources.

The only outstanding question now is how to set context. Well it should be done each time connection will be established. Obviously it is easy to do in a database layer of our application. Also it will probably make sense to add additional logic to the execute method to find when we'll run UPDATEs, INSERTs and everything else because it will make sense to set context only in case of UPDATE/INSERT operations.

Here I will illustrate schematically how it possibly can be used in a Web application

Lets for example say we have Execute method:

public DataSet Execute(string strCommand, string strConnectionInfo)
		// establish the connection connectionInfoString will contain connection info
		SqlConnection connSQL = new SqlConnection(strConnectionInfo);

		// ***** set current context here			
      // stored procedure - uspSetContextLogin

     SqlCommand cmdSQL = new
     cmdSQL.CommandType = CommandType.StoredProcedure;
     // parameter - @Login

     SqlParameter cmdSQLParam = cmdSQL.Parameters.Add("@Login", SqlDbType.VarChar, 50);

	  // assign to the parameter current login name
	  // function currentUserName() may get current login from global class 
	  cmdSQLParam.Value = currentUserName();

	  // here we'll execute the main query
	  SqlDataAdapter dataSQL = new SqlDataAdapter(strCommand,cn);
	  // retrieving the results, closing connections, etc.

So, as we see, every time we'll execute query, we'll set current context with current login name, so on DB side we'll able to get it. As I already mentioned it will probably a good idea either to check if operation is UPDATE or INSERT and set context only if it so or create different methods for UPDATE,INSERT and SELECT.

Sergey Pustovit
Data Architect,
Bank of Amercia

Total article views: 6965 | Views in the last 30 days: 6
Related Articles

Create Login from master

Create login for a database from master


Creating new login

Creating new login


Creating Logins

Creating Logins with limited permission


Difference between Create User and Create Login .....♀§♀§♀§

Difference between Create User and Create Login .....♀§♀§♀§


Create Login from master

Create login for a database from master