Week 7 – Intermediate

Being a villain is hard enough as it is and data issues aren’t only a problem for the good guys. Villains have got a lot of overhead and information to keep track of and EVIL INC. has started using Snowflake for it’s needs.

However , you’ve noticed that the most important part of your superweapons have been leaked : The catch-phrase!

Fortunately , you’ve set up tagging to allow you to keep track of who accessed what information!

Your challenge is to figure out who accessed data that was tagged with “Level Super Secret A+++++++”

Because it might be a bit too difficult to create users to access the data, we’re using roles instead of users.

The following is the preliminary code we want you to run before the challenge. Note that account_usage takes 2 hours to update, so we suggest running the below code and then coming back to the challenge at least a couple of hours later.

If you want to create a new database and schema for this challenge…

-- Create Database
create or replace database FF_WEEK_7;
create or replace warehouse compute_wh with warehouse_size='X-SMALL';
use database FF_WEEK_7;

-- Create Schemas
create schema super_weapons;
create schema super_monsters;
create schema super_villains;

-- Create Tables and Mock data
create or replace table super_villains.villain_information (
	id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(50),
	Alter_Ego VARCHAR(50)
);
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (1, 'Chrissy', 'Riches', 'criches0@ning.com', 'Waterbuck, defassa');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (2, 'Libbie', 'Fargher', 'lfargher1@vistaprint.com', 'Ibis, puna');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (3, 'Becka', 'Attack', 'battack2@altervista.org', 'Falcon, prairie');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (4, 'Euphemia', 'Whale', 'ewhale3@mozilla.org', 'Egyptian goose');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (5, 'Dixie', 'Bemlott', 'dbemlott4@moonfruit.com', 'Eagle, long-crested hawk');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (6, 'Giffard', 'Prendergast', 'gprendergast5@odnoklassniki.ru', 'Armadillo, seven-banded');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (7, 'Esmaria', 'Anthonies', 'eanthonies6@biblegateway.com', 'Cat, european wild');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (8, 'Celine', 'Fotitt', 'cfotitt7@baidu.com', 'Clark''s nutcracker');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (9, 'Leopold', 'Axton', 'laxton8@mac.com', 'Defassa waterbuck');
insert into super_villains.villain_information (id, first_name, last_name, email, Alter_Ego) values (10, 'Tadeas', 'Thorouggood', 'tthorouggood9@va.gov', 'Armadillo, nine-banded');

create or replace table super_monsters.monster_information (
	id INT,
	monster VARCHAR(50),
	hideout_location VARCHAR(50)
);
insert into super_monsters.monster_information (id, monster, hideout_location) values (1, 'Northern elephant seal', 'Huangban');
insert into super_monsters.monster_information (id, monster, hideout_location) values (2, 'Paddy heron (unidentified)', 'Várzea Paulista');
insert into super_monsters.monster_information (id, monster, hideout_location) values (3, 'Australian brush turkey', 'Adelaide Mail Centre');
insert into super_monsters.monster_information (id, monster, hideout_location) values (4, 'Gecko, tokay', 'Tafí Viejo');
insert into super_monsters.monster_information (id, monster, hideout_location) values (5, 'Robin, white-throated', 'Turośń Kościelna');
insert into super_monsters.monster_information (id, monster, hideout_location) values (6, 'Goose, andean', 'Berezovo');
insert into super_monsters.monster_information (id, monster, hideout_location) values (7, 'Puku', 'Mayskiy');
insert into super_monsters.monster_information (id, monster, hideout_location) values (8, 'Frilled lizard', 'Fort Lauderdale');
insert into super_monsters.monster_information (id, monster, hideout_location) values (9, 'Yellow-necked spurfowl', 'Sezemice');
insert into super_monsters.monster_information (id, monster, hideout_location) values (10, 'Agouti', 'Najd al Jumā‘ī');

