Data Governance And Security practice questions

Partitioning

Why it fails: Partitioning organizes data for performance.

Database Mirroring

Why it fails: Mirroring is for high availability, not security.

Database Indexing

Why it fails: Indexing improves speed, not security.

IP Firewall rules on the SQL Server

Why it fails: Firewall rules restrict by network location, not by time or user identity.

Service Principals with managed identity

Why it fails: Managed identities are for service authentication, not manual user access.

Azure RBAC with Conditional Access policies

Why it fails: Conditional Access is for broader access conditions, PIM is specifically for time-bound privilege elevation.

SQL Server Audit logs

Why it fails: Auditing tracks access but does not enforce time-based restrictions.

Grant SELECT on the 'PatientRecords' table to all users but create a database trigger that logs any query referencing the 'Diagnosis' column and alerts the compliance officer.

Why it fails: Logging and alerting do not prevent access; they only detect it after the fact. The requirement is to prevent non-clinical users from viewing the 'Diagnosis' column. A trigger that logs access still allows the data to be viewed, violating the security policy. This approach is reactive rather than preventive and does not enforce the necessary access restriction.

Implement row-level security policies that filter rows based on the user's role, ensuring only 'ClinicalStaff' can see any rows containing a diagnosis.

Why it fails: Row-level security filters rows, not columns. If non-clinical users need to see other columns in the same rows, row-level security would hide entire rows, which is overly restrictive and does not address the specific requirement of hiding only the 'Diagnosis' column. It also does not prevent them from seeing the 'Diagnosis' column in rows they are allowed to access.

Encrypt the 'Diagnosis' column with a symmetric key and grant the decryption key only to the 'ClinicalStaff' role.

Why it fails: Encrypting the column does protect confidentiality, but it does not prevent non-clinical users from seeing the ciphertext if they have SELECT on the table. They could still retrieve the encrypted values, which may be considered a privacy breach. Additionally, encryption adds complexity and does not enforce column-level access control by itself, so it fails to meet the requirement that non-clinical users must not view the column.

Revoke the analysts' SELECT privilege on the patient encounters table and require them to use a stored procedure that returns only approved columns.

Why it fails: A stored procedure can restrict output, but it constrains analysts to the procedure's fixed result shape and parameters, which blocks legitimate ad hoc analysis beyond the approved columns. Revoking table SELECT also risks breaking other approved workflows, making this less precise than exposing a view that already excludes only the sensitive attribute.

Apply dynamic data masking to the Social Security Number column so analysts see masked values while ETL reads real values.

Why it fails: Dynamic data masking can hide values from analysts, but it depends on role-based unmask privileges and vendor-specific policy engines, and masked output still exposes the column and its format. It is also harder to guarantee that downstream ETL is not accidentally caught by the same masking policy, so it is a less deterministic fit than removing the column from the analysts' path entirely.

Encrypt the Social Security Number column with a column-level key and share the decryption key only with the billing ETL service account.

Why it fails: Column encryption protects data at rest and from storage-level compromise, but an analyst with SELECT on the table and access to the application path may still retrieve decrypted values depending on where decryption occurs. It also adds key-management complexity and does not directly answer the requirement to prevent viewing the column during queries.

Binding Corporate Rules (BCRs) for the entire corporate group

Why it fails: BCRs are a valid transfer mechanism, but they require approval from a lead supervisory authority and are complex to implement. The question asks for a mechanism specifically designed for this transfer; SCCs are the standard contractual tool, while BCRs are an alternative that demands significant regulatory engagement.

Explicit consent from each employee for the transfer

Why it fails: Consent is a GDPR legal basis for processing, but it is not the preferred transfer safeguard for routine HR data because it must be freely given and can be withdrawn. Relying on consent for payroll transfers creates operational and legal fragility compared to contractual safeguards such as SCCs.

Adequacy decision by the European Commission for the United States

Why it fails: An adequacy decision is issued by the European Commission and is not something a company implements. The EU-US Data Privacy Framework provides an adequacy route only for certified US organizations, not a blanket decision. The company cannot unilaterally rely on an adequacy decision it does not control.

