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 )
-- 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;
When rep1 queries, they should see this:
When rep2 queries, they should see this:
Remember, if you want to participate:
went with a row access policy
— the model
— init (including creation of the row access policy)
Thanks for another puzzle
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.
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 .. 🙂
Jamie Laird says
Here’s my solution