create table super_weapons.weapon_storage_location (
	id INT,
	created_by VARCHAR(50),
	location VARCHAR(50),
	catch_phrase VARCHAR(50),
	weapon VARCHAR(50)
);
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (1, 'Ullrich-Gerhold', 'Mazatenango', 'Assimilated object-oriented extranet', 'Fintone');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (2, 'Olson-Lindgren', 'Dvorichna', 'Switchable demand-driven knowledge user', 'Andalax');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (3, 'Rodriguez, Flatley and Fritsch', 'Palmira', 'Persevering directional encoding', 'Toughjoyfax');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (4, 'Conn-Douglas', 'Rukem', 'Robust tangible Graphical User Interface', 'Flowdesk');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (5, 'Huel, Hettinger and Terry', 'Bulawin', 'Multi-channelled radical knowledge user', 'Y-Solowarm');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (6, 'Torphy, Ritchie and Lakin', 'Wang Sai Phun', 'Self-enabling client-driven project', 'Alphazap');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (7, 'Carroll and Sons', 'Digne-les-Bains', 'Profound radical benchmark', 'Stronghold');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (8, 'Hane, Breitenberg and Schoen', 'Huangbu', 'Function-based client-server encoding', 'Asoka');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (9, 'Ledner and Sons', 'Bukal Sur', 'Visionary eco-centric budgetary management', 'Ronstring');
insert into super_weapons.weapon_storage_location (id, created_by, location, catch_phrase, weapon) values (10, 'Will-Thiel', 'Zafar', 'Robust even-keeled algorithm', 'Tin');


--Create Tags
create or replace tag security_class comment = 'sensitive data';


--Apply tags
alter table super_villains.villain_information set tag security_class = 'Level Super Secret A+++++++';
alter table super_monsters.monster_information set tag security_class = 'Level B';
alter table super_weapons.weapon_storage_location set tag security_class = 'Level Super Secret A+++++++';


--Create Roles
create role user1;
create role user2;
create role user3;

--Assign Roles to yourself with all needed privileges
grant role user1 to role accountadmin;
grant USAGE  on warehouse compute_wh to role user1;
grant usage on database ff_week_7 to role user1;
grant usage on all schemas in database ff_week_7 to role user1;
grant select on all tables in database ff_week_7 to role user1;

grant role user2 to role accountadmin;
grant USAGE  on warehouse compute_wh to role user2;
grant usage on database ff_week_7 to role user2;
grant usage on all schemas in database ff_week_7 to role user2;
grant select on all tables in database ff_week_7 to role user2;

grant role user3 to role accountadmin;
grant USAGE  on warehouse compute_wh to role user3;
grant usage on database ff_week_7 to role user3;
grant usage on all schemas in database ff_week_7 to role user3;
grant select on all tables in database ff_week_7 to role user3;

--Queries to build history
use role user1;
use database FF_WEEK_7;
select * from super_villains.villain_information;

use role user2;
use database FF_WEEK_7;
select * from super_monsters.monster_information;

use role user3;
use database FF_WEEK_7;
select * from super_weapons.weapon_storage_location;

If you don’t want to create new databases/schemas for this challenge…

USE WAREHOUSE <enter_wh_here>;
USE DATABASE <enter_db_here>;
USE SCHEMA <enter_schema_here>;

create or replace table week7_villain_information (
	id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(50),
	Alter_Ego VARCHAR(50)
);
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (1, 'Chrissy', 'Riches', 'criches0@ning.com', 'Waterbuck, defassa');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (2, 'Libbie', 'Fargher', 'lfargher1@vistaprint.com', 'Ibis, puna');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (3, 'Becka', 'Attack', 'battack2@altervista.org', 'Falcon, prairie');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (4, 'Euphemia', 'Whale', 'ewhale3@mozilla.org', 'Egyptian goose');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (5, 'Dixie', 'Bemlott', 'dbemlott4@moonfruit.com', 'Eagle, long-crested hawk');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (6, 'Giffard', 'Prendergast', 'gprendergast5@odnoklassniki.ru', 'Armadillo, seven-banded');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (7, 'Esmaria', 'Anthonies', 'eanthonies6@biblegateway.com', 'Cat, european wild');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (8, 'Celine', 'Fotitt', 'cfotitt7@baidu.com', 'Clark''s nutcracker');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (9, 'Leopold', 'Axton', 'laxton8@mac.com', 'Defassa waterbuck');
insert into week7_villain_information (id, first_name, last_name, email, Alter_Ego) values (10, 'Tadeas', 'Thorouggood', 'tthorouggood9@va.gov', 'Armadillo, nine-banded');

