SQLSERVER数据审计的存储过程

• 下面是整个脚本，在NORTHWIND数据库中可运行,若需要用在其他数据库中，则将相应的表和触发器改动为需要跟踪的表：
• 
  ?/*
  Dynamic Audit Trigger Table and Code
  Paul Nielsen? www.IsNotNull.com
  This sample script adds the dynamic audit trigger to
  Northwind Customers and Products table.
• Version 1.1 - Aug 6,2001
• */
• USE Northwind
• -------------------------------------------------------------
  -- Create the table to store the Audit Trail
• IF Exists (SELECT * FROM sysobjects WHERE NAME = 'Audit')
  ? DROP TABLE Audit
• Go
  CREATE TABLE Audit (
  ? AuditID UNIQUEIDENTIFIER ROWGUIDCOL? NOT NULL
  ??? CONSTRAINT DF_Audit_AuditID DEFAULT (NEWID())
  ??? CONSTRAINT PK_Audit PRIMARY KEY NONCLUSTERED (AuditID),
  ? AuditDate DATETIME NOT NULL,
  ? SysUser VARCHAR(50) NOT NULL,
  ? Application VARCHAR(50) NOT NULL,
  ? TableName VARCHAR(50)NOT NULL,
  ? Operation CHAR(1) NOT NULL,?
  ? PrimaryKey VARCHAR(50) NOT NULL,
  --? RowDescription VARCHAR(50) NULL,
  ? SecondaryRow?VARCHAR(50) NULL,
  ? [Column] VARCHAR(50) NOT NULL,
  ? OldValue VARCHAR(50) NULL,
  ? NewValue VARCHAR(50) NULL
  ?)
• GO
• -------------------------------------------------------------
  -- Create function to simulate the Columns_Updated() value
• IF EXISTS (SELECT *
  ?????????????????? FROM sysobjects
  ?????????????????? WHERE NAME = 'GenColUpdated')
  ?DROP FUNCTION GenColUpdated
  Go
• CREATE FUNCTION dbo.GenColUpdated
  ? (@Col INT,@ColTotal INT)
  RETURNS INT
  AS
  BEGIN
  -- Copyright 2001 Paul Nielsen
  -- This function simulates Columns_Updated()
  DECLARE
  ? @ColByte INT,
  ? @ColTotalByte INT,
  ? @ColBit INT
• ? -- Calculate Byte Positions
  ? SET @ColTotalByte = ?1 + ((@ColTotal-1) /8)
  ? SET @ColByte = 1 + ((@Col-1)/8)
  ? SET @ColBit = @col - ((@colByte-1) * 8)
• ? -- gen Columns_Updated() value for given column position
  ? RETURN
  ??? POWER(2,@colbit + ((@ColTotalByte-@ColByte) * 8)-1)
  END
  go
• -------------------------------------------------------------
  -- Create the Dynamic Audit Stored Procedures
• IF EXISTS (SELECT * FROM SysObjects WHERE NAME = 'pAudit')
  ?DROP PROC pAudit
  Go
• CREATE PROCEDURE pAudit (
  ? @Col_Updated VARBINARY(1028),
  ? @TableName VARCHAR(100),
  ? @PrimaryKey SYSNAME)
  AS
  -- dynamic auto-audit trigger/stored procedure
  -- Copyright 2001 Paul Nielsen
  SET NoCount ON
  DECLARE
  ? @ColTotal INT,
  ? @ColCounter INT,
  ? @ColUpdatedTemp INT,
  ? @ColName SYSNAME,
  ? @BlankString CHAR(1),
  ? @SQLStr NVARCHAR(1000),
  ? @ColNull NVARCHAR(50),
  ? @SysUser NVARCHAR(100),
  ? @ColumnDataType INT,
  ? @IsUpdate BIT,
  ? @tempError INT
• ?SET @SysUser = suser_sname()
  ?SET @BlankString = ''
• -- Initialize Col variables
  SELECT @ColCounter = 0
  SELECT @ColTotal = Count(*)
  ? FROM SysColumns? ?????
  ??? JOIN SysObjects
  ????? ON SysColumns.id = SysObjects.id
  ? WHERE SysObjects.name = @TableName