Oracle Transparent Data Encryption (TDE)

Why it fails: Transparent Data Encryption encrypts data at rest, protecting against unauthorized access to storage media. It does not record who modified data or prevent users from denying their actions. While TDE is important for confidentiality, it is irrelevant to the traceability and non-repudiation requirements described in the scenario.

Oracle Audit Vault and Database Firewall

Why it fails: Audit Vault and Database Firewall centralizes audit data collection and monitors SQL traffic, but it does not inherently ensure that database users cannot deny their actions. It can detect and alert on suspicious activity, yet non-repudiation requires a mechanism that binds users to their transactions at the database level. This solution is more about monitoring and consolidation than about proving individual accountability.

Oracle Database Vault

Why it fails: Database Vault restricts access to application data and enforces separation of duties by preventing privileged users from accessing data they do not own. It does not provide non-repudiation for transactions or tie modifications to individual users in an auditable way. In this scenario, the requirement is to trace changes to specific users and prevent denial, which is an auditing and authentication concern, not access restriction.

Dynamic Data Masking

Why it fails: Dynamic Data Masking obscures sensitive data in query results but does not log access or changes. It is a data protection feature, not an auditing mechanism. It would not record who accessed or modified the data.

Change Data Capture (CDC)

Why it fails: CDC tracks changes to data for ETL purposes, not for security auditing. It captures insert, update, and delete activity but does not record the user or SELECT statements. It is not designed to provide a security audit trail for compliance.

SQL Server Profiler trace

Why it fails: Profiler can capture SQL statements but is deprecated and not intended for production auditing. It lacks the granular security context and retention features of SQL Server Audit, and it can impose significant overhead. It is not the recommended tool for compliance auditing.

Column-level encryption using pgcrypto

Why it fails: Column-level encryption encrypts data at rest, but administrators with access to the encryption keys can decrypt and view the data. It does not prevent administrators from seeing the plaintext when they have key access. It also requires application changes for encryption and decryption.

Dynamic data masking with the anon extension

Why it fails: The PostgreSQL Anonymizer extension provides masking, but it is not a built-in feature and requires installation. It can mask data dynamically, but administrators with sufficient privileges might bypass it. The question specifies a built-in feature, and this is an extension.

Row-level security (RLS)

Why it fails: Row-level security controls which rows a user can access based on a policy, but it does not mask the data within those rows. It would not hide credit card numbers from administrators who have access to the rows. RLS is about row filtering, not column masking.

Data Ingestion Pipeline

Why it fails: An ingestion pipeline moves and loads data; it does not maintain the audit trail, cataloguing or request-tracking records CCPA disclosure obligations demand. It is tempting because governance frameworks include pipelines, and ingestion is correct when the requirement is acquiring or onboarding new data sources.

Load Balancing

Why it fails: Load balancing distributes traffic across servers and holds no metadata about data subjects or their requests. It is tempting because governance frameworks cover infrastructure availability, and load balancing is correct when the requirement concerns resilience or throughput of systems serving data, not regulatory tracking.

Data Compression

Why it fails: Compression reduces storage size and has no bearing on recording, locating or evidencing consumer access requests. It is tempting because data governance frameworks do address storage efficiency, and compression is the right answer when the requirement concerns reducing volume or cost of retained datasets.

Discretionary Access Control (DAC)

Why it fails: DAC grants access via each object's owner discretion, so permissions follow owner choices rather than job roles, failing the role-based requirement. It is tempting because DAC suits small environments needing flexible, ad-hoc sharing, but it cannot enforce consistent role-to-file mappings across an organisation.

Mandatory Access Control (MAC)

Why it fails: MAC assigns access from system-enforced labels and clearances, not from a user's job role. It is tempting because MAC is a strict, centrally controlled model, but role-based access is delivered by RBAC, which maps permissions to job functions.

Network Access Control (NAC)

Why it fails: NAC governs endpoint admission to a network through policy checks, not which files a user's job role permits, so it cannot enforce file-level role access. It is tempting because NAC is genuinely correct for controlling device onboarding and quarantining non-compliant endpoints.

