Secure Views & Row-Level Filtering

Overview

The REPOSITORY schema includes a set of VW_SECURE_DATASET_* views that mirror the base VW_DATASET_* views but add row-level security filtering. These secure views enforce the same permissions that exist in the Validatar application — ensuring that users querying the replicated data only see objects they are authorized to access.

Validatar's permission model operates on two independent axes: data source permissions and project permissions. Different views are filtered by different permission types depending on the nature of the data they expose.

The Permission Model

Two Permission Axes

These are independent — a user can have access to a data source's metadata without being a member of any project that uses it, and vice versa.

Three Security Tiers in the Secure Views

The secure views use one of three filtering strategies depending on what data they expose:

Event History: Context-Dependent Filtering

VW_SECURE_DATASET_EVENT_HISTORY uses a hybrid approach based on the event's context type:

• Project context events — filtered by project membership
• Data Source context events — filtered by data source ViewMetadata permission
• Global context events — visible to all users (USER_ID is NULL)

Available Secure Views

Data Source Permission (ViewMetadata)

These views filter rows based on VW_SECURE_USER_DATA_SOURCE_ACCESS — the user must have ViewMetadata permission on the data source:

Project Permission (Project Membership)

These views filter rows based on VW_SECURE_USER_PROJECT_ACCESS — the user must be a member of the project:

Combined Permission (Project + Data Source ViewTestResults)

These views filter rows based on VW_SECURE_USER_RESULTS_ACCESS — the user must have BOTH project membership AND the ViewTestResults data source permission:

For test executions, the user must have results access to both the test-side and control-side data sources (when a control data source exists). If the test has no control data source, only test-side access is required.

Context-Dependent (Event History)

Access Resolution

Data Source Access (VW_SECURE_USER_DATA_SOURCE_ACCESS)

A user gains data source access through any of these paths:

Schema:

Project Access (VW_SECURE_USER_PROJECT_ACCESS)

A user gains project access by being added as a project member (directly or via a user group):

Schema:

Results Access (VW_SECURE_USER_RESULTS_ACCESS)

The compound view that requires BOTH data source and project access. It resolves the intersection of:

1. User has ViewTestResults permission on the data source (not just ViewMetadata)
2. User is a project member for a project that uses that data source

Schema:

This view is built by joining PROJECT_DATA_SOURCES (which data sources are used in which projects) with the user's data source ViewTestResults permissions and project membership. A row only exists when the user satisfies both conditions.

Secure View Schema (Additional Columns)

Every VW_SECURE_DATASET_* view includes all columns from its base view plus:

Note: VW_SECURE_DATASET_TEST_EXECUTIONS and VW_SECURE_DATASET_TEMPLATE_TEST_EXECUTIONS only add USER_ID (no ACCESS_TYPE column) because access is resolved through the compound results view.

VW_DATASET_EFFECTIVE_USER_PERMISSIONS

A comprehensive audit view of all effective permissions across data sources, projects, and reports — including both explicit assignments and implicit permissions (from global roles and ownership). This view resolves all permission inheritance into a flat list.

How to Use Secure Views

Filtering by User

The secure views contain one row per user per object. To see what a specific user can access, filter by their USER_ID:

-- What tables can user 42 see? (data source permission)
SELECT
    TABLE_NAME,
    SCHEMA_NAME,
    DATA_SOURCE_NAME,
    TRUST_SCORE,
    ACCESS_TYPE
FROM REPOSITORY.VW_SECURE_DATASET_TABLES
WHERE USER_ID = 42
ORDER BY DATA_SOURCE_NAME, FULL_PATH;

-- What tests can user 42 see? (project permission)
SELECT
    TEST_NAME,
    PROJECT_NAME,
    TEST_TYPE,
    OVERALL_RESULT_STATUS,
    ACCESS_TYPE
FROM REPOSITORY.VW_SECURE_DATASET_TESTS
WHERE USER_ID = 42
ORDER BY PROJECT_NAME, TEST_NAME;

-- What test executions can user 42 see? (requires both project + data source)
SELECT
    TEST_NAME,
    PROJECT_NAME,
    RESULT,
    DATE_COMPLETED,
    TEST_DATA_SOURCE
FROM REPOSITORY.VW_SECURE_DATASET_TEST_EXECUTIONS
WHERE USER_ID = 42
    AND LATEST_TEST_EXECUTION_INDEX = 1
ORDER BY DATE_COMPLETED DESC;

Finding a User ID

SELECT DISTINCT
    USER_ID,
    USER_LOGIN_NAME,
    USER_FIRST_NAME,
    USER_LAST_NAME,
    USER_EMAIL
FROM REPOSITORY.VW_DATASET_EFFECTIVE_USER_PERMISSIONS
WHERE USER_EMAIL = 'john.smith@company.com';

Example Queries

Audit: Which Users Can See a Specific Data Source

SELECT
    USER_ID,
    ACCESS_TYPE
FROM REPOSITORY.VW_SECURE_USER_DATA_SOURCE_ACCESS
WHERE META_DATA_SOURCE_ID = 28
ORDER BY USER_ID;

