Use Spark SQL to implement row-level filtering and column masking

Version requirements

Spark requires AnalyticDB for MySQL to be upgraded to a specific kernel version. Clusters running a version earlier than 3.2.8 must be upgraded first.

Permission requirements

• Policy management DDL statements (CREATE, UPDATE, DROP, BIND, UNBIND, and SHOW) must be executed by an administrator account. If a regular user executes a policy DDL statement, an AccessDenied error is returned.

• Regular users do not need additional authorization to be constrained by policies. A policy takes effect for a user once it is bound to that user.

User identity

• Policy binding and enforcement are based on the ADB username (logon account). The username in BIND ... TO '<user>' must exactly match the ADB logon account of the target user.

• During a query, the system automatically resolves the current user identity from the logon session and matches the policies bound to that user. If identity resolution fails, the query is aborted (fail-closed).

Connection method

Connect to a Spark interactive resource group through JDBC (Beeline, DBeaver, or an application). Example:

jdbc:hive2://<spark-endpoint>:10000/<database>?resource_group=<resource_group_name>

Scenarios

• Multi-tenant data isolation: Analysts of each tenant can only view data rows of their own tenant, for example, tenant_id = 'T001'.

• Region or organization-level data visibility: Regional operators can only view data of their own region, for example, region = 'APAC'.

• Sensitive record protection: Regular employees cannot view sensitive records such as executive or high-salary records, for example, salary < 50000.

• Compliance and audit requirements: Regulations require specific groups to access only desensitized subsets of data rows.

SQL reference

Examples

The following examples use the demo table sec_e2e.employees:

-- Prepare demo data
CREATE DATABASE IF NOT EXISTS sec_e2e;
CREATE TABLE IF NOT EXISTS sec_e2e.employees (
  id INT, name STRING, region STRING, department STRING, salary DOUBLE, phone STRING
) USING parquet;

INSERT INTO sec_e2e.employees VALUES
  (1, 'Alice', 'APAC', 'HR',          75000.0,  '13812341234'),
  (2, 'Bob',   'EU',   'Engineering', 95000.0,  '13956785678'),
  (3, 'Carol', 'US',   'Finance',     85000.0,  '13790129012'),
  (4, 'David', 'APAC', 'Engineering', 105000.0, '13634563456'),
  (5, 'Eve',   'EU',   'HR',          70000.0,  '13578907890');

Scenario: The APAC analyst global_read can only view employee data in the APAC region.

-- 1. Create a row filter policy
CREATE ROW FILTER POLICY rf_apac ON sec_e2e.employees USING (region = 'APAC');

-- 2. Bind the policy to the target user
BIND ROW FILTER POLICY rf_apac TO 'global_read';

-- 3. Verify the policy and binding
SHOW ROW FILTER POLICIES IN sec_e2e;
-- PolicyName | Database | Table     | Condition
-- rf_apac    | sec_e2e  | employees | region = 'APAC'

SHOW ROW FILTER BINDINGS FOR POLICY rf_apac;
-- PolicyName | Database | Table     | Condition        | User
-- rf_apac    | sec_e2e  | employees | region = 'APAC'  | global_read

The bound user global_read runs a query (no SQL modification required):

SELECT * FROM sec_e2e.employees;
-- Only rows where region = 'APAC' are returned (id=1 Alice and id=4 David).
-- Rows in the EU and US regions are invisible to this user.

Filter conditions support any valid SQL predicate expression, including functions and UDFs:

-- Combined conditions
CREATE ROW FILTER POLICY rf_dept ON sec_e2e.employees
  USING (department = 'HR' AND salary > 71000.0);

-- Use a UDF as the filter condition
CREATE FUNCTION IF NOT EXISTS is_hr_with_valid_int
  AS 'com.aliyun.adb.udf.IsHrWithValidInt' USING JAR 'oss://<bucket>/hr-udf-1.0.0.jar';

CREATE ROW FILTER POLICY rf_hr_high_salary ON sec_e2e.employees
  USING (is_hr_with_valid_int(department, CAST(phone AS BIGINT)) AND salary > 71000.0);

