SQL Server数据审计系统：存储过程与触发器实战
|
2025年十月那会儿，我接了个SQL Server数据审计的活儿，需求是给核心业务表加变更追踪——谁改了哪条数据、改前改后值、啥时候改的，全得记下来。领导拍板说用存储过程+触发器实现，我原以为不就是写俩INSERT嘛，结果这俩货差点把我CPU烧穿。 触发器是第一个坑。业务表有三十多个字段，光是AFTER UPDATE触发器就得把OLD和NEW值全捞出来，再拼成JSON存审计表。我按常规写法，声明一堆@变量，逐个字段赋值，结果部署完五分钟，监控就报警了——触发器执行超时，业务事务被锁死。运维老张冲进来喊：“你搞啥呢？生产库卡了！”我盯着屏幕上的行数一直在跳，风扇嗡嗡转得跟直升机似的，心想这触发器怕不是写成死循环了？ 后来一查，问题出在字段类型上。有个TEXT类型的字段，我直接用@var=NEW.TextCol赋值，SQL Server居然偷偷做了隐式转换，把整个TEXT内容转成NVARCHAR(MAX)，耗时直接翻倍。更绝的是，业务方后来又加了个VARCHAR(8000)的备注字段，触发器里没处理，结果更新这条记录时，触发器直接报错，事务回滚，业务系统弹了半小时“操作失败”。开发小李路过问了句“好了没”，我盯着满屏红字，恨不得把键盘吃了。
文章配图，仅供参考 存储过程也没好到哪去。审计数据要按天分区，我写了个动态SQL的存储过程，每天零点自动建新分区表。测试环境跑得挺顺，结果上线第一天，分区表没建成，审计数据全堆到了默认分区，直接撑爆磁盘。后来发现是SQL Agent作业的权限问题——存储过程用了EXECUTE AS 'sa'，但Agent账户没继承权限，动态SQL执行失败也没报错，就那么静默失败了。我查日志查了俩小时，外卖都凉了，最后发现是作业步骤里少了个“WITH LOGON”参数。跟开发吵了一架那回更离谱。他们为了性能，把业务表的更新操作全改成了批量UPDATE，一次改几百条记录。触发器里我用了CURSOR逐条处理审计日志，结果批量更新时，触发器被调用几百次，CPU直接飚到90%。开发小李还振振有词：“谁让你用游标的？活该！”我气得拍桌子：“不用游标怎么拿OLD和NEW值？你行你来写！”最后还是领导拍板，把触发器改成基于TVP（表值参数）的存储过程，批量处理审计日志，这才把CPU压下来。 不过话说回来，这套方案能活下来，还真得感谢“优胜劣汰”。我们试过用CDC（变更数据捕获），结果CDC的维护脚本把系统表锁死了；也试过用变更跟踪，但变更跟踪不记录旧值，审计需求不满足；最后还是触发器+存储过程最“皮实”——虽然性能差，但至少能跑通，出了问题也能定位。上周业务方又提新需求，要记录谁访问了数据（不只是修改），我直接拒绝了：“再加触发器，库真要炸了。”他们现在正琢磨用SQL Server的审计功能，但那玩意儿要企业版，领导还没批预算。 现在回头看，这套方案最坑的地方是维护成本。触发器藏得深，出了问题往往先怪业务代码；存储过程的动态SQL，调试起来比解九连环还难。我原以为把逻辑写死最安全，结果业务一变，这些“死逻辑”就成了定时炸弹。不过话说回来，要不是这些坑，我哪能学会用扩展事件（Extended Events）抓触发器执行计划？哪能知道SET XACT_ABORT ON能避免部分事务问题？ 下一步我打算把触发器里的逻辑拆到应用层，用ORM的拦截器实现审计——至少应用层出错能直接看到堆栈，不用再猜是触发器还是存储过程的问题。但领导担心应用层不可靠，说“触发器是数据库自带的，肯定更稳”。我懒得反驳，等下次触发器再锁库，他自然就懂了。窗外天黑了，我关掉监控页面，心想：这破活儿，谁爱干谁干吧。 （编辑：站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
