Background
Last couple of days I had issues while trying to implement the
audit in one of the asp.net application. Especially if tables don’t
have the structure to accommodate who modified or when modified fields. So what
will do? Is it practical to add all this fields in tables and implement
auditing? Yes if you have 5 or 10 tables. What happens if there are 100’s of
tables?
I came across this issue. Fortunately some one told about the
ContextInfo. Before starting a database connection set the context info with the
current user details. And when the operation finishes call a trigger and update
the auditing table with contextInfo.
What time context info update?
For all db operation a connection need to open. So conextinfo can be set after the open connection.
create an OpenConnection()
method in the base class which all Execute…() methods call, instead of calling
cmd.Connection.Open() directly. Then call SetConext method there.
Implementation
1. OpenConnection
internal override void OpenConnection(IDbConnection con)
{
con.Open();
//For
Auditing purpose
SetContext(con as SqlConnection);
}
2. SetContext
protected virtual void SetContext(IDbConnection conn)
{
string currentUserName = GetCurrentUserName();
string spName = "sp_set_context";
if (conn != null)
{
if (conn.State != ConnectionState.Open)
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = "@username";
param.DbType = DbType.String;
param.Size = 255;
param.Value = currentUserName;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
}
3. sp_set_current_context
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_set_context]
@username nvarchar(256)
AS
BEGIN
-- SET NOCOUNT ON added to prevent
extra result sets from
-- interfering with SELECT
statements.
SET NOCOUNT
ON;
declare
@Ctx varbinary(128)
select @Ctx = convert(varbinary(128), @username)
set context_info
@Ctx
END
GO
4.Function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[f_get_current_user]()
RETURNS uniqueIdentifier
AS
BEGIN
DECLARE @Username nvarchar(256)
DECLARE @UserId uniqueIdentifier
DECLARE @Ctx varbinary(128)
SELECT @Ctx = CONTEXT_INFO()
SELECT @Username = CAST( @Ctx AS nvarchar(256))
IF (@Username is null or @Username = '') select @Username = SYSTEM_USER
SELECT @UserId=UserId from Users where UserName=@Username;
RETURN @UserId
END
5.Trigger
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TR_audit_log]
ON [dbo].[TableName]
FOR INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT
ON;
IF TRIGGER_NESTLEVEL(OBJECT_ID('TR_audit_log')) > 1 RETURN
DECLARE @user_key uniqueidentifier, @tp
INT = 0
IF EXISTS(SELECT 1 FROM deleted) SET @tp = @tp + 1
IF EXISTS(SELECT 1 FROM
inserted) SET
@tp = @tp + 2
DECLARE @i TABLE (Id int, Name nvarchar(100), audit_data VARCHAR(MAX),
PRIMARY KEY (Id))
DECLARE @d TABLE (Id int, Name nvarchar(100), audit_data VARCHAR(MAX),
PRIMARY KEY (Id))
INSERT INTO
@i SELECT Id, Name,
(SELECT Id, Name
FOR XML RAW('audit'))
FROM inserted
INSERT INTO
@d SELECT Id, Name,
(SELECT Id, Name
FOR XML RAW('audit'))
FROM deleted
IF @tp =
2
BEGIN
INSERT INTO audit_log ()
END ELSE
IF @tp = 1
BEGIN
INSERT INTO audit_log ()
END ELSE
BEGIN
INSERT INTO audit_log (
)
END
SET NOCOUNT
OFF;
END
GO
nice
ReplyDeleteNice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for the informative post. download WebSite Auditor
ReplyDelete