主页 / dba / 问题 / 335507
Accepted
Arokh
Asked: 2024-02-05 03:57:05 +0800 CST

审计直接对象访问（跳过间接访问）

  • 772

为了将内部表与第三方数据库访问隔离，我们专门针对该用例创建了模式。第三方登录/数据库用户只能访问那些“公共”模式。
问题在于用户拥有的权限超出了应有的权限，并且许多第三方查询访问内部表。
由于我不能突然删除权限，所以我想一点一点地删除。识别有问题的查询并相应地更改它们。

为此，我尝试使用 Sql Server 数据库审核功能，但看起来我缺少执行所需操作的信息：
可以说我有表[Internal].[Machine]和视图[Public].[Machine]，为简单起见，它只是select * from [Internal].[Machine]. 用户viewer仅具有选择的权限[Public].[Machine]。

select * from [Internal].[Machine]为 (Type=SELECT、ObjectClass=SCHEMA、ObjectName=Internal、PrincipalName=viewer) 添加“数据库审核规范”，会生成和 的条目select * from [Public].[Machine]。
我的目标是只有一个条目select * from [Internal].[Machine]，即当查询语句直接访问对象时。据我所知，生成的行不包含区分两者所需的信息。

这是否可以通过 Sql Server 审核/扩展事件功能（或其他功能）实现？

sql-server

1 个回答

  1. Best Answer

    当您在特定模式上指定要审核的对象时，它必须是由两部分组成的名称，在您的情况下，如果您想要进行审核，[Internal].[machine]那么数据库审核规范将如下所示，

    USE [master]
GO

/****** Object:  Audit [AuditView]    Script Date: 2/6/2024 10:30:30 PM ******/
CREATE SERVER AUDIT [AuditObject]
TO FILE 
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\'
    ,MAXSIZE = 10 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000)
ALTER SERVER AUDIT [AuditObject] WITH (STATE = ON)
GO


USE [db01]
GO

CREATE DATABASE AUDIT SPECIFICATION [AuditObject]
FOR SERVER AUDIT [AuditObject]
ADD (DELETE ON OBJECT::[Internal].[machine] BY [public]),
ADD (INSERT ON OBJECT::[Internal].[machine] BY [public]),
ADD (SELECT ON OBJECT::[Internal].[machine] BY [public]),
ADD (UPDATE ON OBJECT::[Internal].[machine] BY [public])
WITH (STATE = ON)
GO

    如果您想审计两个对象，在本例中同时审计表和视图，则如下所示，

    USE master
GO

CREATE SERVER AUDIT [AuditViewAndObject]
TO FILE 
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\'
    ,MAXSIZE = 10 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000)
ALTER SERVER AUDIT [AuditViewAndObject] WITH (STATE = ON)
GO

USE db01
GO
CREATE DATABASE AUDIT SPECIFICATION [AuditViewAndObject]
FOR SERVER AUDIT [AuditViewAndObject]
ADD (DELETE ON OBJECT::[Pub].[Machine] BY [public]),
ADD (INSERT ON OBJECT::[Pub].[Machine] BY [public]),
ADD (SELECT ON OBJECT::[Pub].[Machine] BY [public]),
ADD (UPDATE ON OBJECT::[Pub].[Machine] BY [public]),
ADD (DELETE ON OBJECT::[Internal].[machine] BY [public]),
ADD (INSERT ON OBJECT::[Internal].[machine] BY [public]),
ADD (SELECT ON OBJECT::[Internal].[machine] BY [public]),
ADD (UPDATE ON OBJECT::[Internal].[machine] BY [public])
WITH (STATE = ON)
GO

--Testing Query
EXECUTE AS LOGIN = 'Viewer'
GO
SELECT * FROM pub.machine
GO
SELECT TOP 1 * FROM internal.machine
GO
REVERT
GO
SELECT [event_time],
       database_name,
       [schema_name],
       [object_name],
       statement,
       database_principal_name,
       m.class_type_desc
FROM sys.fn_get_audit_file(
                              REPLACE(
                                         CAST(SERVERPROPERTY('ErrorLogFileName') AS VARCHAR(256)),
                                         'ERRORLOG',
                                         'AuditViewAndObject*'
                                     ),
                              DEFAULT,
                              DEFAULT
                          ) a
    JOIN sys.dm_audit_class_type_map m
        ON a.class_type = m.class_type
ORDER BY a.event_time;

--To find direct access audit entries
;WITH cte AS(
SELECT [event_time],
       database_name,
       [schema_name],
       [object_name],
       statement,
       database_principal_name,
       m.class_type_desc
FROM sys.fn_get_audit_file(
                              REPLACE(
                                         CAST(SERVERPROPERTY('ErrorLogFileName') AS VARCHAR(256)),
                                         'ERRORLOG',
                                         'AuditViewAndObject*'
                                     ),
                              DEFAULT,
                              DEFAULT
                          ) a
    JOIN sys.dm_audit_class_type_map m
        ON a.class_type = m.class_type
)
SELECT   CAST(cte.event_time AS VARCHAR(19)) AS EventTime, cte.object_name,cte.database_principal_name,cte.object_name, COUNT(1) AS Count
FROM cte
WHERE cte.object_name <> ''
GROUP BY CAST(cte.event_time AS VARCHAR(19)),  cte.object_name,cte.database_principal_name
--HAVING COUNT(1) = 1 --Filter for direct access records

    检测结果：

    • 访问视图时您将看到两条审核记录。
    • 当访问表对象时，您将看到一个审计条目。

    在此输入图像描述

    以用户界面为例。在此输入图像描述

    • 1

相关问题