Your question is Securing Sensitive Data. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
How would you handle permissions and security on sensitive data sets?
Translate the access policy into a PostgreSQL query that returns only currently authorized dataset grants. Sensitive datasets require an active user, enabled MFA, and an approved security role. Standard datasets require only an active user and a valid grant.
email, dataset_name, classification, permission_level, granted_role, and access_scopeemail, then dataset_name| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| VARCHAR(255) | User email address | |
| role | VARCHAR(50) | User security role |
| is_active | BOOLEAN | Whether the user account is active |
| mfa_enabled | BOOLEAN | Whether multi-factor authentication is enabled |
| Column | Type | Description |
|---|---|---|
| dataset_idPK | INT | Unique dataset identifier |
| dataset_name | VARCHAR(150) | Dataset name |
| classification | VARCHAR(30) | Data classification such as public, internal, restricted, or secret |
| owner_team | VARCHAR(100) | Team responsible for the dataset |
| is_active | BOOLEAN | Whether the dataset is available |
| Column | Type | Description |
|---|---|---|
| grant_idPK | INT | Unique grant identifier |
| user_id | INT | User receiving the grant |
| dataset_id | INT | Dataset covered by the grant |
| permission_level | VARCHAR(30) | Granted access level |
| granted_role | VARCHAR(50) | Role recorded when access was granted |
| revoked_at | DATE | Date the grant was revoked |
| expires_at | DATE | Grant expiration date |