April 6, 2014 at 3:21 pm
Thank you very much for the code. I just didn't understood one thing.
My application name is GT.
I would like to audit everything that is NOT made by this applications.
For exemple:
If someone runs a script against the table, then that is not made by the applications. I would like to save into the audittable
If someone changes the data of the table directely on SQL, then I would like also to save this information into the audit table.
I don't need to audit anything if, the changes are made through the application GT.
In your code I could not see anything saying that only data not inserted, updated or deleted through the GT should be audited (inserted into the auditing table).
Thank you.
April 6, 2014 at 3:33 pm
And what others have been saying is that it would be easy to spoof the audit regarding who made the change. Through the connection string it would not be difficult to make SSMS look like any changes it made were made by your application GT.
April 6, 2014 at 3:36 pm
I understand. I will need to think about that in the future.
For now, I would prefer to understand how can I do this.
April 6, 2014 at 3:45 pm
Start by looking at the system functions you can use in a query to capture some of the information you require. Not everything you need is in the table(s) you audit.
April 6, 2014 at 5:12 pm
So if the original login name (Origininal_Login()) is the name of the app, just do a return in the trigger.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2014 at 6:16 pm
Or do you use APP_NAME()?
May 7, 2014 at 9:30 pm
J Livingston SQL (4/6/2014)
/*possible proof of concept when applied to wide tables*/
/*response to Jeff's comments
"Also, because you have to make a copy of both the INSERTED and DELETED logical trigger tables,
this could end up being quite the chore for SQL Server on wide tables.
I went through this at work with a similar trigger written as a CLR.
It took 4 minutes to update 4 columns for just 10,000 rows on a wide (I didn't design it) 137 column table.
>>>JLS reply
same code with 120 wide column table...albeit majority are INTS
100 000 row table
6 column updates
please run code and review stats
*/
/*start in a safe place !!!!*/
use [tempdb]
GO
IF OBJECT_ID('tempdb..TransData', 'U') IS NOT NULL
DROP TABLE tempdb..TransData;
IF OBJECT_ID('tempdb..Audit', 'U') IS NOT NULL
DROP TABLE tempdb..Audit;
/* create the audit table.
There will only need to be one of these in a database
will be updated from any table with the trigger below*/
CREATE TABLE Audit (
Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
ColumnName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate DATETIME,
UserName VARCHAR(128),
AppName VARCHAR(128)
)
GO
/*create some test data*/
SELECT TOP 100000
TranID = IDENTITY(INT, 1, 1),
CustomerID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
Trans_type = CHAR(Abs(Checksum(Newid())) % 2 + 65)
+ CHAR(Abs(Checksum(Newid())) % 3 + 65)
+ CHAR(Abs(Checksum(Newid())) % 7 + 65),
ProdID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol1 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol2 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol3 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol4 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol5 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol6 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol7 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol8 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol9 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol10 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol11 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol12 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol13 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol14 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol15 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol16 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol17 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol18 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol19 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol20 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol21 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol22 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol23 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol24 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol25 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol26 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol27 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol28 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol29 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol30 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol31 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol32 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol33 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol34 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol35 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol36 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol37 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol38 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol39 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol40 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol41 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol42 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol43 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol44 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol45 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol46 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol47 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol48 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol49 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol50 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol51 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol52 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol53 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol54 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol55 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol56 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol57 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol58 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol59 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol60 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol61 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol62 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol63 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol64 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol65 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol67 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol68 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol69 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol70 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol71 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol72 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol73 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol74 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol75 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol76 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol77 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol78 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol79 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol80 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol81 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol82 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol83 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol84 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol85 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol86 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol87 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol88 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol89 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol90 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol91 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol92 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol93 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol94 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol95 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol96 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol97 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol98 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol99 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol100 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol101 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol102 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol103 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol104 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol105 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol106 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol107 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol108 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol109 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol110 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol111 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol112 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol113 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol114 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol115 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol116 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol117 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol118 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol119 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol120 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
Sales_Amount= CAST(Rand(Checksum(Newid())) * 99 + 1 AS DECIMAL(5, 2)),
Trans_Date = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2011', '2012'), '2011')
INTO TransData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
/*note that for this audit trigger to work there must be a primary key on the table*/
ALTER TABLE [dbo].[TransData] ADD CONSTRAINT [PK_TransData] PRIMARY KEY CLUSTERED ([TranID] ASC)
GO
/*create the trigger . This has to be created on every table you want to monitor
*/
CREATE TRIGGER [dbo].[transdata_Audit]
ON [dbo].[TransData]
FOR
--INSERT, ---uncomment if required
--DELETE, ---uncomment if required
UPDATE
AS
DECLARE @bit INT
, @field INT
, @maxfield INT
, @char INT
, @ColumnName VARCHAR(128)
, @TableName VARCHAR(128)
, @PKCols VARCHAR(1000)
, @sql VARCHAR(2000)
, @UpdateDate VARCHAR(21)
, @UserName VARCHAR(128)
, @Type CHAR(1)
, @PKSelect VARCHAR(1000)
, @PKField VARCHAR(1000)
, @AppName VARCHAR(128)
/*IMPORTANT You will need to change @TableName to match the table to be audited*/
SELECT @TableName = 'transdata'
/* date - user - application*/
SELECT @UserName = SYSTEM_USER
, @AppName = APP_NAME()
, @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
/* Action*/
IF EXISTS ( SELECT *
FROM inserted )
IF EXISTS ( SELECT *
FROM deleted )
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
/* get list of columns*/
SELECT *
INTO #ins
FROM inserted
SELECT *
INTO #del
FROM deleted
/* Get primary key columns for full outer join*/
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.'
+ c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
/* Get primary key columns ---jls*/
SELECT @PKField = COALESCE(@PKField + '+', '') + ''''
+ '''+convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))+'''''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR ( 'no PK on table %s', 16, - 1, @TableName )
RETURN
END
SELECT @field = 0
, @maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = ( @field - 1 ) % 8 + 1
SELECT @bit = POWER(2, @bit - 1)
SELECT @char = ( ( @field - 1 ) / 8 ) + 1
IF SUBSTRING(COLUMNS_UPDATED(), @char, 1) & @bit > 0
OR @Type IN ( 'I', 'D' )
BEGIN
SELECT @ColumnName = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
SELECT @sql = '
insert Audit (
Type,
TableName,
PK,
ColumnName,
OldValue,
NewValue,
UpdateDate,
UserName,
Appname)
select ''' + @Type + ''',''' + @TableName + ''',' + @PKField + ','''
+ @ColumnName + '''' + ',convert(varchar(1000),d.'
+ @ColumnName + ')' + ',convert(varchar(1000),i.'
+ @ColumnName + ')' + ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''',''' + @Appname + ''''
+ ' from #ins i full outer join #del d' + @PKCols
+ ' where i.' + @ColumnName + ' <> d.' + @ColumnName
+ ' or (i.' + @ColumnName + ' is null and d.'
+ @ColumnName + ' is not null)' + ' or (i.'
+ @ColumnName + ' is not null and d.' + @ColumnName
+ ' is null)'
EXEC ( @sql )
END
END
/*trigger end*/
GO
/*=======================================================*/
/* do some updates*/
/*=======================================================*/
UPDATE TransData
SET Trans_type = 'jls',
WideCol118 = 100,
WideCol45 = 32,
WideCol13 = 1800,
WideCol18 = 258888,
WideCol49 = 27
WHERE (TranID % 1000 = 0)
SELECT * FROM Audit
order by tableName,cast(PK AS INT),ColumnName;
/*TRUNCATE TABLE Audit;*/
Sorry for the month old reply, Graham. That's a nice test setup. Did you get any performance results?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2014 at 5:35 am
Hi Jeff
I had forgotten about this...thought I had posted some results, but seems not....so
/* do some updates*/
/*create temptable to store start and end times*/
CREATE TABLE #Results (
Comment VARCHAR(20)
, StartTime DATETIME
, EndTime DATETIME
, Duration DATETIME
)
GO
/*assuming trigger is enabled to begin with
run 100 iterations of update with trigger on
then disable trigger and repeat
takes around 30 secs on my poor old laptop*/
SET NOCOUNT ON
DECLARE @StartTime DATETIME = getdate()
DECLARE @EndTime DATETIME
UPDATE TransData
SET Trans_type = CHAR(Abs(Checksum(Newid())) % 2 + 65) + CHAR(Abs(Checksum(Newid())) % 3 + 65
) + CHAR(Abs(Checksum(Newid())) % 7 + 65)
, WideCol118 = WideCol118 + 20
, WideCol45 = WideCol45 - 20
, WideCol13 = WideCol13 + 50
, WideCol18 = WideCol18 + 100
, WideCol49 = WideCol49 - 100
WHERE (TranID % 1000 = 0)
SELECT @EndTime = getdate()
INSERT #Results
SELECT 'with_Audit', @StartTime, @EndTime ,@EndTime-@StartTime
GO 100
DISABLE TRIGGER transdata_Audit ON TransData
GO
DECLARE @StartTime datetime = getdate()
DECLARE @EndTime datetime
UPDATE TransData
SET Trans_type = CHAR(Abs(Checksum(Newid())) % 2 + 65) + CHAR(Abs(Checksum(Newid())) % 3 + 65
) + CHAR(Abs(Checksum(Newid())) % 7 + 65)
, WideCol118 = WideCol118 + 20
, WideCol45 = WideCol45 - 20
, WideCol13 = WideCol13 + 50
, WideCol18 = WideCol18 + 100
, WideCol49 = WideCol49 - 100
WHERE (TranID % 1000 = 0)
SELECT @EndTime = getdate()
INSERT #Results
SELECT'without_Audit', @StartTime, @EndTime ,@EndTime-@StartTime
GO 100
/*re enable trigger for further runs*/
ENABLE TRIGGER transdata_Audit ON TransData
GO
SET NOCOUNT OFF
average the results....
/*get average results */
SELECT comment
, AVG(datepart(ms, duration)) AS ms
FROM #Results
GROUP BY comment
results on by 32bit bit laptop with 3gb RAM (32bit) SQL 2012 developer
comment ms
-------------------- -----------
with_Audit 238
without_Audit 10
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply