0

to isolate internal tables from third party database access, we've created schemas specifically for that use case. Third party Logins/DB Users only have access to those "public" Schemas.
The problem is that the user had more permissions than it should have and many third party queries access the internal tables.
Since I can't just remove the permissions abruptly, I'd like to do it bit by bit. Identifying problematic queries and change them accordingly.

For this purpose I tried to use the Sql Server Database Auditing features, but it looks like I'm missing information todo what I need:
Lets say I have the table [Internal].[Machine] and the View [Public].[Machine] which for simplicity is just select * from [Internal].[Machine]. The user viewer only has permission to select from [Public].[Machine].

Adding a "Database Audit Specification" for (Type=SELECT, ObjectClass=SCHEMA, ObjectName=Internal, PrincipalName=viewer), produces an entry for select * from [Internal].[Machine] and select * from [Public].[Machine].
My goal is to only have an entry for select * from [Internal].[Machine], i.e. when the object is directly accessed by the query statement. And as far as I've seen the produced rows do not contain the information necessary to differentiate between the two.

Is this somehow possible with the Sql Server Audit/Extended Events feature (or something else altogether)?

2
  • Have you tried filtering Extended Events by object ID? I think that works. I'll put together a proper answer if I get a chance.
    – J. Mini
    Commented Feb 4 at 21:55
  • None of the Events I tried did what I wanted, but I only have used Extended Events very superficially, so I could have easily missed something. An example would be great.
    – Arokh
    Commented Feb 7 at 4:10

1 Answer 1

1

When you are specifying object for audit on specific schema it has to be two part name, in your case if you want to have audit [Internal].[machine] then the database audit specification would be like below,

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

If you want to audit for both objects, in this case for both table and view, then it would be as below,

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

Test results:

  • You will see two audit records when a view is accessed.
  • You will see one audit entry when table object is accessed.

enter image description here

With UI example.enter image description here

6
  • I just tried your example, either I did something wrong or it doesn't work as I need it to: Using your first example, select * from [Public].[Machine] will also add an entry to the audit logs, which I don't want, since it is a legitimate way to access that data. I only want to log it if it is accessed by using the table object directly, i.e. select * from [Internal].[Machine].
    – Arokh
    Commented Feb 7 at 4:15
  • If you want to isolate the physical object access then you would need to use second approach, updated details in my post. With that when a view is accessed you will see two entries, whereas for the direct table access you will see single entry.
    – S.D.
    Commented Feb 7 at 7:11
  • Mhh, wouldn't work for more complex queries/views as there could be a mixture of direct/indirect access, but my specific use case is simple enough that it might work. If there aren't any other suggestions in the next few days, I'll choose this as the answer.
    – Arokh
    Commented Feb 7 at 9:14
  • @Arokh Ack, you could still find the direct access accounts with a multiple objects defined in your view. Updated my second script, check the last query to filter out such entries.
    – S.D.
    Commented Feb 7 at 18:37
  • It would still not work perfectly. For example the Query select * from [Public].[Machine] a inner join [Internal].[Machine] b on a.id = b.id would not show up in your query. But for my case your approach is close enough.
    – Arokh
    Commented Feb 9 at 23:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.