In today’s digital world, security is of utmost importance. Many applications require logging changes made to the database in order to track who did what. SQL Server provides a powerful feature called triggers to accomplish this goal. Triggers can be developed for UPDATE, DELETE, and INSERT events, and each time a record is changed, the result of the SUSER_SNAME() function can be stored in a field.
However, there is a challenge when it comes to using a single account to access the database. Many service providers and companies restrict the usage of SQL Server logins for security reasons. So, how can we log changes and stay within the limitations of a single account?
The answer lies in the SET CONTEXT_INFO command in SQL Server 2000. This command allows us to associate up to 128 bytes of binary information with the current session or connection. Each connection can have its own unique value set, making it easy to track changes and retrieve the login name.
Let’s take a look at an example of how to implement this system:
CREATE TABLE tblUsers
(
ID int IDENTITY(1,1),
Login varchar(50) NOT NULL,
FirstName varchar(50) NULL,
LastName varchar(50) NULL
)
INSERT INTO tblUsers (Login, FirstName, LastName)
VALUES ('jsmith', 'John', 'Smith'),
('mdavis', 'Michael', 'Davis'),
('rjohns', 'Richard', 'Johns')
CREATE PROCEDURE uspSetContextLogin
@Login varchar(50) = ''
AS
BEGIN
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 ''
END
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
CREATE TABLE tblProducts
(
ID int IDENTITY,
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()
)
CREATE TRIGGER UTrig_tblProducts ON tblProducts
FOR UPDATE
AS
BEGIN
UPDATE tblProducts
SET ChangedBy = dbo.fnGetContextLogin(),
Changed = GETDATE()
FROM inserted i
WHERE i.ID = tblProducts.ID
END
With this system in place, we can now track changes in the tblProducts table using only a single SQL account to connect to the database. Each time a connection is established, the context should be set to the current login name using the uspSetContextLogin stored procedure. This allows us to retrieve the current login name using the fnGetContextLogin function.
For example, let’s say John Smith logs in to the application with his login name ‘jsmith’. After establishing the connection, we can set the context using the uspSetContextLogin stored procedure:
EXEC uspSetContextLogin 'jsmith'
Now, whenever a new record is created in the tblProducts table, the CreatedBy and ChangedBy fields will be automatically set to the current login name. Similarly, when an update is made, the ChangedBy field will be updated accordingly.
By utilizing the SET CONTEXT_INFO command, we have overcome the limitations of a single account and can effectively track changes in the database. It is important to note that setting the context should only be done for UPDATE and INSERT operations, and not for SELECTs, to minimize any impact on system performance.
In conclusion, the SET CONTEXT_INFO command in SQL Server provides a solution for implementing a single account security audit trail system. By setting the context for each session, we can track changes and retrieve the login name, even when restricted to using a single account.
Thank you for reading!