无论如何要为“ SELECT”创建SQL Server DDL触发器。陈述？ [英] Anyway to create a SQL Server DDL trigger for "SELECT" statements?

问题描述

我正在处理一些敏感的Accounting表，并且我想审核对该表执行的任何 SELECT 语句或与它们相关的任何视图。

I am dealing with some sensitive Accounting tables and I would like to audit any SELECT statement executed on the table or any views associated with them.

我没有找到任何 DDL事件在BOL（在线图书）上与 SELECT 语句有关。
和DML触发器分别用于 INSERT ， UPDATE 和 DELETE

I did not find any DDL Events on BOL (Books Online) that had anything to do with SELECT statement. And DML triggers are for INSERT, UPDATE and DELETE only.

是否可以通过 SELECT 语句记录谁访问表和视图？

Is it possible to log who accesses table and views through SELECT statement?

推荐答案

您有3种选择：

• 允许如果要登录（并删除表权限），则通过存储过程进行访问


• 如果要限制并保持直接访问，请在视图后隐藏表


• 运行永久跟踪

我会选择选项1或2，因为它们是您应用程序的一部分并且自包含

I'd go for options 1 or 2 because they are part of your application and self contained.

尽管，开始记录确实有点晚：应该先限制对表的访问。

Although, this does sound a bit late to start logging: access to the table should have been restricted up front.

此外，如果最终用户未直接进行更正（例如通过Web服务器或服务帐户），则任何解决方案都将失败。除非您使用存储的过程来发送最终用户名...

Also, any solution fails if end users do not correct directly (eg via web server or service account). Unless you use stored procs to send in the end user name...

查看示例：

CREATE VIEW dbo.MyTableMask
AS
SELECT *
FROM
    MyTable
    CROSS JOIN
    (SELECT 1 FROM SecurityList WHERE name = SUSER_SNAME())
--WHERE could use NOT EXISTS too with table
GO

这篇关于无论如何要为“ SELECT”创建SQL Server DDL触发器。陈述？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！