POST_START

Troubleshooting Column-Level Security with Function and Grant Inspection

I was working on a data pipeline where certain users needed access to specific columns in the customer data, but not all. I noticed that when I tried to query the email column, it wasn’t showing up in the results, even though the column existed in the table. I needed to understand why the email column wasn’t visible and how to resolve this issue. I decided to investigate the function and grants that might be affecting access to the email field.

DESCRIBE FUNCTION EXTENDED production.security.email_mask;

I ran the DESCRIBE FUNCTION EXTENDED command to see what the email_mask function was doing. The output showed that the function was taking in customer_id, customer_name, and region, but not the email. This made me think that the function might be masking the email column or that it wasn’t being applied correctly. I wanted to check if the function had the necessary permissions to be executed by the users who needed access.

SHOW GRANTS ON FUNCTION production.security.email_mask;

I checked the grants on the email_mask function using SHOW GRANTS. The output showed that the function was granted to both data_analysts and data_engineers groups, which were the users I expected to have access. This suggested that the function was available, but I still couldn’t see the email column. I needed to dig deeper into the table structure to confirm what columns were actually being returned.

DESCRIBE TABLE EXTENDED production.customers.customer_master;

I ran DESCRIBE TABLE EXTENDED on the customer_master table to confirm the columns it contained. The output listed customer_id, customer_name, and region

, but not email. This made me realize that the email column might not be part of the table itself, or it might be masked by the function. I needed to test the query to see if the function was being applied correctly.

SELECT customer_id, email FROM production.customers.customer_master;