create or replace table week7_monster_information (
	id INT,
	monster VARCHAR(50),
	hideout_location VARCHAR(50)
);

insert into week7_monster_information (id, monster, hideout_location) values (1, 'Northern elephant seal', 'Huangban');
insert into week7_monster_information (id, monster, hideout_location) values (2, 'Paddy heron (unidentified)', 'Várzea Paulista');
insert into week7_monster_information (id, monster, hideout_location) values (3, 'Australian brush turkey', 'Adelaide Mail Centre');
insert into week7_monster_information (id, monster, hideout_location) values (4, 'Gecko, tokay', 'Tafí Viejo');
insert into week7_monster_information (id, monster, hideout_location) values (5, 'Robin, white-throated', 'Turośń Kościelna');
insert into week7_monster_information (id, monster, hideout_location) values (6, 'Goose, andean', 'Berezovo');
insert into week7_monster_information (id, monster, hideout_location) values (7, 'Puku', 'Mayskiy');
insert into week7_monster_information (id, monster, hideout_location) values (8, 'Frilled lizard', 'Fort Lauderdale');
insert into week7_monster_information (id, monster, hideout_location) values (9, 'Yellow-necked spurfowl', 'Sezemice');
insert into week7_monster_information (id, monster, hideout_location) values (10, 'Agouti', 'Najd al Jumā‘ī');


create table week7_weapon_storage_location (
	id INT,
	created_by VARCHAR(50),
	location VARCHAR(50),
	catch_phrase VARCHAR(50),
	weapon VARCHAR(50)
);

insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (1, 'Ullrich-Gerhold', 'Mazatenango', 'Assimilated object-oriented extranet', 'Fintone');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (2, 'Olson-Lindgren', 'Dvorichna', 'Switchable demand-driven knowledge user', 'Andalax');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (3, 'Rodriguez, Flatley and Fritsch', 'Palmira', 'Persevering directional encoding', 'Toughjoyfax');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (4, 'Conn-Douglas', 'Rukem', 'Robust tangible Graphical User Interface', 'Flowdesk');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (5, 'Huel, Hettinger and Terry', 'Bulawin', 'Multi-channelled radical knowledge user', 'Y-Solowarm');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (6, 'Torphy, Ritchie and Lakin', 'Wang Sai Phun', 'Self-enabling client-driven project', 'Alphazap');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (7, 'Carroll and Sons', 'Digne-les-Bains', 'Profound radical benchmark', 'Stronghold');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (8, 'Hane, Breitenberg and Schoen', 'Huangbu', 'Function-based client-server encoding', 'Asoka');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (9, 'Ledner and Sons', 'Bukal Sur', 'Visionary eco-centric budgetary management', 'Ronstring');
insert into week7_weapon_storage_location (id, created_by, location, catch_phrase, weapon) 
    values (10, 'Will-Thiel', 'Zafar', 'Robust even-keeled algorithm', 'Tin');
    

--Create Tags
create or replace tag security_class comment = 'sensitive data';

--Apply tags
alter table week7_villain_information set tag security_class = 'Level Super Secret A+++++++';
alter table week7_monster_information set tag security_class = 'Level B';
alter table week7_weapon_storage_location set tag security_class = 'Level Super Secret A+++++++';

--Create Roles
create role user1;
create role user2;
create role user3;

--Assign Roles to yourself with all needed privileges
grant role user1 to role accountadmin;
grant USAGE  on warehouse <enter_wh_here> to role user1;
grant usage on database <enter_db_here> to role user1;
grant usage on all schemas in database <enter_db_here> to role user1;
grant select on all tables in database <enter_db_here> to role user1;

grant role user2 to role accountadmin;
grant USAGE  on warehouse <enter_wh_here> to role user2;
grant usage on database <enter_db_here> to role user2;
grant usage on all schemas in database <enter_db_here> to role user2;
grant select on all tables in database <enter_db_here> to role user2;

