当前位置：文江博客话题详情

无论如何，为“SELECT”创建 SQL Server DDL 触发器声明？

发布于 2024-08-06 03:16:49 字数 316 浏览 7 评论 0原文

我正在处理一些敏感的会计表，我想审核在表上执行的任何 SELECT 语句或与其关联的任何视图。

我在 BOL（联机丛书）上没有找到任何 DDL 事件与 SELECT 语句有关的任何事情。 DML 触发器仅适用于 INSERT、UPDATE 和 DELETE。

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

独﹏钓一江月 2024-08-13 03:16:49

是的，可以通过在事件通知上创建="http://msdn.microsoft.com/en-us/library/ms179332(SQL.90).aspx" rel="noreferrer">AUDIT_DATABASE_OBJECT_ACCESS_EVENT 事件。然而，做这样的事情的成本将是巨大的。

最好使用审核基础架构，或使用自定义访问包装器正如 gbn 所建议的那样。

回复收藏 0 原文

赢得她心 2024-08-13 03:16:49

您有 3 个选项：

如果您想记录（并删除表权限），则允许通过存储过程进行访问
如果您想限制并保持“直接”访问，则将表隐藏在视图后面
运行永久跟踪

我会选择选项 1 或2 因为它们是您的应用程序的一部分并且是独立的。

尽管如此，这听起来确实有点晚了开始记录：对表的访问应该预先受到限制。

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

查看示例：

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

You have 3 options:

allow access via stored procedures if you want to log (and remove table rights)
hide the table behind a view if you want to restrict and keep "direct" access
run a permanent trace

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

View example:

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

回复收藏 0 原文

金橙橙 2024-08-13 03:16:49

    --In the master database create a server audit
USE master
GO
CREATE SERVER AUDIT [Audit_Select_HumanResources_Employee]
TO FILE
(     FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup'
      ,MAXSIZE = 0 MB
      ,MAX_ROLLOVER_FILES = 2147483647
      ,RESERVE_DISK_SPACE = OFF)
WITH
(QUEUE_DELAY = 1000, state=  on)

ALTER SERVER AUDIT Audit_Select_HumanResources_Employee 
WITH (STATE = ON) ;
GO
--In the database to monitor create a database audit
USE [AdventureWorks2012]
go

CREATE DATABASE AUDIT SPECIFICATION [Database-Audit]
FOR SERVER AUDIT [Audit_Select_HumanResources_Employee]
--In this example, we are monitoring the humanResources.employee
ADD (SELECT ON OBJECT::[HumanResources].[Employee] BY [dbo])
with (state=on)

--Now you can see the activity in the audit file created
SELECT * FROM sys.fn_get_audit_file ('c:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Audit_Select_HumanResources_Employee.sqlaudit',default,default);
GO

我刚刚为你添加了一些代码。该代码创建服务器审核、针对选定活动的数据库审核，最后使用 sys.fn_get_audit_file 从文件中检索信息。您必须为每个表单独执行此操作。如果您想要更自动化的查询，您可以使用其他工具，例如 Apex SQL Audit 或您喜欢的其他第三方工具。

    --In the master database create a server audit
USE master
GO
CREATE SERVER AUDIT [Audit_Select_HumanResources_Employee]
TO FILE
(     FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup'
      ,MAXSIZE = 0 MB
      ,MAX_ROLLOVER_FILES = 2147483647
      ,RESERVE_DISK_SPACE = OFF)
WITH
(QUEUE_DELAY = 1000, state=  on)

ALTER SERVER AUDIT Audit_Select_HumanResources_Employee 
WITH (STATE = ON) ;
GO
--In the database to monitor create a database audit
USE [AdventureWorks2012]
go

CREATE DATABASE AUDIT SPECIFICATION [Database-Audit]
FOR SERVER AUDIT [Audit_Select_HumanResources_Employee]
--In this example, we are monitoring the humanResources.employee
ADD (SELECT ON OBJECT::[HumanResources].[Employee] BY [dbo])
with (state=on)

--Now you can see the activity in the audit file created
SELECT * FROM sys.fn_get_audit_file ('c:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Audit_Select_HumanResources_Employee.sqlaudit',default,default);
GO

I just added some code for you. The code creates a server audit, a database audit for select activities and finally the sys.fn_get_audit_file is used to retrieve the information from the file. You have to do that individually for each table. If you want a more automated query, you can use other tools like Apex SQL Audit or other third party tool of your preference.

回复收藏 0 原文

救星 2024-08-13 03:16:49

SQL Server 2008 审核也许能够捕获它。除此之外，Profiler/Tracing 是 SQL Server 中唯一可以做到这一点的东西。

回复收藏 0 原文

我们只是彼此的过ke 2024-08-13 03:16:49

编辑：使用 SQL Server Profiler 查看和分析跟踪

回复收藏 0 原文

吹泡泡o 2024-08-13 03:16:49

CREATE PROCEDURE sp_Product_Select @User_Name VarChar(128), @ID Int AS
INSERT INTO My_Trace_Table (Table_Name, User_Name, Table_ID, Select_DateTime)
VALUES ('Products', @User_Name, @ID, GetDate())

SELECT *
FROM Products
WHERE ID = @ID
RETURN
GO

CREATE PROCEDURE sp_Product_Select @User_Name VarChar(128), @ID Int AS
INSERT INTO My_Trace_Table (Table_Name, User_Name, Table_ID, Select_DateTime)
VALUES ('Products', @User_Name, @ID, GetDate())

SELECT *
FROM Products
WHERE ID = @ID
RETURN
GO

回复收藏 0 原文

~没有更多了~