• -- Set IsUpdated Flag
  IF EXISTS(SELECT * FROM #tempDel)
  ? SELECT @IsUpdate = 1
  ELSE
  ? SELECT @IsUpdate = 0
• -- Column Updates
  WHILE ((SELECT @ColCounter) != @ColTotal)?
  ? -- run through some columns
  ? BEGIN
  ??? SELECT @ColCounter = @ColCounter + 1
  ??? SET @ColUpdatedTemp
  ???????? = dbo.GenColUpdated(@ColCounter,@ColTotal)
• ?? -- bitwise AND between updated bits
  ?? -- and the selected column bit???
  ? IF (@Col_Updated & @ColUpdatedTemp) = @ColUpdatedTemp
  ??? BEGIN
  ????? SET @ColNull = null
  ????? SELECT
  ????????? @ColName = SysColumns.[name],
  ?????????? -- get the column name & Data Type
  ????????? @ColumnDataType = SysColumns.xtype?
  ??????? FROM SysColumns? ?????
  ????????? JOIN SysObjects
  ??????????? ON SysColumns.id = SysObjects.id
  ??????? WHERE SysObjects.[NAME] = @TableName? ????
  ????????? and SysColumns.ColID = @ColCounter
  ????? IF @ColName NOT IN ('Created','Modified')
  ??????? BEGIN
  ????????? -- text columns
  ????????? IF? @ColumnDataType IN
  ????????????? ( 175,239,99,231,35,98,167 )
  ??????????? SET @ColNull =? ''''''???
  ????????? -- numeric +? bit columns
  ????????? ELSE IF? @ColumnDataType IN
  ???????????? (? 106,62,56,60,108,59,52,122,104 )
  ??????????? SET @ColNull = '0'???
  ????????? -- date columns
  ????????? ELSE IF? @ColumnDataType IN ( 61,58 )
  ??????????? SET @ColNull =? '''1/1/1980'''
  ????????? -- uniqueidentifier columns
  ????????? ELSE IF? @ColumnDataType IN ( 36 )
  ??????????? SET @ColNull =? ''''''
  ?????????
  ????????? IF @ColNull IS NOT NULL
  ??????????? BEGIN
  ????????????? IF @IsUpdate = 1
  SET @SQLStr = ???
  ? ' Insert Audit(TableName,PrimaryKey,SysUser,[Column],'
  ?? +' AuditDate,Application,OldValue,NewValue,Operation)'
  ?? +' Select '''+ @TableName + ''',
  ?? #tempIn.['+ @PrimaryKey + '],
  ?? ''' + @SysUser + ''',' +
  ?? '''' + @ColName + ''',GetDate(),App_Name(),' +
  ?? ' IsNull(convert(nvarchar(100),
  ?????? #tempDel.[' + @ColName + ']),''<null>''),
  ?????? #tempIn.[' + @ColName +?? ']),''U''' +
  ?? ' From #tempIn' +
  ?? ' Join #tempDel' +
  ?? ' On #tempIn.['+ @PrimaryKey + ']
  ????? = #tempDel.['+ @PrimaryKey + ']' +
  ?? ' AND isnull(#tempIn.' + @ColName +?? ',' + @ColNull + ')
  ???? != isnull(#tempDel.' + @ColName +?? ',' + @ColNull + ')'
  ?? + ' Where Not (#tempIn.[' + @ColName + '] Is Null
  ???? and #tempDel.[' + @ColName + ']? Is Null)'
  ?
  ELSE -- Insert
  ? SET @SQLStr = ???
  ?' Insert Audit(TableName,'
  ? +' AuditDate,Operation)'
  ? +' Select '''+ @TableName + ''',#tempIn.['+ @PrimaryKey
  ? + '],''' + @SysUser + ''',' +
  ? '''' + @ColName + ''',' +
  ? ' Null,' +
  ? ' IsNull(convert(nvarchar(100),
  ?? #tempIn.[' + @ColName +']),''I''' +
  ? ' From #tempIn' +
  ? ' Where Not (#tempIn.[' + @ColName + '] Is Null)'
• EXEC sp_executesql? @SQLStr
  SET @TempError = @@Error
  IF @TempError <> 0
  ? BEGIN
  ??? -- turn rollback on only if you want a
  ??? -- failure to record audit to cancel
  ??? -- the data modification operation
  ??? -- Rollback
  ??? RAISERROR ('Audit Trail Error',15,1)
  ? END
  END??
  END
  END
  END
  RETURN
  Go
• ------------------------------------------------------------
  ------------------------------------------------------------
  -- sample Table Triggers
  -- this will need to be added to every table
  -- and the Table and Primary Key settings
• -- Products trigger
• IF EXISTS (SELECT *
  ??????????? FROM sysobjects
  ??????????? WHERE NAME = 'Products_Audit')
  ? DROP TRIGGER Products_Audit
  Go
• CREATE TRIGGER Products_Audit
  ON dbo.Products
  AFTER Insert,Update
  NOT FOR REPLICATION
  AS
  -- Dynamic Audit Trail Code Begin
  -- (c)2001 Paul Nielsen
  DECLARE
  ? @Col_Updated VARBINARY(1028),
  ? @PrimaryKey SYSNAME
• SET NoCount ON
• -- Set up the Audit data
  -- set to the table name
  SET @TableName = 'Products'?
  -- set to the column to identify the row
  SET @PrimaryKey = 'ProductID'?
  SET @Col_Updated = Columns_Updated()?
  SELECT * INTO #TempIn FROM Inserted
  SELECT * INTO #TempDel FROM Deleted
• -- call the audit stored procedure
  EXEC pAudit @Col_Updated,@TableName,@PrimaryKey
• Go
  -------------------------------------------------------------
  -- Customer Trigger
• IF EXISTS (SELECT *
  ??????????? FROM SysObjects
  ??????????? WHERE [NAME] = 'Customers_Audit')
  ? DROP TRIGGER Customers_Audit
  Go
• CREATE TRIGGER Customers_Audit
  ON dbo.Customers
  AFTER Insert,Update
  NOT FOR REPLICATION
  AS
  -- Dynamic Audit Trail
  -- (c)2001 Paul Nielsen
  DECLARE
  ? @Col_Updated VARBINARY(1028),
  ? @PrimaryKey SYSNAME
  SET NoCount ON
  SET @TableName = 'Customers'?
  SET @PrimaryKey = 'CustomerID'
  SET @Col_Updated = Columns_Updated()?
  SELECT * INTO #TempIn FROM Inserted
  SELECT * INTO #TempDel FROM Deleted
  EXEC pAudit @Col_Updated,@PrimaryKey
  go
• ----------------------------------------------------
  -- Sample test code
• -- test insert
  INSERT Products (ProductName,CategoryID)
  ? VALUES('Ye Old Audit Trail',1)
  go
• -- test single row updates
  UPDATE Products
  ? SET ProductName = 'Audit Test'
  ? WHERE ProductID = 1
  go
• UPDATE Products
  ? SET ReorderLevel = 3
  ? WHERE ProductID = 1
  go
• -- test multi-row update
  UPDATE Products
  ? SET UnitPrice = UnitPrice * 1.1
  ? WHERE CategoryID = 1
  go
• -- test all columns update
  UPDATE Customers
  ? SET
  ? ContactName = 'Phil Senn',
  ? CompanyName = 'AuditTest',
  ? ContactTitle = 'M.P.',
  ? Address = '123 Audit Ln. Pl. Ave. Dr.',
  ? City = 'Hickory',
  ? Region = 'NC',
  ? PostalCode = '12345',
  ? Country = 'US',
  ? Phone = '555 123-4567',
  ? Fax = '555 123-4568'
  ? WHERE CustomerID = 'ALFKI'
  go
• -- examine raw table
  SELECT * FROM Audit
  go
• -- view Products w/ Audit SELECT Products.ProductName,Audit.* ? FROM Products ??? JOIN Audit ????? ON Audit.PrimaryKey = Products.ProductID ? WHERE Audit.TableName = 'Products'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!