🔒 Row-Level Security Implementation¶

-- Create function to filter by user's region
CREATE OR REPLACE FUNCTION sales.region_filter(region STRING)
RETURNS BOOLEAN
RETURN (
    CASE
        -- Admins see all regions
        WHEN is_account_group_member('admin_group') THEN true
        -- Regional users see only their region
        WHEN is_account_group_member('na_sales') AND region = 'North America' THEN true
        WHEN is_account_group_member('eu_sales') AND region = 'Europe' THEN true
        WHEN is_account_group_member('apac_sales') AND region = 'Asia Pacific' THEN true
        ELSE false
    END
);

-- Apply row filter
ALTER TABLE sales.orders
SET ROW FILTER sales.region_filter ON (region);

-- Verify filter is applied
DESCRIBE TABLE EXTENDED sales.orders;

-- Mask PII columns
CREATE OR REPLACE FUNCTION sales.mask_email(email STRING)
RETURNS STRING
RETURN (
    CASE
        WHEN is_account_group_member('pii_access') THEN email
        ELSE CONCAT(LEFT(email, 2), '***@', SPLIT(email, '@')[1])
    END
);

-- Apply column mask
ALTER TABLE sales.customers
ALTER COLUMN email SET MASK sales.mask_email;

-- Test as different users
SELECT current_user();
SELECT * FROM sales.orders LIMIT 10;

-- Verify row filter is working
SELECT region, COUNT(*) FROM sales.orders GROUP BY region;