POST_START

Removing Stale Production Access After Team Changes

Last week, our team underwent a restructuring, and the former sales team was disbanded. As part of the cleanup, I needed to ensure that no former team members still had access to production data. My first step was to check what access was currently granted on the production.sales.orders table.

SHOW GRANTS ON TABLE production.sales.orders;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

I noticed that the former sales team was no longer part of the list, but I wanted to be thorough. I decided to double-check the grants to make sure there were no lingering permissions from the previous team.

SHOW GRANTS ON TABLE production.sales.orders;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

After verifying the current grants, I was confident there were no remnants of the former sales team. However, to be absolutely sure, I wanted to explicitly revoke any accidental or outdated access. I ran the revoke command for the SELECT privilege on the production.sales.orders table for the former-sales-team principal.

REVOKE SELECT ON TABLE production.sales.orders FROM `former-sales-team`;

I saw a representative result like this:

result
Revoke applied successfully; the requested privilege is no longer granted.

The system confirmed that the privilege had been successfully revoked. To ensure the change was effective, I ran the SHOW GRANTS command once more.

SHOW GRANTS ON TABLE production.sales.orders;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

With the final check complete, I was satisfied that the former sales team no longer had access to the production.sales.orders table. This step was crucial to maintaining security and ensuring that only authorized users could access production data. It also reinforced the importance of regularly auditing access rights, especially after team changes.