grant role user3 to role accountadmin;
grant USAGE  on warehouse <enter_wh_here> to role user3;
grant usage on database <enter_db_here> to role user3;
grant usage on all schemas in database <enter_db_here> to role user3;
grant select on all tables in database <enter_db_here> to role user3;

--Queries to build history
use role user1;
select * from week7_villain_information;

use role user2;
select * from week7_monster_information;

use role user3;
select * from week7_weapon_storage_location;

Result

Your answer should look like the below

Remember if you want to participate:

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

12 responses to “Week 7 – Intermediate”

ChrisHastie

2022-07-29
Another fun challenge, and my first time actually querying the usage history based on tags so a fun way to learn.

My solution URL:
https://github.com/ChrisHastieIW/Frosty-Friday/tree/main/Week%207%20-%20Intermediate%20-%20Tags%2C%20Account%20Usage
Log in to Reply
1. ChrisHastie
  
  2022-09-05
  Reorganised my submission repo, new master solution URL here.
  
  Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
  Log in to Reply
dsmdavid

2022-07-29
I’m loving this.
Don’t access the account usage frequently, and it showed.

My approach using dbt:

– Modify the pre-required script to run it as a run-operation for prep.
– Add the SNOWFLAKE database as a source.
– Check the account_usage for…
– All the objects accessed using Access History
– Keeping only the objects that contain the relevant tag from the Tag Reference (on ObjectID)
– And retrieving the roles from Query History

Docs here: http://dvd-il-bucket-public.s3-website-eu-west-1.amazonaws.com/#!/model/model.frosty.challenge_07#code
- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_07.sql
Log in to Reply
sachin.mittal04@gmail.com

