Week 9 – Intermediate

STORY
It’s not just bad guys that need to guard their secrets!
Superheroes are our first line of defence against those evil-doers so we really need to protect their information.
Running a superhero organisation however is a big job so we’ve got a lot of people that have access to our systems and we need to make sure that the true identity of our heroes is never revealed!
HR is advocating for a more personal touch to our business though and has requested that some staff should be able to see the first names of the superheroes to connect on a more basic level. Higher ups should still be able to see everything !
With the constant changing roles within the organisation , we’d really like something that’s dynamic and can handle roles that haven’t been created yet.

CHALLENGE

With the use of Tags and Masking , we want to mask the first_name and last_name columns from our data_to_be_masked table.
We want the following :

• The default user that has access can only see the hero_name data unmasked
• Role foo1 can only see hero_name and first_name
• Role foo2 can see the contents of the whole table
• The used masking policy should NOT use a role checking feature. (current_role = … etc.)

START UP CODE

--CREATE DATA
CREATE OR REPLACE TABLE data_to_be_masked(first_name varchar, last_name varchar,hero_name varchar);
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Eveleen', 'Danzelman','The Quiet Antman');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Harlie', 'Filipowicz','The Yellow Vulture');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Mozes', 'McWhin','The Broken Shaman');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Horatio', 'Hamshere','The Quiet Charmer');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Julianna', 'Pellington','Professor Ancient Spectacle');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Grenville', 'Southouse','Fire Wonder');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Analise', 'Beards','Purple Fighter');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Darnell', 'Bims','Mister Majestic Mothman');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Micky', 'Shillan','Switcher');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Ware', 'Ledstone','Optimo');

--CREATE ROLE
CREATE ROLE foo1;
CREATE ROLE foo2;
GRANT ROLE foo1 TO USER <ENTER USERNAME HERE>;
GRANT ROLE foo2 TO USER <ENTER USERNAME HERE>;

RESULTS

–Can you see the data?

USE ROLE ACCOUNTADMIN;
SELECT * FROM data_to_be_masked;

–What can foo1 see?

USE ROLE foo1;


SELECT * FROM data_to_be_masked;

–And foo2?

USE ROLE foo2;


SELECT * FROM data_to_be_masked;

ADMIN NOTE :
This challenge has been clarified on 30-09-2022 and the last bullet point for the challenge was added.
Any solutions given before this time might not be a total solution to the question.

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‘ (note joining our mailing list does not give you a Frosty Friday account)
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.