POST_START

Testing Column-Level Security for Privileged and Standard Users

I recently needed to verify how column-level security is enforced in our Databricks environment. My task was to test whether privileged users could access certain columns in a table, while standard users were restricted. To do this, I started by examining the structure of the customer_master table in the production.customers schema.

Reviewing the Table Structure

I ran the SHOW CREATE TABLE command to understand the schema and any potential security configurations applied to the table.

SHOW CREATE TABLE production.customers.customer_master;

I saw a representative result like this:

createtab_stmt
CREATE VIEW production.reporting.daily_sales AS SELECT ...

This output showed that the customer_master table might be involved in some view or security configuration, which made me curious about how column access was controlled.

Checking the Current User

Before proceeding with data queries, I wanted to confirm my current user identity to ensure I was testing under the correct permissions. I ran the SELECT current_user() command.

SELECT current_user();

I saw a representative result like this:

current_user()
analyst@demo.com

This confirmed that I was logged in as analyst@demo.com, which I assumed was a standard user with limited access. I would later test with a privileged user to compare the results.

Querying the Customer Master Table

With the table structure and my user identity confirmed, I decided to query the customer_master table to see what data I could access. I ran the following command:

SELECT * FROM production.customers.customer_master LIMIT 20;

I saw a representative result like this:

customer_id | customer_name | region | status
1001 | Maria Keller | EU | ACTIVE
10

This output showed that I could access the customer_id, customer_name, region, and status columns. However, I noticed that the status column had values like ACTIVE and INACTIVE, which might be sensitive. I wanted to confirm whether my access was limited by column-level security rules.

Testing with a Privileged User

To test column-level security, I switched to a privileged user account to see if they had different access. I repeated the same steps to verify that the privileged user could access additional columns or more detailed data.

I ran the SHOW CREATE TABLE command again, just to ensure the table structure was consistent.

SHOW CREATE TABLE production.customers.customer_master;

I saw a representative result like this:

createtab_stmt
CREATE VIEW production.reporting.daily_sales AS SELECT ...

I then ran the SELECT current_user() command to confirm my new identity.

SELECT current_user();

I saw a representative result like this:

current_user()
admin@demo.com

Finally, I executed the same query to see if the privileged user had access to additional columns or more detailed information.

SELECT * FROM production.customers.customer_master LIMIT 20;

I saw a representative result like this:

customer_id | customer_name | region | status | additional_info
1001 | Maria Keller | EU | ACTIVE | Confidential
1002 | Daniel Smith | US | ACTIVE | ...

This confirmed that column-level security was in effect. The privileged user had access to additional columns like additional_info, while the standard user did not. This helped me understand how security policies were enforced at the column level in our Databricks environment.