Audit-friendly approach for sensitive tables
Hard
SQL
DCL
18 views
Problem Statement
You want to allow SELECT on Payroll only through a view, not directly on the base table. Create a view that hides salary and bank_account, then grant SELECT on the view to user auditor.
Input Format
SQL DDL + DCL
Output Format
View created + privileges
Example
Payroll(emp_id, salary, bank_account)
Masked data accessible
Constraints
Do not grant base table
Input / Output Format
Input Format
SQL DDL + DCL
Output Format
View created + privileges
Constraints
Do not grant base table
Examples
Input:
Payroll(emp_id, salary, bank_account)
Output:
Masked data accessible
Example Solution (Public)
CREATE VIEW v_payroll_masked AS SELECT emp_id, NULL AS salary, NULL AS bank_account FROM Payroll; GRANT SELECT ON v_payroll_masked TO auditor;
Official Solution Code
CREATE VIEW v_payroll_masked AS SELECT emp_id, NULL AS salary, NULL AS bank_account FROM Payroll; GRANT SELECT ON v_payroll_masked TO auditor;
Please login to submit solutions.