Interview Question

"Design a security model for a multi-tenant data platform where different departments can only see their own data, and sensitive columns like SSN and salary are masked for non-privileged users."

Role-Based Access Control (RBAC)

Snowflake uses a hierarchical RBAC model where privileges are granted to roles, and roles are granted to users or other roles.

Role Hierarchy

Creating RBAC Structure

-- 1. Create custom roles
CREATE ROLE data_admin;
CREATE ROLE data_engineer;
CREATE ROLE analyst;
CREATE ROLE etl_developer;
CREATE ROLE read_only;

-- 2. Grant roles to users
GRANT ROLE data_admin TO USER john_doe;
GRANT ROLE analyst TO USER jane_smith;

-- 3. Create role hierarchy
GRANT ROLE data_engineer TO ROLE data_admin;
GRANT ROLE analyst TO ROLE data_admin;
GRANT ROLE etl_developer TO ROLE data_engineer;
GRANT ROLE read_only TO ROLE analyst;

-- 4. Grant database privileges
GRANT USAGE ON DATABASE company_db TO ROLE data_engineer;
GRANT USAGE ON DATABASE company_db TO ROLE analyst;
GRANT CREATE SCHEMA ON DATABASE company_db TO ROLE data_engineer;

-- 5. Grant schema privileges
GRANT USAGE ON SCHEMA company_db.public TO ROLE data_engineer;
GRANT CREATE TABLE ON SCHEMA company_db.public TO ROLE data_engineer;
GRANT SELECT ON ALL TABLES IN SCHEMA company_db.public TO ROLE analyst;

-- 6. Grant table privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE company_db.public.orders TO ROLE data_engineer;
GRANT SELECT ON TABLE company_db.public.orders TO ROLE analyst;

-- 7. Grant future grants (auto-apply to new objects)
GRANT SELECT ON FUTURE TABLES IN SCHEMA company_db.public TO ROLE analyst;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE company_db TO ROLE analyst;

-- 8. Check role hierarchy
SELECT 
    grantee_name,
    granted_role,
    granted_by,
    created_on
FROM snowflake.account_usage.grants_to_roles
WHERE granted_role IN ('DATA_ENGINEER', 'ANALYST', 'ETL_DEVELOPER')
ORDER BY granted_role, grantee_name;

Dynamic Data Masking

Data masking transforms sensitive data at query time based on the user's role.

Column-Level Masking

-- 1. Create masking policy for email
CREATE OR REPLACE MASKING POLICY email_mask 
AS (val VARCHAR) RETURNS VARCHAR ->
    CASE
        WHEN CURRENT_ROLE() IN ('DATA_ADMIN', 'SECURITY_ADMIN') THEN val
        WHEN CURRENT_ROLE() = 'ANALYST' THEN REGEXP_REPLACE(val, '.', '*', 2)
        ELSE '***MASKED***'
    END;

-- 2. Apply masking policy to column
ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;

-- 3. Create masking policy for SSN
CREATE OR REPLACE MASKING POLICY ssn_mask 
AS (val VARCHAR) RETURNS VARCHAR ->
    CASE
        WHEN CURRENT_ROLE() IN ('DATA_ADMIN', 'HR_ADMIN') THEN val
        ELSE CONCAT('XXX-XX-', RIGHT(val, 4))
    END;

-- 4. Apply SSN masking
ALTER TABLE employees MODIFY COLUMN ssn SET MASKING POLICY ssn_mask;

-- 5. Create masking policy for salary
CREATE OR REPLACE MASKING POLICY salary_mask 
AS (val NUMBER) RETURNS NUMBER ->
    CASE
        WHEN CURRENT_ROLE() IN ('DATA_ADMIN', 'HR_ADMIN') THEN val
        WHEN CURRENT_ROLE() = 'MANAGER' THEN ROUND(val, -3)  -- Round to nearest 1000
        ELSE NULL
    END;

-- 6. Apply salary masking
ALTER TABLE employees MODIFY COLUMN salary SET MASKING POLICY salary_mask;

-- Test masking
-- As DATA_ADMIN (sees full data)
SET ROLE DATA_ADMIN;
SELECT email, ssn, salary FROM customers LIMIT 5;

-- As ANALYST (sees masked data)
SET ROLE ANALYST;
SELECT email, ssn, salary FROM customers LIMIT 5;
-- email: j**@example.com
-- ssn: XXX-XX-1234
-- salary: NULL

Row-Level Security (Row Access Policies)

-- 1. Create a mapping table for department access
CREATE TABLE user_department_access (
    user_name VARCHAR(100),
    department VARCHAR(50),
    access_level VARCHAR(20)
);

INSERT INTO user_department_access VALUES
('john_doe', 'SALES', 'FULL'),
('jane_smith', 'MARKETING', 'FULL'),
('bob_jones', 'ENGINEERING', 'READ_ONLY');