Audit: Which Users Are Members of a Project

SELECT
    USER_ID,
    ACCESS_TYPE
FROM REPOSITORY.VW_SECURE_USER_PROJECT_ACCESS
WHERE PROJECT_ID = 5
ORDER BY USER_ID;

Audit: Who Has Results Access (Both Project + Data Source)

SELECT
    r.USER_ID,
    r.PROJECT_ID,
    r.META_DATA_SOURCE_ID,
    p.USER_LOGIN_NAME,
    p.USER_EMAIL
FROM REPOSITORY.VW_SECURE_USER_RESULTS_ACCESS r
JOIN REPOSITORY.VW_DATASET_EFFECTIVE_USER_PERMISSIONS p
    ON p.USER_ID = r.USER_ID
    AND p.OBJECT_TYPE = 'Project'
    AND p.OBJECT_ID = r.PROJECT_ID
WHERE r.PROJECT_ID = 5
GROUP BY r.USER_ID, r.PROJECT_ID, r.META_DATA_SOURCE_ID,
    p.USER_LOGIN_NAME, p.USER_EMAIL;

Permission Gap: Users with Project Access but Missing Data Source Access

-- Find users who can see tests but not the underlying metadata
SELECT
    pa.USER_ID,
    pa.PROJECT_ID,
    pds.META_DATA_SOURCE_ID,
    mds.NAME AS DATA_SOURCE_NAME
FROM REPOSITORY.VW_SECURE_USER_PROJECT_ACCESS pa
CROSS JOIN PROJECT_DATA_SOURCES pds
INNER JOIN META_DATA_SOURCES mds ON mds.ID = pds.META_DATA_SOURCE_ID
WHERE pds.PROJECT_ID = pa.PROJECT_ID
    AND pds.IS_ACTIVE = TRUE
    AND NOT EXISTS (
        SELECT 1
        FROM REPOSITORY.VW_SECURE_USER_DATA_SOURCE_ACCESS dsa
        WHERE dsa.USER_ID = pa.USER_ID
            AND dsa.META_DATA_SOURCE_ID = pds.META_DATA_SOURCE_ID
    );

Compare Visibility Across Permission Tiers

-- For user 42: what can they see at each tier?
SELECT 'Data Sources (metadata)' AS TIER, COUNT(DISTINCT DATA_SOURCE_ID) AS COUNT
FROM REPOSITORY.VW_SECURE_DATASET_TABLES WHERE USER_ID = 42

UNION ALL

SELECT 'Projects (tests/jobs)', COUNT(DISTINCT PROJECT_ID)
FROM REPOSITORY.VW_SECURE_DATASET_TESTS WHERE USER_ID = 42

UNION ALL

SELECT 'Results (executions)', COUNT(*)
FROM REPOSITORY.VW_SECURE_DATASET_TEST_EXECUTIONS
WHERE USER_ID = 42 AND LATEST_TEST_EXECUTION_INDEX = 1;

Data Sources a User Cannot Access

SELECT DISTINCT t.DATA_SOURCE_NAME, t.DATA_SOURCE_ID
FROM REPOSITORY.VW_DATASET_TABLES t
WHERE NOT EXISTS (
    SELECT 1
    FROM REPOSITORY.VW_SECURE_USER_DATA_SOURCE_ACCESS sec
    WHERE sec.META_DATA_SOURCE_ID = t.DATA_SOURCE_ID
        AND sec.USER_ID = 42
);

Users with Global Admin Access

SELECT DISTINCT
    USER_LOGIN_NAME,
    USER_EMAIL,
    USER_DATE_LAST_LOGIN,
    PERMISSION_NAME
FROM REPOSITORY.VW_DATASET_EFFECTIVE_USER_PERMISSIONS
WHERE PERMISSION_NAME IN ('Global Admin', 'Global Data Source Admin')
ORDER BY USER_LOGIN_NAME;

Important Considerations

Performance

Secure views perform a join to the access resolution views on every query. For large environments, this adds overhead. Always filter by USER_ID early.

Row Multiplication

Because secure views contain one copy of each row per authorized user, queries without a USER_ID filter return significantly more rows than the base views. Always filter by USER_ID unless you're intentionally analyzing cross-user access patterns.

ViewMetadata vs ViewTestResults

These are different data source permissions:

• ViewMetadata — grants visibility into the metadata catalog (schemas, tables, columns, profiles). Used by the metadata-filtered secure views.
• ViewTestResults — grants visibility into test execution results for that data source. Used (in combination with project access) by the results-filtered secure views.

A user can have ViewMetadata without ViewTestResults (they can see the catalog but not execution results), or vice versa.

When to Use Base vs Secure Views

Databricks Notes

• Replace DATEADD(day, -30, CURRENT_DATE) with DATE_ADD(CURRENT_DATE(), -30)
• The permission model and view structure is identical between Snowflake and Databricks
• In Databricks, you may additionally layer Unity Catalog row-level security on top of these views for native enforcement