【数据库】通过触发器实现审计日志记录-应用篇

PostgreSQL触发器，日志审计小神器

 

最近遇到的项目问题，审计日志记录不够详细，比如某用户编辑了某台设备，只记录了用户操作的设备名、操作时间、登录用户和登录IP，至于设备其他属性编辑前和编辑后的信息就没有更详细的对比了，审计粒度不够细，显然是不能让客户满意的，秉承客户满意优先原则，只好技术加持一波了。

 

实际客户想要记录的更多，涉及的业务属性比较广，返本溯源，我们决定在数据库层面解决，以期能最少的改动业务代码。

利用PostgreSql触发器在源表数据发生变化的时候准备一张对应表进行备份记录，把变化数据就像提交svn一样插入到历史表中，如图

 

 

解决方案有了，但其实还存在一个问题触发器只在数据库内部运行，如何记录是当前是哪个登录用户对数据源进行的变更还是个问题，经过一位大佬的指导，可以利用PostgreSql的会话变量解决，简单的测试代码如下

 

 

 

 

打印结果

 

 

 

这样就可以利用数据库会话变量的特性，在每一次的数据库变更前，先将全局操作pid关联到用户uid为一张表，而变更的时候，将将全局操作pid插入到历史数据表中，这样通过唯一的全局操作pid就可以关联查询出是哪个用户的操作了。

于是解决方案图变为

 

 

 

开心的代码实现

 

创建审计主表

 

 

 

 

 

创建审计历史表

 

 

 

 

 

 

创建用户id和操作id关联表

 

 

 

 

在用户进行数据操作变更的postgresql连接时，我们都先将数据库全局变量插入到operation_log表中，将当前登录的用户id与这次变更操作绑定在一起，这个可以封装在web框架层实现。

 

 

创建触发器

 

触发器函数定义了触发器被触发后执行的操作，下面列一下触发器函数中可以使用的变量，例子中的 OLD，就表示触发操作的旧数据行，详细如下：

l NEW:INSERT、UPDATE 操作触发的行级触发器中存储的新的数据行,

l OLD:INSERT、UPDATE 操作触发的行级触发器中存储的的数据行,

l TG_OP:代表当前触发器监听到的操作类型，数据类型为 text，内容为 INSERT、UPDATE、DELETE、TRUNCATE。

 

 

 

 

小结

本文只是大概如何利用PostgreSql触发器对用户行为进行详细日志审计，旨在提供一个触发器记录数据源变更和web框架结合的思路，希望对你有所帮助，如果你有更好的解决方案可以留言告诉我哦

 

PS：如果文章对你有价值，欢迎点个推荐。