Policy maintenance:

-- Update the filter condition (takes effect immediately for all bound users)
UPDATE ROW FILTER POLICY rf_apac USING (region = 'EU');

-- Unbind a user (the user immediately regains full visibility)
UNBIND ROW FILTER POLICY rf_apac FROM 'global_read';

-- Delete the policy (cascade-removes all bindings)
DROP ROW FILTER POLICY rf_apac;

Idempotent creation is supported for scripted and automated management:

-- If the policy already exists, the statement is silently skipped
CREATE ROW FILTER POLICY IF NOT EXISTS rf_apac ON sec_e2e.employees USING (region = 'APAC');

Scope and bypass prevention

Row-level filtering is automatically injected during query analysis. It is transparent to users and cannot be bypassed through SQL:

• Subqueries: SELECT * FROM (SELECT * FROM sec_e2e.employees) t. Inner scans are also filtered.

• CTEs (WITH clauses): WITH cte AS (SELECT * FROM sec_e2e.employees) SELECT * FROM cte. Also filtered.

• Views (including temporary views): Queries on temporary views are also filtered.

• JOINs: The filter condition applies only to tables with bound policies. Unbound tables in the join are not affected.

Write protection

When row-level filtering is enabled (rowFilter.enabled=true), write protection is enabled by default (blockWrites.enabled=true):

• When a row filter policy is bound to a user on a table, all data modification operations on that table are prohibited, including INSERT (INTO/OVERWRITE), UPDATE, DELETE, MERGE, TRUNCATE TABLE, LOAD DATA, REPLACE TABLE, and ALTER TABLE ... DROP PARTITION.

• Violating write operations are rejected and aborted.

Scenarios not affected by write protection:

• Users without bound policies (including administrators) can still write to the same table.

• CREATE TABLE ... AS SELECT (CTAS) to a new table is allowed because no existing policy bindings apply to the new table.

• INSERT INTO unbound_table SELECT ... FROM bound_table is allowed. Row-level filtering still applies when reading from the source table.

Limits

• Disabled by default: spark.adb.security.rowFilter.enabled defaults to false. When disabled, policies do not take effect and queries return plaintext data (fail-open). This is a static configuration that must be set in the resource group or job startup configuration.

• Built-in catalog only: Row-level filtering applies only to tables in spark_catalog (the built-in metadata catalog). Tables accessed through external catalogs (such as external-hive or paimon V2 catalogs) are not covered.

• One policy per user per table: A user can bind only one row filter policy per table. Binding a second policy returns an error. To switch policies, unbind the old policy first.

• Multiple policies per table: A table can have multiple row filter policies (with unique names) for different user groups, but each user can match only one policy.

• Syntax pre-validation: Filter conditions are syntax-checked at creation time, but referenced columns and functions are resolved at query time. If the table schema changes after policy creation and the condition cannot be resolved, queries from bound users are aborted (fail-closed).

• Policy change propagation: Changes take effect immediately in the session that executes the DDL. Other running Spark applications have a policy cache with a default TTL of 5 minutes.

• Cross-engine conflicts: If a row filter policy already exists on the same table in another engine (XIHE), creating a policy in Spark returns an error. This conflict cannot be resolved from the Spark side.

• Metadata service degradation: If the metadata service endpoint is not configured, row-level filtering degrades to a no-op. Regular queries are not affected, but protection does not take effect.

Scenarios

• PII protection: Mask phone numbers, ID numbers, and email addresses for regular analysts, for example, 138****1234.

• Salary protection: Regular employees cannot view actual salaries and can only see hashed or masked values.

• Compliance requirements: Regulations require sensitive fields to be invisible in plaintext in unauthorized scenarios.

• Data sharing and outsourced analysis: External collaborators can analyze data, but sensitive columns are always masked.

SQL reference

Column masking DDL follows the same structure as row-level filtering, replacing ROW FILTER with MASKING and specifying the target column:

Examples

The following examples use the sec_e2e.employees table from the row-level filtering examples. The target user is global_read.

Partial masking (retain the first 3 and last 4 digits of phone numbers):

