POST_START

Investigating User Activity Against a Production Table with Audit Logs

I recently needed to investigate user activity against a production table in our data warehouse. The table in question was production.sales.orders, and I wanted to understand who had accessed it, what actions they performed, and when. Since we use Databricks Unity Catalog for access control and auditing, I decided to query the system’s audit logs to gather this information.

Querying Audit Logs for Table Activity

I started by running a query to retrieve the most recent events related to the production.sales.orders table. I used the system.access.audit table, which logs all access actions, and filtered for entries that included the table name in the request_params field.

SELECT event_time, user_identity, action_name, request_params 
FROM system.access.audit 
WHERE request_params LIKE '%production.sales.orders%' 
ORDER BY event_time DESC;

I noticed that the results included entries for production.sales.customers, which was not the table I was interested in. This might have been a result of the filtering logic or a data inconsistency. I realized I needed a more precise query to focus only on production.sales.orders.

Refining the Query with a Limit

To get a more focused view, I adjusted the query to include only the event_time, user_identity, and action_name fields, and limited the results to the most recent 50 entries. This would help me quickly identify any patterns or anomalies in the access behavior.

SELECT event_time, user_identity, action_name 
FROM system.access.audit 
WHERE request_params LIKE '%production.sales.orders%' 
ORDER BY event_time DESC 
LIMIT 50;

I verified that the results were still showing entries for production.sales.customers, which was unexpected. This made me think that the search pattern might not be precise enough. I considered refining the query further or checking if there were any other tables with similar names that might be causing the mismatch.

Despite the unexpected results, the query provided valuable insights into the access patterns of the production.sales.orders table. I planned to review the logs more carefully and possibly refine the search terms to ensure I was capturing only the relevant events.