Week 132 - Security & Governance

Data Security: Snowflake Join Policies

"A little knowledge is a dangerous thing" — a quote attributed to many, yet still undeniably true. We’ve all witnessed situations where people jump to conclusions based on partial information rather than considering the full picture.

Today, we’re tackling this issue by enforcing restrictions that prevent users from viewing only a fragment of the data.

Join Policies to the Rescue

Join Policies in Snowflake allow us to enforce joins, ensuring that users cannot retrieve data from a table unless they join it with another specific table. This is a powerful way to ensure data is always viewed within its proper context.

---

The Challenge

Step 1: Create and Populate the Tables

Run the following script to set up your environment:

-- Create the primary table with a Join Policy requirement
CREATE OR REPLACE TABLE join_table (
    col1 INT,
    col2 VARCHAR,
    col3 NUMBER 
)
JOIN POLICY my_join_policy;

-- Insert sample data
INSERT INTO join_table (col1, col2, col3) 
VALUES (1, 'Sample Data', 123.45);

-- Create the secondary detail table
CREATE OR REPLACE TABLE join_table_details (
    col1 INT,
    col4 VARCHAR,
    col5 DATE
);

-- Insert additional info
INSERT INTO join_table_details (col1, col4, col5) 
VALUES (1, 'Additional Info', '2025-03-07');

---

Step 2: Enforce Join Restriction

Your task is to configure the join policy so that only the ACCOUNTADMIN role can query join_table directly.

Any other role attempting to query join_table without a proper join (e.g., running SELECT * FROM join_table) should encounter an error message instead of retrieving data.

The Goal

If anyone besides ACCOUNTADMIN runs a direct query on join_table, the expected result should be an error message similar to: