How GBase 8a Handles Masked Columns in WHERE, GROUP BY, and Projections

Test Setup

Create a table with a default masking policy on an integer column and insert sample data:

CREATE TABLE "testmask" (
    "id" int(11) DEFAULT NULL MASKED WITH ( FUNCTION = 'DEFAULT()' )
);

INSERT INTO testmask VALUES (1), (2), (3), (4), (99);

Key Tests and Findings

1. Projection Returns Masked Values

A user without unmask privileges sees all id values replaced by the default mask 0. This is the basic masking behaviour.

SELECT * FROM testmask;
-- All five rows show id = 0

2. WHERE Filter Uses Original Values

A query with WHERE id = 1 returns exactly one row. Although the projected id still displays the masked value 0, the filter condition operates on the original data. The user can infer that a row with id = 1 exists, but the actual value is never revealed.

SELECT * FROM testmask WHERE id = 1;
-- One row returned; projected id is 0, but the predicate matched the original value 1

3. GROUP BY Uses Original Values

When grouping on a masked column, the aggregation is performed on the original data. The modulo operation id % 3 correctly reflects the distribution of the original values 1, 2, 3, 4, and 99.

SELECT id % 3, COUNT(*) FROM testmask GROUP BY id % 3;
-- Grouping is based on the original id values

4. ORDER BY Uses Original Values

Sorting on a masked column also uses the original data. Observing the rowid alongside the result confirms that rows are ordered by the original id descending, not by the uniform masked value 0.

SELECT rowid, t.* FROM testmask t ORDER BY id DESC;
-- Ordering is based on the original id values

5. Data Migration Writes Masked Values

When a masked column's data is moved - either via UPDATE to another column, or via INSERT SELECT into a new table - the physical data written is the masked value. The target column or table does not inherit the masking policy; it simply stores the already-masked 0.

UPDATE migration:

ALTER TABLE testmask ADD COLUMN id2 int;
UPDATE testmask SET id2 = id;
SELECT * FROM testmask;
-- id2 is all 0, and id2 column has no mask

INSERT SELECT migration:

CREATE TABLE testmask2 AS SELECT * FROM testmask;
SELECT * FROM testmask2;
-- both id and id2 are all 0, no mask defined

Summary

The behaviour of data masking in a GBase database follows a clear principle:

Masking is applied only when the column is projected - either returned to the application or copied to another storage location. When the column participates in filtering, grouping, or sorting, the engine operates on the original, unmasked values. This behaviour aligns with Oracle Data Redaction policies and is essential to understand when designing secure, yet performant, queries in GBASE's GBase 8a.