2022-08-21
https://github.com/sachingithubaccnt/Frosty_Friday/blob/main/Week7_Intermediate
https://github.com/sachingithubaccnt/Frosty_Friday/blob/main/Week7_Intermediate
Approach:
1. Use the existing create Database script
2. Use ACCESS_HISTORY table and group by on TableName,user_name
3. Use ACCESS_HISTORY and flatten on table(flatten(obj.value:columns) columns to get the frequently accessed columns
4. Use information_schema.tag_references_all_columns to get the list of columns with tag_name = ‘SECURITY_CLASS’and tag_value = ‘Level Super Secret A+++++++’
Log in to Reply
Pekka Kanerva

2022-09-30
Nice intro to account usage views!
- Solution URL – https://github.com/pekka-kanerva/frosty-friday/blob/main/week7/ff-week7-solution.sql
Log in to Reply
Atzmonky

2022-10-04
Check out a blog post of the challenge: https://theinformationlab.nl/en/2022/10/04/history-access-in-snowflake/
- Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ch7_AccessHistory.sql
Log in to Reply
zlzlzl2

2022-10-31
Good opportunities to learn what can be found in the account usage schema.
- Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/00325d5785da3ca831ffe71cbc488b372d340949/FF7_LZ.sql
Log in to Reply
Jamie Laird

2023-01-29
Here’s my solution
- Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_7/ff_week_7.sql
Log in to Reply
jameskalfox

2023-04-12
Really liked the task in this one, good way to explore the account_usage schema
- Solution URL – https://github.com/jameskalfox/frosty-friday-snowflake-challenges/blob/main/Week_07.sql
Log in to Reply
canonicalized

2023-05-27
Very cool!
- Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK7.sql
Log in to Reply
mvdvelden

2023-12-29
Interesting challenge!
I had 2 solutions: one query with CTE’s and a shorter query without them:

https://github.com/marioveld/frosty_friday/tree/main/ffw7
- Solution URL – https://github.com/marioveld/frosty_friday/tree/main/ffw7
Log in to Reply
dzapp

2024-02-07
🙂
- Solution URL – https://github.com/darylkit/Frosty_Friday/blob/main/Week%207%20-%20Tags%2C%20Account%20Usage/tags_account_usage.sql
Log in to Reply

Comments

ChrisHastie says

2022-07-29 at 08:57
Another fun challenge, and my first time actually querying the usage history based on tags so a fun way to learn.

My solution URL:
https://github.com/ChrisHastieIW/Frosty-Friday/tree/main/Week%207%20-%20Intermediate%20-%20Tags%2C%20Account%20Usage
Log in to Reply
- ChrisHastie says
  
  2022-09-05 at 21:23
  Reorganised my submission repo, new master solution URL here.
  - Solution URL - https://github.com/ChrisHastieIW/Frosty-Friday
  Log in to Reply
dsmdavid says

2022-07-29 at 23:08
I’m loving this.
Don’t access the account usage frequently, and it showed.

My approach using dbt:

– Modify the pre-required script to run it as a run-operation for prep.
– Add the SNOWFLAKE database as a source.
– Check the account_usage for…
– All the objects accessed using Access History
– Keeping only the objects that contain the relevant tag from the Tag Reference (on ObjectID)
– And retrieving the roles from Query History

Docs here: http://dvd-il-bucket-public.s3-website-eu-west-1.amazonaws.com/#!/model/model.frosty.challenge_07#code
- Solution URL - https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_07.sql
Log in to Reply
sachin.mittal04@gmail.com says

2022-08-21 at 16:49
https://github.com/sachingithubaccnt/Frosty_Friday/blob/main/Week7_Intermediate
https://github.com/sachingithubaccnt/Frosty_Friday/blob/main/Week7_Intermediate
Approach:
1. Use the existing create Database script
2. Use ACCESS_HISTORY table and group by on TableName,user_name
3. Use ACCESS_HISTORY and flatten on table(flatten(obj.value:columns) columns to get the frequently accessed columns
4. Use information_schema.tag_references_all_columns to get the list of columns with tag_name = ‘SECURITY_CLASS’and tag_value = ‘Level Super Secret A+++++++’
Log in to Reply
Pekka Kanerva says

2022-09-30 at 12:12
Nice intro to account usage views!
- Solution URL - https://github.com/pekka-kanerva/frosty-friday/blob/main/week7/ff-week7-solution.sql
Log in to Reply
Atzmonky says

2022-10-04 at 08:51
Check out a blog post of the challenge: https://theinformationlab.nl/en/2022/10/04/history-access-in-snowflake/
- Solution URL - https://github.com/Atzmonky/snowflake/blob/main/ch7_AccessHistory.sql
Log in to Reply
zlzlzl2 says

2022-10-31 at 12:12
Good opportunities to learn what can be found in the account usage schema.
- Solution URL - https://github.com/zlzlzl2-data/FrostyFriday/blob/00325d5785da3ca831ffe71cbc488b372d340949/FF7_LZ.sql
Log in to Reply
Jamie Laird says

2023-01-29 at 13:12
Here’s my solution
- Solution URL - https://github.com/apd-jlaird/frosty-friday/blob/main/week_7/ff_week_7.sql
Log in to Reply
jameskalfox says

2023-04-12 at 15:19
Really liked the task in this one, good way to explore the account_usage schema
- Solution URL - https://github.com/jameskalfox/frosty-friday-snowflake-challenges/blob/main/Week_07.sql
Log in to Reply
canonicalized says

2023-05-27 at 13:13
Very cool!
- Solution URL - https://github.com/canonicalized/FrostyFriday/blob/main/WEEK7.sql
Log in to Reply
mvdvelden says

2023-12-29 at 20:41
Interesting challenge!
I had 2 solutions: one query with CTE’s and a shorter query without them:

https://github.com/marioveld/frosty_friday/tree/main/ffw7
- Solution URL - https://github.com/marioveld/frosty_friday/tree/main/ffw7
Log in to Reply
dzapp says

2024-02-07 at 07:02
🙂
- Solution URL - https://github.com/darylkit/Frosty_Friday/blob/main/Week%207%20-%20Tags%2C%20Account%20Usage/tags_account_usage.sql
Log in to Reply

12 responses to “Week 7 – Intermediate”

Leave a Reply Cancel reply

Reader Interactions

Comments

Leave a Reply Cancel reply