Audit triggers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Audit triggers

Audit triggers

Rate Topic

Display Mode

Topic Options

Author

Message

Posted Thursday, February 21, 2013 10:19 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, April 26, 2013 11:23 AM
Points: 76, Visits: 188

Dear Experts

How to use triggers in auditing and in which cases

Can any one provide examples

Thanks

Post #1422700

andrecesarr

Posted Monday, March 11, 2013 7:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 6:11 PM
Points: 9, Visits: 40

Imagine that you want to know all inserts, delete's and updates that have been made into an specific table in certain period of time, or because a crash, or because a bad data being processed, so, for these and others situations you can use the triggers to tracking the changes. This can be very helpful to know if there is some malicious user changing things in you database tables.

Have a look at this topic:

http://www.sqlservercentral.com/Forums/Topic1429035-1550-1.aspx

Regards,

André CR

Post #1429259

vikingDBA

Posted Tuesday, March 12, 2013 12:02 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:34 AM
Points: 189, Visits: 864

I have attached examples of how I do them. We use them for accountability, and for troubleshooting ("why is it THIS value???"). I have a mechanism embedded that allows you to turn on and off auditing by table. We use this method for any internally-developed databases. They are used by only a few in each department, so it won't be overwhelming the database to do it this way.

If you are doing hundreds or thousands of transactions a minute, then I would look for a different way of doing it.

Hope it helps (and I hope my attachments attached).

Post #1430022

vikingDBA

Posted Tuesday, March 12, 2013 12:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:34 AM
Points: 189, Visits: 864

Oh well. Here is the code. Sorry for putting it here....

USE master
GO

CREATE DATABASE AuditTest
GO

USE AuditTest
GO

CREATE TABLE Customer
( CustID int IDENTITY NOT NULL
CONSTRAINT PK_Customer_On_CustID PRIMARY KEY CLUSTERED,
CustFName varchar(20) NULL,
CustMName varchar(35) NULL,
CustLName varchar(35) NULL,
CurrRec bit NOT NULL DEFAULT (1),
LCHost varchar(50) NOT NULL DEFAULT HOST_NAME(),
LCUser varchar(50) NOT NULL DEFAULT USER,
LCDate datetime NOT NULL DEFAULT GETDATE()
)
GO

CREATE TABLE CustomerAudit
( CustAuditID int IDENTITY NOT NULL
CONSTRAINT PK_CustomerAudit_On_CustAuditID PRIMARY KEY CLUSTERED,
CustID int NOT NULL,
CustFName varchar(20) NULL,
CustMName varchar(35) NULL,
CustLName varchar(35) NULL,
CurrRec bit NOT NULL DEFAULT (1),
LCHost varchar(50) NOT NULL DEFAULT HOST_NAME(),
LCUser varchar(50) NOT NULL DEFAULT USER,
LCDate datetime NOT NULL DEFAULT GETDATE(),
FinalDelete bit NOT NULL DEFAULT (0)
)
GO
/* ============================================================================================== */
/* = System Tables = */
/* ============================================================================================== */

CREATE TABLE AppErrorLog
( EID int IDENTITY NOT NULL
CONSTRAINT PK_AppErrorLog_On_EID PRIMARY KEY CLUSTERED,
EDate datetime NOT NULL,
EUser varchar(50) NULL,
EMessage varchar(1000) NULL,
ESource varchar(150) NOT NULL,
ENo int NOT NULL DEFAULT 0,
ESeverity int NOT NULL DEFAULT 0,
ELineNo int NOT NULL DEFAULT 0,
EHost varchar(50) NULL
)
GO

/* ========================================================================================== */
CREATE TABLE DBActivityLog
( DBActivityID int IDENTITY NOT NULL
CONSTRAINT PK_DBActivityLog_On_DBActivityID PRIMARY KEY CLUSTERED,
DBASPROC varchar(300) NULL,
DBAMessage varchar(1000) NULL,
DBAAppName varchar(150) NULL DEFAULT APP_NAME(),
DBAHost varchar(50) NULL DEFAULT HOST_NAME(),
DBAUser varchar(50) NULL DEFAULT USER,
DBADate datetime NULL DEFAULT GETDATE()
)
GO

/* ======================================================================================== */
CREATE TABLE MiscValues
( MiscID int NOT NULL
CONSTRAINT PK_MiscValues_On_MiscID PRIMARY KEY CLUSTERED,
MiscDesc varchar(100) NULL,
MiscValue varchar(200) NULL,
LCHost varchar(50) NOT NULL DEFAULT HOST_NAME(),
LCUser varchar(50) NOT NULL DEFAULT USER,
LCDate datetime NOT NULL DEFAULT GETDATE()
)
GO

