Hands-on practice: Configuring Security Policies

Implementing security controls requires translating governance requirements into database objects. A production-grade Massively Parallel Processing (MPP) environment rarely manages permissions on a user-by-user basis. Instead, you construct a system of inherited privileges and data-driven policies that adapt as your organization scales.

In this exercise, we will secure a sensitive dataset containing financial transactions. We will implement a three-tier security architecture:

1. Hierarchical RBAC to manage functional access.
2. Dynamic Data Masking to obfuscate sensitive columns based on role.
3. Row-Level Security (RLS) to filter data rows based on geographic entitlements.

Establishing the Role Hierarchy

A flat role structure becomes unmanageable as the number of users increases. We use a hierarchical model where higher-level functional roles inherit privileges from lower-level access roles. This strategy, often referred to as functional role composition, allows for cleaner audit trails and simplified grant management.

Consider a scenario with two user types: Financial Analysts, who need read access to transaction data but should not see Personally Identifiable Information (PII), and Compliance Officers, who require full visibility into all data fields.

We first define the access roles that hold the explicit privileges on the database objects.

-- Create the base access role
CREATE ROLE role_access_finance_read;
GRANT USAGE ON DATABASE fin_db TO ROLE role_access_finance_read;
GRANT USAGE ON SCHEMA fin_db.transactions TO ROLE role_access_finance_read;
GRANT SELECT ON TABLE fin_db.transactions.ledger TO ROLE role_access_finance_read;

Next, we create the functional roles and map them to the access roles. This separation allows us to swap underlying database permissions without modifying the functional user assignments.

-- Create functional roles
CREATE ROLE functional_analyst;
CREATE ROLE functional_compliance;

-- Grant inheritance
GRANT ROLE role_access_finance_read TO ROLE functional_analyst;
GRANT ROLE role_access_finance_read TO ROLE functional_compliance;

The following diagram illustrates the inheritance structure. The functional roles (top) inherit permissions from the object-access roles (middle), which hold the direct grants to the database objects (bottom).

Hierarchy of role inheritance separating functional requirements from object-level permissions.

Implementing Dynamic Data Masking

Column-level security ensures that sensitive fields remain obfuscated for unauthorized users while remaining clear for privileged roles. We use Dynamic Data Masking (DDM) to alter the data at query time without changing the underlying storage.

We will create a masking policy for the account_number column. Analysts should see only the last four digits, while Compliance Officers see the full number.

The masking logic can be mathematically represented as a function $M(v, r)$ where $v$ is the value and $r$ is the user's role:

$$M(v, r) = \begin{cases} v & \text{if } r \in \{\text{Compliance}\} \\ \text{concat('****', right(v, 4))} & \text{otherwise} \end{cases}$$

Here is the SQL implementation for a standard cloud data warehouse:

CREATE OR REPLACE MASKING POLICY mask_account_number AS (val string) 
RETURNS string ->
    CASE
        WHEN CURRENT_ROLE() IN ('FUNCTIONAL_COMPLIANCE') THEN val
        ELSE '****' || RIGHT(val, 4)
    END;

-- Apply the policy to the column
ALTER TABLE fin_db.transactions.ledger 
MODIFY COLUMN account_number 
SET MASKING POLICY mask_account_number;

When a user with the FUNCTIONAL_ANALYST role queries the table, the database engine intercepts the execution plan and injects this CASE statement into the projection clause. This ensures the raw data never leaves the storage layer.

Configuring Row-Level Security (RLS)

While masking handles columns, RLS handles horizontal filtering. We often need to restrict access based on data attributes, such as region or department. Hardcoding these rules into views (e.g., v_ledger_us, v_ledger_eu) creates technical debt and schema explosion.

A scalable Level 3 approach utilizes a mapping table (entitlement table) and a Row Access Policy. This allows security logic to be data-driven rather than code-driven.

First, create an entitlement table that maps roles to allowed values.

CREATE TABLE security.entitlements (
    role_name VARCHAR,
    region_code VARCHAR
);

-- Populate entitlements
INSERT INTO security.entitlements (role_name, region_code) VALUES 
('FUNCTIONAL_ANALYST_US', 'US'),
('FUNCTIONAL_ANALYST_EU', 'EU'),
('FUNCTIONAL_COMPLIANCE', 'ALL');

Next, we define the row access policy. The policy evaluates to TRUE if the current user's role matches a row in the entitlement table corresponding to the data's region.

The filtering logic implies that for a query returning set $R$, a row $r \in R$ is included if and only if:

$$\exists e \in E : (e_{role} = \text{CurrentRole}) \land (e_{region} = r_{region} \lor e_{region} = \text{'ALL'})$$

where $E$ is the set of entitlements.

CREATE OR REPLACE ROW ACCESS POLICY region_policy 
AS (region_val varchar) RETURNS BOOLEAN ->
    EXISTS (
        SELECT 1 FROM security.entitlements
        WHERE role_name = CURRENT_ROLE()
          AND (region_code = region_val OR region_code = 'ALL')
    );

-- Apply the policy to the table
ALTER TABLE fin_db.transactions.ledger
ADD ROW ACCESS POLICY region_policy ON (region_code);

This configuration forces the query optimizer to perform a semi-join against the entitlements table for every query on the ledger.

Validating the Security Model

After applying both masking and row-level policies, you must verify the behavior. Verification should test both positive cases (access granted) and negative cases (access denied/masked).

We utilize EXECUTE AS (or equivalent session context switching) to simulate different user personas.

-- Test as US Analyst
USE ROLE functional_analyst_us;
SELECT region_code, account_number, amount 
FROM fin_db.transactions.ledger;

Expected Output for US Analyst:

1. Rows: Only rows where region_code = 'US' are returned.
2. Columns: The account_number appears as ****1234.

-- Test as Compliance Officer
USE ROLE functional_compliance;
SELECT region_code, account_number, amount 
FROM fin_db.transactions.ledger;

Expected Output for Compliance Officer:

1. Rows: All regions are returned.
2. Columns: The account_number appears fully visible.

The following diagram demonstrates the data flow when a query is executed under these policies. The query engine evaluates the request against the metadata layer before retrieving micro-partitions from storage.

Sequence of policy evaluation enforcing RLS before data retrieval and masking before data presentation.

Performance

Applying these policies introduces overhead. The RLS policy adds a join operation to every query. To mitigate performance degradation:

1. Cluster by Policy Columns: Ensure the table is clustered by the column used in the RLS policy (e.g., region_code). This allows the engine to prune partitions effectively even with the policy applied.
2. Materialize Entitlements: If the entitlement table is large, query performance may suffer. Keep the entitlement table small and heavily cached.
3. Memoizable Functions: In advanced setups, you can wrap the entitlement lookup in a memoizable function to prevent repeated scanning of the mapping table within the same session.

By combining hierarchical RBAC, Dynamic Masking, and table-driven RLS, you ensure that the data warehouse remains secure by design, enforcing governance rules transparently at the infrastructure level.