Update the VPC Route Table

Why it fails: VPC route tables direct subnet traffic to gateways and cannot grant or deny S3 object reads; bucket access is governed by IAM policies and bucket policies. It tempts because route tables control network reachability, and would be correct when restricting which subnets can reach an endpoint.

Enable Public Access Block

Why it fails: Public Access Block only prevents bucket policies and ACLs from granting public exposure; it cannot limit access to a named Finance group. It tempts because it hardens buckets against anonymous access, and would be correct when the goal is blocking all public exposure rather than scoping reads to a specific team.

Enable S3 Versioning

Why it fails: S3 Versioning preserves and recovers object revisions; it grants no access control and cannot restrict viewing to the Finance team. It tempts because versioning supports data-protection and recovery goals, and would be the right choice when the requirement is retaining prior object states rather than enforcing identity-based read permissions.

Database Replication

Why it fails: Database replication copies data to secondary instances for availability and read scaling; it duplicates ciphertext and never controls which application holds decryption keys. Replication would be the right answer if the requirement were disaster recovery, failover or distributing read workloads across regions.

Database Triggers

Why it fails: Database triggers execute procedural code in response to data events such as inserts or updates; they enforce integrity rules and cannot gate cryptographic decryption by application identity. A trigger would be correct if the requirement were automating audit logging or cascading changes on table modifications.

Shared Responsibility Model

Why it fails: The Shared Responsibility Model allocates security duties between provider and customer; it does not itself decrypt data. It is tempting because it governs cloud security generally, and would be the right answer when the question asks who secures what layer, not which control restricts decryption to authorised applications.

Snapshotting

Why it fails: Snapshotting creates point-in-time copies that preserve the personal data, so erasure is not achieved and copies may persist indefinitely. It is tempting because snapshots aid recovery and audit trails, and deletion workflows must account for them, but taking snapshots contradicts the GDPR erasure requirement.

Database Sharding

Why it fails: Sharding partitions data across nodes for scalability; it does not locate and erase a data subject's records on every node. It is tempting because sharding spreads data physically, and a compliant deletion process must reach each shard, but sharding itself performs no deletion.

Cache clearing

Why it fails: Cache clearing only removes transient copies; the authoritative records persist on each node, so erasure cannot be demonstrated. It is tempting because caches often retain personal data, and clearing them is a valid step within a broader deletion workflow, but it does not satisfy GDPR erasure across the cluster.

Database File Stream

Why it fails: FileStream stores and accesses BLOB data as files on the filesystem; it provides no encryption mechanism or external key custody for column values. It is tempting because it moves data outside the database engine, but it is correct for storing large unstructured documents, not for encrypting PII columns with externally managed keys.

Transparent Data Encryption (TDE)

Why it fails: TDE encrypts the entire database and log files at rest, with the database master key and certificate held inside the SQL Server instance, so keys are not managed externally. It is tempting as a broad at-rest encryption control, and would be correct for whole-database file encryption rather than column-level PII protection.

SQL Server Data Masking

Why it fails: Data Masking obscures column values in query results for non-privileged users while the underlying stored data remains plaintext, so it neither encrypts nor manages keys externally. It is tempting because it targets sensitive columns, and would be correct for limiting exposure in test or reporting environments, not for cryptographic protection.

Data Partitioning

Why it fails: Data partitioning splits large tables into smaller segments to improve query performance and manageability, recording no user or administrative activity. It is tempting because it is a database feature administrators configure, but it addresses storage and performance, not auditing; an audit facility captures the administrative actions required.

Query Optimization

Why it fails: Query optimisation rewrites or indexes statements to reduce execution cost, generating no record of who performed administrative actions. It is tempting because it is a core DBA tuning task, but it targets performance rather than accountability; auditing needs a facility that logs administrative events to a reviewable trail.

Transaction Log Backup

Why it fails: Transaction log backups capture data changes for point-in-time recovery and restore, recording no administrative actions or login events. Tempting because logs are associated with auditing, but they serve disaster recovery; auditing requires enabling the database server's native audit or trace facility that records administrative activity.

