Week 88 - Security & Governance

This week we're taking a look into Data Governance and taking it a step further then the Masking Policy and Row Access Policies that usually get all of the attention.

We're going to protect a column from being queried completely BUT we'd still like to be able to use it for our joins.

CREATE or replace TABLE PersonalDetails (
ID INT PRIMARY KEY,
FirstName VARCHAR,
LastName VARCHAR,
SSN VARCHAR
);

INSERT INTO PersonalDetails (ID, FirstName, LastName, SSN) VALUES(1, 'John', 'Doe', '123-45-6789'),(2, 'Jane', 'Doe', '987-65-4321'),(3, 'Jim', 'Beam', '111-22-3333'),(4, 'Jill', 'Valentine', '444-55-6666'),(5, 'Leon', 'Kennedy', '777-88-9999'),(6, 'Claire', 'Redfield', '222-33-4444'),(7, 'Chris', 'Redfield', '555-66-7777'),(8, 'Ada', 'Wong', '888-99-0000'),(9, 'Albert', 'Wesker', '666-77-8888'),(10, 'Rebecca', 'Chambers', '999-00-1111'),(11, 'Barry', 'Burton', '333-44-5555'),(12, 'Carlos', 'Oliveira', '666-55-4444'),(13, 'Nikolai', 'Zinoviev', '777-33-2222'),(14, 'Jill', 'Sandwich', '888-44-5555'),(15, 'Hunk', 'Unknown', '999-66-7777');

CREATE or replace TABLE EmploymentDetails (
EmploymentID INT PRIMARY KEY,
SSN VARCHAR,
CompanyName VARCHAR,
Position VARCHAR,
StartDate DATE,
Salary INT
);

INSERT INTO EmploymentDetails (EmploymentID, SSN, CompanyName, Position, StartDate, Salary) VALUES
(1, '123-45-6789', 'ACME Corporation', 'Software Engineer', '2018-06-01', 70000),
(2, '987-65-4321', 'Globex Corporation', 'Project Manager', '2019-08-15', 75000),
(3, '111-22-3333', 'Soylent Corp', 'Quality Assurance Engineer', '2020-02-01', 68000),
(4, '444-55-6666', 'Initech', 'IT Support Specialist', '2017-05-23', 62000),
(5, '777-88-9999', 'Umbrella Corporation', 'Research Scientist', '2021-03-12', 78000),
(6, '222-33-4444', 'Hooli', 'Data Analyst', '2018-07-01', 69000),
(7, '555-66-7777', 'Vehement Capital Partners', 'Investment Analyst', '2019-09-09', 71000),
(8, '888-99-0000', 'Massive Dynamic', 'Executive Assistant', '2020-01-20', 65000),
(9, '666-77-8888', 'Wayne Enterprises', 'Security Consultant', '2017-04-10', 72000),
(10, '999-00-1111', 'Stark Industries', 'Mechanical Engineer', '2021-08-05', 83000),
(11, '333-44-5555', 'Pied Piper', 'Software Developer', '2019-06-01', 85000),
(12, '666-55-4444', 'Bluth Company', 'Sales Manager', '2018-11-01', 64000),
(13, '777-33-2222', 'Dunder Mifflin', 'Regional Manager', '2017-12-01', 73000),
(14, '888-44-5555', 'Los Pollos Hermanos', 'Operations Manager', '2020-07-15', 55000),
(15, '999-66-7777', 'Cyberdyne Systems', 'Systems Analyst', '2019-04-01', 76000);

The challenge this week is a bit different in that we want you to works towards an error instead of a solution. The error in question :

error caused by SELECT * FROM PersonalDetail

This error SHOULD popup when we execute a 'SELECT * FROM PersonalDetails' but we should still be able to run the following :

SELECT p.FirstName, p.LastName, e.CompanyName, e.Position, e.StartDate, e.SalaryFROM PersonalDetails pJOIN EmploymentDetails e ON p.SSN = e.SSN;

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