全Sql語句實作SBO事務日志記錄與查詢

接受一個朋友的委托，希望對SBO的事務日志進行記錄，并且提供查詢分析功能，說實話，行為日志與審計不管對于作業系統、資料庫系統或者是使用者軟體，盡管都是安全考慮所需要的，但是要真正的實作并且通用起來審計，是有難度的。

不過受人之托，還是忠人之事吧，用純Sql方式實作了一個簡單的日志記錄與查詢【姑且算是審計吧】。畢竟運作于SBO之上采用的是沒有經過任何的控制介入的方法，局限太多，但是對于要求不太高的機關，還是夠用了。

第一步 請在Sql server的查詢分析器中運作一下腳本

1.1 建立日志表

if exists(_select* from dbo.sysobjects where id = object_id(N'fsTransLog') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

 drop table fsTransLog

go

create table fsTransLog

(

 AutoKey   int IDENTITY (1, 1) NOT FOR REPLICATION  NOT null,

 ObjectType   nvarchar(25),

 TransType   nvarchar(1),

 KeyNum   int,

 KeyColumns  nvarchar(255),

 KeyValues  nvarchar(255),

 Result   int default 0,

 ErrNotes  nvarchar(255),

 TransUser  int,

 TransTime  datetime

)

1.2 建立事務表單對應表，在此表中定義的事務才進行日志記錄，請務必填寫此表單【表單内容個填寫方式可以參見附件内容】，以確定您的事務日志被記錄，和日志報表資訊的明晰。

if exists(_select* from dbo.sysobjects where id = object_id(N'fsTransTable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

 drop table fsTransTable

create table fsTransTable 

 TransType   nvarchar(25) not null,  --事務類型：13，銷售發票；15，銷售交貨....

 TransName   nvarchar(50),   --事務類型名稱：13，銷售發票；15，銷售交貨....

 TransTable  nvarchar(30),   --事務對應的資料表：銷售發票對應OINV，銷售交貨對應ORIN

 TransKey  nvarchar(255) not null,

 KeyName   nvarchar(1000),

 UserField  nvarchar(30) default 'UserSign',

 IsActive  bit default 0

if exists(_select* from dbo.sysobjects where id = object_id(N'fsTransType') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

 drop table fsTransType

create table fsTransType

 TransType   nvarchar(1) not null,

 TransName   nvarchar(50)

_insertINTO fsTransType(TransType,TransName)VALUES(N'A',N'添加')

_insertINTO fsTransType(TransType,TransName)VALUES(N'U',N'修改')

_insertINTO fsTransType(TransType,TransName)VALUES(N'D',N'删除')

_insertINTO fsTransType(TransType,TransName)VALUES(N'C',N'取消')

_insertINTO fsTransType(TransType,TransName)VALUES(N'L',N'關閉')

1.3 建立儲存事務日志的存儲過程

 如果希望對所有的日志進行記錄，就去掉 if exists(_select...) 那一行語句

if exists (_select* from dbo.sysobjects where id = object_id(N'[dbo].[fsup_WriteTransLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

 drop procedure [dbo].[fsup_WriteTransLog]

create procedure fsup_WriteTransLog

 @ObjectType nvarchar(25),

 @TransType nchar(1),

 @KeyNum int,

 @KeyColumns nvarchar(255),

 @KeyValues nvarchar(255),

 @Result int,

 @ErrNotes  nvarchar(255)

as

begin

 if exists(_select* FROM fsTransTable WHERE Upper(TransType)=Upper(@ObjectType) AND IsActive=1)

 begin

  _declare@table nvarchar(30), @TransKey nvarchar(300), @UserField nvarchar(50), @strSql nvarchar(4000), @UserId int

  CREATE TABLE #TransUser (UserId int)

  _select@table=TransTable, @TransKey=TransKey, @UserField=UserField FROM fsTransTable WHERE Upper(TransType)=Upper(@ObjectType)

  if (IsNull(@UserField,'')<>'')

  begin

   _select@strSql = '_insertINTO #TransUser(UserId) _select' + @UserField + ' FROM ' + @table + ' WHERE ' + @TransKey + '=N''' + @KeyValues + ''''

   __execUTE(@strSql)

   _select@UserId=UserId FROM #TransUser

  end

  _insertINTO fsTransLog(ObjectType,TransType,KeyNum,KeyColumns,KeyValues,Result,ErrNotes,TransTime,TransUser)

   VALUES(@ObjectType,@TransType,@KeyNum,@KeyColumns,@KeyValues,@Result,@ErrNotes,getdate(),@UserId)

 end

end

第二步 在 SBO_SP_TransactionNotification 過程的最後部分的_select@error, @error_message之前，加入以下語句。 對于自定義的中途捕捉到事務錯誤提前傳回的，應該在 return 之前加入以下語句， 沒有這個添加就不會記錄日志

__exec fsup_WriteTransLog @object_type,@transaction_type,@num_of_cols_in_key,@list_of_key_cols_tab_del,@list_of_cols_val_tab_del, @error, @error_message

第三步 事務日志報表，在自定義報表中作如下定義

局限：

--  1、由于SBO的原因，查詢條件顯示的不很友好，都是最後登入時間

--  2、由于SBO的報表局限，無法實作深度關聯查詢

_declare@sd datetime,  @ed datetime, @userid int

_select@sd=min(a.lastLogin) FROM OUSR a WHERE a.LastLogin>='[%0]'

_select@ed=max(b.lastLogin) FROM OUSR b WHERE b.LastLogin<='[%1]'

_select@userid=c.INTERNAL_K FROM OUSR c WHERE c.U_Name<='[%2]'

_select@sd=__cast(Convert(nvarchar(10), IsNull(@sd, '2000-1-1'), 121) as datetime)

_select@ed=__cast(Convert(nvarchar(10), IsNull(@ed, getdate()), 121) + ' 23:59:59' as datetime)

_select@userid=IsNull(@userid,-100)

_selectTransTime 事務時間, TransUser 使用者辨別, User_Code 使用者代碼, U_NAME 使用者名, b.TransType 事務代碼, b.TransTable 事務表單, b.TransName 事務名稱, a.TransType 事務類型, d.TransName 事務類型名稱, KeyColumns 事務辨別, KeyName 事務辨別名稱, KeyValues 事務辨別值, case when Result=0 then N'成功' else N'失敗：'+ErrNotes + N'[' + __cast(Result as nvarchar(20)) +']' end 事務結果

from fsTransLog a left join fsTransTable b on a.ObjectType=b.TransType

left join OUSR c on a.TransUser=c.INTERNAL_K

inner join fsTransType d on a.TransType=d.TransType

執行效果，進入到SBO查詢分析器，執行上述查詢分析代碼，選擇日志時段和日志對象，得到相應的結果如下：

本文轉自foresun  51CTO部落格，原文連結：http://blog.51cto.com/foresun/194633，如需轉載請自行聯系原作者