/* ======================================================================================== */
CREATE TABLE zSys_AuditTable
( AuditTableID int IDENTITY NOT NULL
CONSTRAINT PK_zSys_AuditTable_On_AuditTableID PRIMARY KEY CLUSTERED,
SchemaName varchar(128) NOT NULL,
TableName varchar(128) NOT NULL,
AuditThisTable bit NOT NULL DEFAULT (0),
LCHost varchar(50) NOT NULL DEFAULT HOST_NAME(),
LCUser varchar(50) NOT NULL DEFAULT USER,
LCDate datetime NOT NULL DEFAULT GETDATE()
)
GO

--Load all tables in the zSys_AuditTable table (determines dynamically which ones should be audited).
INSERT INTO zSys_AuditTable
SELECT CONVERT(varchar(128),TABLE_SCHEMA) AS SchemaName, CONVERT(varchar(128),TABLE_NAME) AS TableName, 1 AS AuditThisTable, HOST_NAME() AS LCHost, USER as LCUser, GETDATE() AS LCDate
FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE' AND TABLE_TYPE<>'VIEW' AND TABLE_NAME <> 'zSys_AuditTable' AND (TABLE_NAME NOT LIKE '%Audit')
ORDER BY TABLE_NAME

--Reset these as unaudited.
UPDATE zSys_AuditTable
SET AuditThisTable=0
WHERE SchemaName='dbo' AND TableName IN ('AppErrorLog','DBActivityLog','MiscValues')

