Week 22 - Security & Governance

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 CSVcreate or replace file format frosty_csvtype = csvfield_delimiter = ','field_optionally_enclosed_by = '"'skip_header = 1;-- Creates stage to read the CSVcreate or replace stage w22_frosty_stageurl = 's3://frostyfridaychallenges/challenge_22/'file_format = frosty_csv;-- Roles needed for challengecreate role rep1;create role rep2;-- Grant roles to self for testinggrant 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 warehousegrant role public to role rep1;grant role public to role rep2;-- Create the table from the CSV in S3create table <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.week22 asselect 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 accessgrant usage on database <YOUR_DB_NAME> to role rep1;grant usage on database <YOUR_DB_NAME> to role rep2;-- And schema accessgrant usage on schema <YOUR_DB_NAME>.challenges to role rep1;grant usage on schema <YOUR_DB_NAME>.challenges to role rep2;-- And usage of viewgrant 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 queriesuse role rep1select * from <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities;use role rep2select * 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