Securing Sensitive Data

HardSQL · PostgreSQL

Problem

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.

Output

One row per authorized grant
Columns: email, dataset_name, classification, permission_level, granted_role, and access_scope
Exclude inactive users, inactive datasets, revoked grants, expired grants, and unauthorized sensitive access
Order by email, then dataset_name

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	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

datasets

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

access_grants

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

Tablesusersdatasetsaccess_grants