INSERT INTO MiscValues VALUES (1,'Group Name','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (2,'Dept Name','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (3,'Addr1','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (4,'Addr2','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (5,'City','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (6,'ST','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (7,'Zip','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (8,'Phone','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (9,'Fax','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (10,'Manager','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (11,'Application Name','AuditExample',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (12,'ApplicationVersion','1.0',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (13,'ApplicationDescription','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (14,'DatabaseVersion','1.0',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (15,'DatabaseDescription','',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (16,'SMTPServerName','mail.mycompany.com',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (17,'DBMail Profile Name','SQL Server Agent Mail Profile',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (18,'ReplyEmailAddr','noreply@myemail.com',HOST_NAME(),USER,GETDATE())
INSERT INTO MiscValues VALUES (19,'LogDBActivity','TRUE',HOST_NAME(),USER,GETDATE()) -- log database activity (running of SPROCs, with their associated parameter values)
INSERT INTO MiscValues VALUES (20,'LogAuditActivity','TRUE',HOST_NAME(),USER,GETDATE()) -- log audit activity (record value changes sent to the Audit table)

CREATE PROCEDURE dbo.usp_Read_Customer_All_Audit_Rec
@custid int,
@spstat int OUTPUT,
@errmsg varchar(200) OUTPUT,
@recn int OUTPUT
AS
DECLARE @numrecs int
DECLARE @mvalue varchar(200) --log db activity
DECLARE @pvalue varchar(1000) --log db activity

DECLARE @dbactivityidadded int --id value of record inserted into DBActivityLog, if turned on
DECLARE @continueproc bit --continue processing

SET NOCOUNT ON

SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok

SET @dbactivityidadded = 0 -- set to 0
SET @continueproc = 1 -- set to ok

BEGIN TRY
--log db activity
SET @pvalue = ''
SET @pvalue = CONVERT(varchar(100),@custid)
SELECT @mvalue=ISNULL(MiscValue,'') FROM MiscValues WHERE MiscDesc = 'LogDBActivity'
if @mvalue = 'TRUE'
BEGIN

INSERT INTO DBActivityLog
VALUES ('dbo.usp_Read_Customer_All_Audit_Rec','SPROC call - Params= ' + @pvalue,APP_NAME(),HOST_NAME(),USER,GETDATE())
SET @dbactivityidadded = scope_identity()

END

if @continueproc = 1 --only continue if everything is ok
BEGIN

SELECT 0 AS CustAuditID,CustID,CustFName,CustMName,CustLName,CurrRec,LCHost,LCUser,LCDate, 0 AS FinalDelete
FROM Customer (NOLOCK)
WHERE CustID=@custid

UNION ALL

SELECT CustAuditID,CustID,CustFName,CustMName,CustLName,CurrRec,LCHost,LCUser,LCDate, FinalDelete
FROM CustomerAudit (NOLOCK)
WHERE CustID=@custid
ORDER BY LCDate DESC

SET @numrecs = @@rowcount

if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record selected'
SET @recn = 0
END

END
else
BEGIN
SET @errmsg = 'Encountered unknown error.'
INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @errmsg, 'dbo.usp_Read_Customer_All_Audit_Rec', 0, 0, 0, HOST_NAME())
END
RETURN @spstat
END TRY
BEGIN CATCH
DECLARE @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@errmessage varchar(1000)
SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@errmessage = ERROR_MESSAGE()

SET @errmsg = CONVERT(varchar(200), @errmessage)
SET @spstat = 0

INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @errmessage, 'dbo.usp_Read_Customer_All_Audit_Rec', @ErrorNo, @Severity, @LineNo, HOST_NAME())
END CATCH
GO

/* ====================================================================================== */
/* Create Trigger for table Customer to handle Last Change and/or Audit */

CREATE TRIGGER dbo.TG_Trigger_On_Table_Customer
ON dbo.Customer
FOR INSERT, UPDATE, DELETE
AS
BEGIN TRY
DECLARE @idval int
DECLARE @CountDel int
DECLARE @CountIn int
DECLARE @logauditactivity bit
DECLARE @mvalue varchar(200)

SELECT @CountDel = COUNT(*) FROM Deleted
SELECT @CountIn = COUNT(*) FROM Inserted

-- Do check to see if need to audit this table
SET @logauditactivity = 0
SELECT @mvalue=ISNULL(MiscValue,'') FROM MiscValues WHERE MiscDesc = 'LogAuditActivity'
if @mvalue = 'TRUE' -- only if even turned on do we check for the individual table
BEGIN
if EXISTS(SELECT * FROM zSys_AuditTable WHERE SchemaName='dbo' AND TableName='Customer' AND AuditThisTable=1)
SET @logauditactivity = 1 -- set to true

else
SET @logauditactivity = 0 -- set to false

END
if @CountDel = 0 and @CountIn >= 1 -- inserted
BEGIN
BEGIN TRANSACTION
UPDATE Customer
SET LCDATE = GETDATE(),
LCUSER = USER,
LCHOST = HOST_NAME()
FROM dbo.Customer s
JOIN inserted i
ON i.CustID = s.CustID
COMMIT TRANSACTION

END

IF @CountDel >= 1 and @CountIn = 0 and @logauditactivity = 1 -- deleted
BEGIN
BEGIN TRANSACTION
INSERT INTO CustomerAudit
SELECT CustID, CustFName, CustMName, CustLName, CurrRec, LCHost, LCUser, LCDate, 0 AS FinalDelete FROM DELETED

INSERT INTO CustomerAudit
SELECT CustID, CustFName, CustMName, CustLName, CurrRec, HOST_NAME() AS LCHost, USER AS LCUser, GETDATE() AS LCDate, 1 AS FinalDelete FROM DELETED
COMMIT TRANSACTION
END

if @CountDel >= 1 and @CountIn >= 1 -- updated
BEGIN
BEGIN TRANSACTION
if @logauditactivity = 1
BEGIN
INSERT INTO CustomerAudit
SELECT CustID, CustFName, CustMName, CustLName, CurrRec, LCHost, LCUser, LCDate, 0 AS FinalDelete FROM DELETED
END

UPDATE Customer
SET LCDATE = GETDATE(),
LCUSER = USER,
LCHOST = HOST_NAME()
FROM dbo.Customer s
JOIN inserted i
ON i.CustID = s.CustID
COMMIT TRANSACTION

END
END TRY
BEGIN CATCH
DECLARE @ErrorNo int,
@Severity int,
@State int,
@LineNo int,
@errmessage varchar(1000)
SELECT @ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@errmessage = ERROR_MESSAGE()

ROLLBACK TRAN

INSERT INTO AppErrorLog
VALUES (GETDATE(), USER, @errmessage, 'TG_Trigger_On_Table_Customer', @ErrorNo, @Severity, @LineNo, HOST_NAME())
END CATCH

GO

/* Note: Run these one at a time to see what it is doing */

INSERT INTO Customer VALUES ('John','Q','Public',1,HOST_NAME(),USER,GETDATE())

SELECT * FROM Customer

UPDATE Customer SET CustLName = 'Smith' WHERE CustID = 1

SELECT * FROM Customer

SELECT * FROM CustomerAudit

UPDATE Customer SET CustLName = 'Johnson' WHERE CustID = 1

SELECT * FROM Customer
SELECT * FROM CustomerAudit

DECLARE @st int
DECLARE @rn int
DECLARE @em varchar(200)
EXEC dbo.usp_Read_Customer_All_Audit_Rec 1,@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT
SELECT @st as Status, @em as ErrorMessage

DELETE FROM Customer WHERE CustID = 1

DECLARE @st int
DECLARE @rn int
DECLARE @em varchar(200)
EXEC dbo.usp_Read_Customer_All_Audit_Rec 1,@spstat=@st OUTPUT,@errmsg=@em OUTPUT,@recn=@rn OUTPUT
SELECT @st as Status, @em as ErrorMessage

SELECT * FROM DBActivityLog
SELECT * FROM AppErrorLog

Post #1430027

« Prev Topic | Next Topic »

Permissions