无论如何，为“SELECT"创建一个 SQL Server DDL 触发器；声明? [英] Anyway to create a SQL Server DDL trigger for "SELECT" statements?

问题描述

我正在处理一些敏感的会计表，我想审计在该表上执行的任何 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.

我在 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.

此外，如果最终用户没有直接纠正(例如通过网络服务器或服务帐户)，任何解决方案都会失败.除非您使用存储过程来发送最终用户名...

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屋！