-- 2. Create row access policy
CREATE OR REPLACE ROW ACCESS POLICY department_policy 
AS (department VARCHAR) RETURNS BOOLEAN ->
    CASE
        WHEN CURRENT_ROLE() IN ('DATA_ADMIN', 'SECURITY_ADMIN') THEN TRUE
        WHEN EXISTS (
            SELECT 1 FROM user_department_access 
            WHERE user_name = CURRENT_USER() 
              AND department = department
        ) THEN TRUE
        ELSE FALSE
    END;

-- 3. Apply row access policy to table
ALTER TABLE sales_data ADD ROW ACCESS POLICY department_policy ON (department);

-- 4. Test row-level security
-- As DATA_ADMIN (sees all rows)
SET ROLE DATA_ADMIN;
SELECT COUNT(*) FROM sales_data;  -- Returns all rows

-- As analyst with SALES access
SET ROLE ANALYST;
SELECT COUNT(*) FROM sales_data WHERE department = 'SALES';  -- Returns only SALES rows
SELECT COUNT(*) FROM sales_data WHERE department = 'ENGINEERING';  -- Returns 0 rows

Real-World Scenario: Google

Question: "How do you implement column-level security for a table that has PII data, and different users need different levels of access to the same column?"

Multi-Level Column Security

-- Create table with PII data
CREATE TABLE customer_pii (
    customer_id VARCHAR(100),
    name VARCHAR(200),
    email VARCHAR(200),
    phone VARCHAR(20),
    ssn VARCHAR(11),
    birth_date DATE,
    address VARCHAR(500),
    credit_score NUMBER
);

-- Create multiple masking policies for different sensitivity levels

-- Policy 1: Full masking for unauthorized users
CREATE OR REPLACE MASKING POLICY pii_full_mask 
AS (val VARCHAR) RETURNS VARCHAR ->
    CASE
        WHEN CURRENT_ROLE() IN ('DATA_ADMIN', 'PII_ADMIN') THEN val
        ELSE '***REDACTED***'
    END;

-- Policy 2: Partial masking for authorized analysts
CREATE OR REPLACE MASKING POLICY pii_partial_mask 
AS (val VARCHAR) RETURNS VARCHAR ->
    CASE
        WHEN CURRENT_ROLE() IN ('DATA_ADMIN', 'PII_ADMIN') THEN val
        WHEN CURRENT_ROLE() = 'PII_ANALYST' THEN 
            CONCAT(LEFT(val, 2), REPEAT('*', LENGTH(val) - 4), RIGHT(val, 2))
        ELSE '***REDACTED***'
    END;

-- Policy 3: Numeric masking for credit score
CREATE OR REPLACE MASKING POLICY credit_score_mask 
AS (val NUMBER) RETURNS NUMBER ->
    CASE
        WHEN CURRENT_ROLE() IN ('DATA_ADMIN', 'PII_ADMIN') THEN val
        WHEN CURRENT_ROLE() = 'PII_ANALYST' THEN ROUND(val, -1)  -- Round to nearest 10
        ELSE NULL
    END;

-- Apply policies
ALTER TABLE customer_pii MODIFY COLUMN name SET MASKING POLICY pii_partial_mask;
ALTER TABLE customer_pii MODIFY COLUMN email SET MASKING POLICY pii_partial_mask;
ALTER TABLE customer_pii MODIFY COLUMN phone SET MASKING POLICY pii_full_mask;
ALTER TABLE customer_pii MODIFY COLUMN ssn SET MASKING POLICY pii_full_mask;
ALTER TABLE customer_pii MODIFY COLUMN credit_score SET MASKING POLICY credit_score_mask;

-- Test different access levels
-- PII_ADMIN: sees everything
-- PII_ANALYST: sees partial data
-- Regular ANALYST: sees only redacted data

Encryption & Network Security

-- 1. Check encryption settings
SELECT 
    database_name,
    schema_name,
    table_name,
    encryption_algorithm,
    key_version
FROM information_schema.tables
WHERE table_catalog = 'COMPANY_DB';

-- 2. Network policies
CREATE OR REPLACE NETWORK POLICY corporate_policy
    ALLOWED_IP_LIST = ('203.0.113.0/24', '198.51.100.0/24')
    BLOCKED_IP_LIST = ('192.0.2.0/24')
    COMMENT = 'Corporate office IP ranges';

-- 3. Apply network policy to account
ALTER ACCOUNT SET NETWORK_POLICY = corporate_policy;

-- 4. Create network policy for specific users
CREATE OR REPLACE NETWORK POLICY analytics_policy
    ALLOWED_IP_LIST = ('10.0.0.0/8');

-- 5. Check network policy usage
SELECT 
    policy_name,
    comment,
    created_on
FROM information_schema.network_policies;

-- 6. Audit network access
SELECT 
    user_name,
    client_ip,
    event_timestamp,
    event_type
FROM snowflake.account_usage.login_history
WHERE event_timestamp >= DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY event_timestamp DESC;

Best Practices