CREATE MASKING POLICY cm_phone ON sec_e2e.employees COLUMN phone
  USING (concat(substr(phone, 1, 3), '****', substr(phone, 8, 4)));

BIND MASKING POLICY cm_phone TO 'global_read';

Query result for global_read:

SELECT id, name, phone FROM sec_e2e.employees;
-- id | name  | phone
-- 1  | Alice | 138****1234
-- 2  | Bob   | 139****5678
-- ... (The phone column is fully masked. Plaintext is not visible.)

Hash masking (irreversible digest for correlation analysis without revealing original values):

CREATE MASKING POLICY cm_salary ON sec_e2e.employees COLUMN salary
  USING (md5(cast(salary AS STRING)));

BIND MASKING POLICY cm_salary TO 'global_read';
-- global_read sees a 32-character hexadecimal string for the salary column, such as '5f4dcc3b5aa765d61d8327deb882****'

Full masking (constant mask):

CREATE MASKING POLICY cm_name ON sec_e2e.employees COLUMN name
  USING ('********');

BIND MASKING POLICY cm_name TO 'global_read';
-- global_read sees '********' for all values in the name column

NULL masking (completely hide column values):

CREATE MASKING POLICY cm_region ON sec_e2e.employees COLUMN region
  USING (CAST(NULL AS STRING));

BIND MASKING POLICY cm_region TO 'global_read';
-- global_read sees NULL for all values in the region column

Multi-column masking:

You can create multiple masking policies for different columns of the same table. Columns without masking policies retain their original values:

SELECT * FROM sec_e2e.employees WHERE id = 1;
-- global_read perspective:
-- id=1, name='********', region=NULL, department='HR' (original value),
-- salary=<32-char md5>, phone='138****1234'

Policy maintenance:

-- Update the masking expression
UPDATE MASKING POLICY cm_phone USING ('********');

-- View all masking policies bound to a user
SHOW MASKING BINDINGS FOR USER 'global_read';

-- Unbind a user (the user immediately regains access to original values)
UNBIND MASKING POLICY cm_phone FROM 'global_read';

-- Delete the policy (cascade-removes all bindings)
DROP MASKING POLICY cm_phone;

Scope and bypass prevention

Like row-level filtering, column masking is automatically injected during query analysis. Subqueries, CTEs, and views (including temporary views) cannot bypass masking. In JOIN scenarios, only the columns of tables with bound policies are masked.

Limits

• Disabled by default: spark.adb.security.columnMask.enabled defaults to false. When disabled, policies do not take effect and queries return plaintext data (fail-open).

• Built-in catalog only: Column masking applies only to tables in spark_catalog. External catalog tables are not covered.

• Masking expressions support only pure scalar expressions. The following are rejected:

  • Aggregate functions such as sum(col) and max(col).

  • Window functions or window expressions such as row_number() OVER (...).

  • Function calls with DISTINCT or FILTER modifiers.

  • Function names with more than three parts (only fn or db.fn forms are supported).

  • References to columns outside the current table (referenced columns must belong to the policy table).

• One masking policy per column per user: At query time, only one masking policy can match for a given (table, column, user) combination. If multiple bindings exist, the query is aborted (fail-closed). An administrator must clean up to exactly one binding per column.

• Output data type may change: The output column name remains the same, but the type is determined by the masking expression. Downstream reports or applications that depend on the original column type must be evaluated in advance.

• Column masking affects reads only: Write operations such as INSERT from bound users proceed normally (writing original values), but read results are masked. For bind-and-block-writes protection, use row-level filtering.

• Expression pre-validation: Expressions are syntax-checked at creation time, but referenced columns and functions are resolved at query time. If the table schema changes and the expression cannot be resolved, queries from bound users are aborted (fail-closed).

• Policy change propagation: Changes take effect immediately in the session that executes the DDL. Other running Spark applications have a policy cache with a default TTL of 5 minutes.

• Cross-engine conflicts: If a masking policy already exists on the same column in another engine (XIHE), creating a policy in Spark returns an error. Contact the administrator to resolve the conflict in the original engine.