|
只因为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:ForVSProjectsdbcudtdbcudtbinDebugdbcudt.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,
?dbo.Func_sendAuditSQL(name,callTimes) as name,
?--name,
?securitycode
from dbo.vper1;
这样一来实现将审计到的语句写入到文件。
性能：
原表查询：1.457s(十万记录)
透明视图查询：1.691s(十万记录)
如果再作相关的优化，估计还能提高一百毫秒左右。
--缺点，查询字段假如不涉及改造字段，语句将不会被记录到文件。（正好符合要求，实现字段的查询审计）
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
