Week 22 – Basic

The sales manager at Frosty Sales has no friends. Therefore, instead of giving each sales reps a list of cities, he decided to order them so that city-districts with an odd id belong to rep1, and even to rep2.

Your job is to create a secure view that will:

  • Ensure rep1 can only see city-districts with an odd id
  • Ensure rep2 can only see city-districts with an even id
  • The ‘id’ fields is actually a randomly generated id (so we can fulfil secure view best practice )
Start-Up Code

-- File format to read the CSV
create or replace file format frosty_csv
    type = csv
    field_delimiter = ','
    field_optionally_enclosed_by = '"'
    skip_header = 1;
    
-- Creates stage to read the CSV
create or replace stage w22_frosty_stage
  url = 's3://frostyfridaychallenges/challenge_22/'
  file_format = frosty_csv;
  
-- Roles needed for challenge
create role rep1;
create role rep2;

-- Grant roles to self for testing
grant role rep1 to user <INSERT_YOUR_USERNAME>;
grant role rep2 to user <INSERT_YOUR_USERNAME>;

-- Enable warehouse usage. Assumes that `public` has access to the warehouse
grant role public to role rep1;
grant role public to role rep2;

-- Create the table from the CSV in S3
create table <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.week22 as
select t.$1::int id, t.$2::varchar(50) city, t.$3::int district from @w22_frosty_stage (pattern=>'.*sales_areas.*') t;

-- Code for creating the secure view
<ENTER_CODE_HERE>
    
-- Roles need DB access
grant usage on database <YOUR_DB_NAME> to role rep1;
grant usage on database <YOUR_DB_NAME> to role rep2;
-- And schema access
grant usage on schema <YOUR_DB_NAME>.challenges to role rep1;
grant usage on schema <YOUR_DB_NAME>.challenges to role rep2;
-- And usage of view
grant select on view <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities to role rep1;
grant select on view <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities to role rep2;

-- Get the result of queries
use role rep1
select * from <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities;

use role rep2
select * from <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities;

RESULTS

When rep1 queries, they should see this:

When rep2 queries, they should see this:

Remember, if you want to participate:

  1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
  2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
  3. Post the URL in the comments of the challenge

7 responses to “Week 22 – Basic”

  1. mat avatar
    mat

    Thanks for another puzzle

    • Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w22.sql
  2. ChrisHastie avatar
    ChrisHastie

    Apologies for my tardiness, I was away for the weekend. To make up for it, my solution includes 4 different methods to achieve these requirements. I’ve also thrown in a column masking policy to provide the randomly generated ID field when the executing role does not have access to a specific role.

    • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
  3. Atzmonky avatar
    Atzmonky

    Added my ‘secure view’ approach: No policy this time, but with a mapping table.
    Was wondering though… Shouldn’t the ‘district id’ be checked for even/odd and not ‘id’ – not that it really matters .. 🙂

    • Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ch22_rls_secure_views.sql
  4. Jamie Laird avatar
    Jamie Laird

    Here’s my solution

    • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_22/ff_week_22.sql
  5. zlzlzl2 avatar
    zlzlzl2

    Learning new functions on row level access policy, need to practice my grants better though.

    • Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF022_LZ.sql
  6. Non-deterministicNorman avatar
    Non-deterministicNorman

    picked up a few things along the way 🙂

    • Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2022%20-%20basic%20/RLS

Leave a Reply