POST_START

Inventorying Sensitive Production Columns Before Applying Protection

I recently took on the task of securing sensitive data within our production environment. My first step was to understand what columns in our production databases might contain sensitive information. I needed a clear inventory of all columns across all tables in the production catalog, so I started by querying the system’s information schema.

Getting a General Overview of Production Columns

I ran the following SQL to get a broad view of all columns in the production catalog:

SELECT table_schema, table_name, column_name, data_type 
FROM system.information_schema.columns 
WHERE table_catalog = 'production';

From the output, I saw that the table schema includes columns like customer_id, customer_name, and region. While I knew customer_id was likely a unique identifier, I needed to determine which of these columns might contain sensitive or personally identifiable information.

Focusing on the Customers Schema

To narrow down my focus, I decided to look specifically at the customers schema, which likely contained more detailed customer information. I executed the following query to retrieve the columns in that schema:

SELECT table_name, column_name, data_type 
FROM system.information_schema.columns 
WHERE table_catalog = 'production' AND table_schema = 'customers';

The results were similar to the previous query, but now I was focused on the customers schema. I noticed that customer_name was a string type and might contain names, which could be sensitive. I also saw that region was a string, which might contain location data, potentially indicating where a customer is based. These were the columns I needed to consider for protection.

With this inventory in place, I was ready to proceed with applying appropriate data protection measures to the identified sensitive columns. The next step was to determine which of these columns required encryption, masking, or access controls based on our data governance policies.