Auditoria para acesso direto a objetos (ignorando acesso indireto)

Quando você está especificando um objeto para auditoria em um esquema específico, ele deve ter um nome de duas partes; no seu caso, se você quiser fazer uma auditoria, [Internal].[machine]a especificação de auditoria do banco de dados seria como abaixo,

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

Se você deseja auditar ambos os objetos, neste caso, tanto para tabela quanto para visualização, seria como abaixo,

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

Resultado dos testes:

• Você verá dois registros de auditoria quando uma visualização for acessada.
• Você verá uma entrada de auditoria quando o objeto da tabela for acessado.

Com exemplo de UI.