只因为Oracle里面有自己的细粒度审计,也想看看SQLServer是否也对于查询进行完全审计。经过一番调研后,下面是详细的实现步骤
1 原表创建:
create table PERFORMANCE_C
(
PID INTEGER not null,
PERSIONID VARCHAR(40),
DATEFIELD DATETIME,
NAME VARCHAR(40),
ACCOUNT VARCHAR(20),
BALANCE DECIMAL(9,2),
SECURITYCODE VARCHAR(60)
);
2 在原有CLR工程基础上,添加文件:AuditSession.cs
关于文件的内容如下:
主要是将审计到的文件写入到文件(C#实现)。
//用于识别查询是否是第一次执行,(Key=@@SPID,Value=@@SPID+RAND())
private static Hashtable g_OldAuditSQLStatus = System.Collections.Hashtable.Synchronized(new System.Collections.Hashtable());
//用于存储当前每个连接对应的当前查询语句
private static Hashtable g_AuditSQLTable = System.Collections.Hashtable.Synchronized(new System.Collections.Hashtable());
//此函数在一次查询中是被多次执行的
public static SqlString SendAuditSql(SqlString colValue,SqlString auditSQL,SqlInt32 spid,SqlDouble randV)
{
if (auditSQL.IsNull)
return colValue;
// DebugLog.WriteLine(randV.Value.ToString());
if (g_AuditSQLTable.Contains(spid))
{
if (g_OldAuditSQLStatus[spid].Equals(randV))
{
if (g_AuditSQLTable[spid].Equals(auditSQL))
{
return colValue;
}
else
{
DebugLog.WriteLine(auditSQL.Value);
g_OldAuditSQLStatus[spid] = randV;
g_AuditSQLTable[spid] = auditSQL;
return colValue;
}
}
else
{
DebugLog.WriteLine(auditSQL.Value);
g_OldAuditSQLStatus[spid] = randV;
g_AuditSQLTable[spid] = auditSQL;
return colValue;
}
}
else
{
DebugLog.WriteLine(auditSQL.Value);
g_AuditSQLTable.Add(spid, auditSQL);
g_OldAuditSQLStatus.Add(spid, randV);
return colValue;
}
}
//写入到文件
private static void WirteFile(string val)
{
string filePath = "c:/storeproc.log";
FileStream fout = new FileStream(filePath, FileMode.Append);
StreamWriter fstr = new StreamWriter(fout);
fstr.WriteLine(val.ToString());
fstr.Close();
fstr = null;
fout = null;
}
3 注册CLR集的SQL语句:
--创建CLR集
CREATE ASSEMBLY DBCUDT
FROM 'D:\ForVSProjects\dbcudt\dbcudt\bin\Debug\dbcudt.dll'
WITH PERMISSION_SET = UNSAFE;
GO
--需要打开数据库的TRUSTWORTHY 及CLR支持打开
--注册里面的相关函数
create FUNCTION dbo.Func_sendAuditSQL
(@colValue NVARCHAR(4000),@auditStr NVARCHAR(4000),@sessionID int,@callTimes float)
RETURNS Nvarchar(4000)
EXTERNAL NAME [DBCUDT].[schina.dbcoffer.dbcudt.AuditSession].[SendAuditSql];
--[your assembly].[space.class].[your function]
GO
4 5 创建获取审计语句的视图
CREATE view [dbo].[getSQL] as
select (
when rand()>0
then (SELECT
cast(text as varchar(max)) as auditSQL FROM
(SELECT * FROM sys.dm_exec_sql_text(
(SELECT most_recent_sql_handle FROM sys.dm_exec_connections AS t
WHERE (session_id = @@SPID))) AS dm_exec_sql_text_1) AS t1)
else NULL end) as auditSQL;
--至于这里为什么要用到rand()及用到case逻辑,去读读SQLServer的不确定函数及预编译相关的内容,你会有不少收获的啦。
--此处主要是保证上面的查询在一次查询过程只执行一次,千万不将这个语法直接嵌到查询中,否则,你晓得的,慢上40倍左右。
5 创建callTimes视图,用于标识当前的查询语句(实则为一个伪列)
CREATE view [dbo].[vperCallTimes] as
select pid,
persionid,
datefield,
name,
account,
balance,
securitycode,
rand() as callTimes
from dbo.performance_c;
--rand()主要是确定为一次查询
6 创建透明视图
CREATE view [dbo].[vper] as
select pid,
dbo.Func_sendAuditSQL(persionid,(select auditSQL from [dbo].[getSQL]),@@spid,callTimes) as persionid,
datefield,
dbo.Func_sendAuditSQL(name,(select auditSQL from [dbo].[getSQL]),@@spid,callTimes) as name,
--name,
account,
balance,
securitycode
from dbo.vper1;
这样一来实现将审计到的语句写入到文件。
性能:
原表查询:1.457s(十万记录)
透明视图查询:1.691s(十万记录)
如果再作相关的优化,估计还能提高一百毫秒左右。
--缺点,查询字段假如不涉及改造字段,语句将不会被记录到文件。(正好符合要求,实现字段的查询审计)