POST_START

Finding Excessive Table Privileges Assigned to Production Groups

I recently took on the task of reviewing access controls for a few key tables in our production environment. One of the tables that raised my attention was production.finance.payments. I wanted to make sure that the right people had the right level of access and that no unnecessary privileges were granted to production groups.

Checking Privileges with SHOW GRANTS

I started by using the SHOW GRANTS ON TABLE command, which is a straightforward way to see what privileges have been assigned to a specific table. I ran the following query:

SHOW GRANTS ON TABLE production.finance.payments;

I saw a representative result like this:

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

From this output, I learned that the data_analysts group has SELECT access to the payments table, and the data_engineers group has MODIFY access. While these privileges make sense for their roles, I wanted to double-check if these were the only privileges assigned to these groups across all tables in the finance schema.

Reviewing Privileges with the Information Schema

To get a more comprehensive view, I turned to the system.information_schema.table_privileges table. This allows me to query all the privileges assigned to tables in a specific catalog and schema. I used the following SQL command:

SELECT * FROM system.information_schema.table_privileges WHERE table_catalog = 'production' AND table_schema = 'finance' AND table_name = 'payments';

I saw a representative result like this:

grantee | privilege_type | object_name
data_analysts | SELECT | customers
data_engineers | MODIFY | customers

This output showed that the data_analysts group had SELECT access to the customers table as well, and the data_engineers group had MODIFY access. While this is expected for the customers table, it raised a question: were these groups granted unnecessary privileges across other tables in the finance schema?

Understanding the Implications

I realized that the data_engineers group had MODIFY access to multiple tables, which could pose a security risk if they were not fully trusted with modifying financial data. Similarly, the data_analysts group had SELECT access to tables that might contain sensitive information.

I verified that the SHOW GRANTS command provided a high-level overview, while the information_schema table offered more granular details. Both were essential for my analysis. I wanted to ensure that the groups in question had only the privileges they needed to perform their duties, and no more.

Next Steps

With this information, I planned to review the access controls for other tables in the finance schema and consider whether the privileges assigned to these groups were appropriate. I also thought about implementing additional access controls or role-based permissions to better restrict access to sensitive data.