Database Clustering

Why it fails: Clustering replicates data across nodes for high availability and failover; every node holds a readable copy, widening rather than closing the exfiltration path. It is selected when uptime and automatic failover, not insider threat, are the requirement.

Database Archiving

Why it fails: Archiving moves aged data to secondary storage for retention and cost control; the rogue administrator retains access to the archive, so exfiltration remains possible. It is the right choice for meeting retention policies or shrinking the active dataset.

Database Sharding

Why it fails: Sharding partitions data across nodes for scalability and query distribution; it grants an administrator with access to any shard the same readable data, so it does not restrict exfiltration. It is chosen to spread load or grow beyond one server's capacity.

Backup the production database

Why it fails: A DPIA identifies and mitigates privacy risks before processing begins; it does not copy or restore data. It is tempting because both activities sit inside data governance and both concern production systems, but backup is a resilience control, whereas a DPIA is a documented risk evaluation required for high-risk processing.

Configure firewall rules

Why it fails: A DPIA evaluates privacy risk to individuals; firewall rules are a network security control, a separate discipline. It is tempting because both appear under security compliance and both protect data, but a DPIA produces a risk assessment document, not technical access restrictions, and GDPR requires it before high-risk processing starts.

Accelerate database performance

Why it fails: A DPIA assesses risks to data subjects' rights from high-risk processing and documents mitigation; it has no performance role. It is tempting because DPIAs accompany large data projects where tuning matters, but database speed is addressed by indexing, query plans and schema design, not by a GDPR risk assessment.

Data Replication

Why it fails: Replication copies data to additional nodes or regions for availability and disaster recovery; it duplicates all tenants' rows rather than restricting visibility. Replication suits durability and latency requirements, whereas tenant isolation demands row-level security or separate schemas keyed to the tenant identifier.

Schema Versioning

Why it fails: Schema versioning tracks structural changes to database objects across releases; it enforces no row-level tenant boundary, so tenant A's queries could still return tenant B's rows. It is tempting because versioned schemas do support controlled multi-tenant deployments, and would be correct where the requirement is managing divergent table structures per tenant.

Database Encryption

Why it fails: Encryption protects data at rest from unauthorised access to storage media, but does not filter rows by tenant; a user with valid keys still reads every tenant's records. Encryption is the right control for meeting confidentiality or compliance requirements, not for logical data isolation between tenants.

Database Snapshotting

Why it fails: A snapshot captures the entire database at a point in time, retaining all production records and exposing personal data. Snapshots are correct for point-in-time recovery or rollback, not for producing a privacy-protected subset with preserved structure.

Database Migration

Why it fails: Migration moves schema and data between platforms or versions; it neither reduces the dataset nor anonymises values, so privacy is unprotected. Migration is correct when relocating a database, whereas test-data generation needs subsetting plus masking to preserve structure without exposing real records.

Full Database Clone

Why it fails: A full clone copies every production row, so no subsetting occurs and sensitive records remain, breaching the privacy requirement. Cloning suits scenarios needing complete, identical environments; masking or subsetting is required when only representative, de-identified data is wanted.

Server Inventory List

Why it fails: A Server Inventory List records hardware, operating systems and hosting details, not accountability for data assets. Data Owner responsibilities belong in a data governance policy or RACI matrix. It is tempting because ownership of servers feels adjacent to data ownership, yet the inventory names custodians of infrastructure, not owners of data domains.

Database Schema

Why it fails: A Database Schema defines tables, columns, keys and relationships, describing data structure rather than accountability. Data Owner responsibilities are documented in a governance policy or RACI chart. It is tempting because schemas touch the data itself, yet they specify technical layout, not who owns and stewards each data domain.

Network Topology Map

Why it fails: A Network Topology Map documents routers, switches and connectivity paths, so it cannot assign Data Owner accountability. Those responsibilities sit in a data governance charter or stewardship matrix. It is tempting because both artefacts map enterprise assets, but topology describes infrastructure links rather than ownership of data domains.