POST_START

Identifying Who Granted or Revoked Production Unity Catalog Access from Audit Events

I recently needed to determine who granted or revoked access to the production database in Unity Catalog. This was critical for understanding the access changes that could impact the security and compliance of our financial data. I decided to use the audit events in the system.access.audit table, which logs all access-related actions.

I started by querying the audit events for any actions that involved granting access. I used the

SELECT * FROM system.access.audit WHERE action_name LIKE '%grant%' ORDER BY event_time DESC;

command to retrieve all such events, sorted by the most recent first. This helped me quickly identify the latest grant operations, which were essential for understanding recent access changes.

Checking for Revoke Actions

As I reviewed the grant events, I realized that I also needed to check for any revoke actions that might have been performed. These could indicate when access was removed, which is just as important for tracking changes in access rights. I ran the

SELECT * FROM system.access.audit WHERE action_name LIKE '%revoke%' ORDER BY event_time DESC;

query to get all revoke events, again sorted by time.

I noticed that the revoke events were fewer in number, but they provided valuable insight into when specific users had their access removed. This helped me understand the full scope of access changes over time.

Verifying Grants on a Specific Table

To ensure I had the correct permissions for the production.finance.payments table, I wanted to verify what grants were in place. I used the

SHOW GRANTS ON TABLE production.finance.payments;

command to list all the grants assigned to that table. This step was crucial for cross-referencing the audit events with the current access rights and confirming that the changes I observed in the logs were accurate and reflected in the actual permissions.

By following these steps, I was able to trace the access changes to the production database, identify who granted or revoked access, and ensure that the current permissions aligned with the audit records. This process was essential for maintaining security and compliance in our data environment.