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:
SET CONTEXT_INFO
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:
CREATE TABLE tblUsers ( IDint IDENTITY(1,1), Loginvarchar(50) NOT NULL, FirstNamevarchar(50) NULL, LastNamevarchar(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:
INSERT INTO tblUsers (Login,FirstName,LastName) SELECT 'jsmith','John','Smith' UNION SELECT 'mdavis','Michael','Davis' UNION SELECT 'rjohns','Richard','Johns'
So each user in a table has unique identifier now.
Query:
select * from tblUsers
Result:
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 @Loginvarchar(50)= '' AS DECLARE@ContextInfo varbinary(128) IF @Login <> '' BEGIN SELECT @ContextInfo = convert(varbinary(128),ID) FROM tblUsers WHERE Login = @Login IF @ContextInfo is NULL SET @ContextInfo = 0 SET CONTEXT_INFO @ContextInfo SELECT @ContextInfo AS ContextInfo, convert(int,@ContextInfo) AS INTContextInfo END ELSE SELECT ''
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'
Result:
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 ( IDint IDENTITY, Namevarchar(50), Quantityint, Pricemoney, CreatedByvarchar(50) DEFAULT dbo.fnGetContextLogin(), ChangedByvarchar(50) DEFAULT dbo.fnGetContextLogin(), Createddatetime DEFAULT getdate(), ChangeddatetimeDEFAULT 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 FOR UPDATE AS 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 WHERE ID = 1 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);
connSQL.Open();
// ***** set current context here
// stored procedure - uspSetContextLogin
SqlCommand cmdSQL = new
SqlCommand("uspSetContextLogin”,connSQL);
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();
cmdSQL.ExecuteNonQuery();
// 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
MCDBA
Data Architect,
Bank of Amercia
