Snowflake Permissions Script

-- =============================================================================
-- AnomalyArmor Snowflake Permissions Setup
-- =============================================================================
-- WHAT THIS GRANTS:
-- - USAGE on database: View database metadata
-- - USAGE on warehouse: Execute queries
-- - USAGE on schemas: View schema metadata
-- - SELECT on tables/views: Read data for freshness checks
--
-- WHAT THIS DOES NOT GRANT:
-- - INSERT, UPDATE, DELETE: No data modification
-- - CREATE: No table/schema creation
-- - ADMIN privileges: No user/role management
-- =============================================================================

-- Step 1: Create the read-only role
CREATE ROLE IF NOT EXISTS ANOMALYARMOR_ROLE;

-- Step 2: Grant database access
GRANT USAGE ON DATABASE YOUR_DATABASE TO ROLE ANOMALYARMOR_ROLE;

-- Step 3: Grant warehouse access
GRANT USAGE ON WAREHOUSE YOUR_WAREHOUSE TO ROLE ANOMALYARMOR_ROLE;

-- Step 4: Grant schema access (all schemas in database)
GRANT USAGE ON ALL SCHEMAS IN DATABASE YOUR_DATABASE TO ROLE ANOMALYARMOR_ROLE;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE YOUR_DATABASE TO ROLE ANOMALYARMOR_ROLE;

-- Step 5: Grant read access to tables
GRANT SELECT ON ALL TABLES IN DATABASE YOUR_DATABASE TO ROLE ANOMALYARMOR_ROLE;
GRANT SELECT ON FUTURE TABLES IN DATABASE YOUR_DATABASE TO ROLE ANOMALYARMOR_ROLE;

-- Step 6: Grant read access to views
GRANT SELECT ON ALL VIEWS IN DATABASE YOUR_DATABASE TO ROLE ANOMALYARMOR_ROLE;
GRANT SELECT ON FUTURE VIEWS IN DATABASE YOUR_DATABASE TO ROLE ANOMALYARMOR_ROLE;

-- Step 7: Create the user
CREATE USER IF NOT EXISTS ANOMALYARMOR_USER
  PASSWORD = 'your_secure_password'
  DEFAULT_ROLE = ANOMALYARMOR_ROLE
  DEFAULT_WAREHOUSE = YOUR_WAREHOUSE
  MUST_CHANGE_PASSWORD = FALSE;

-- Step 8: Assign role to user
GRANT ROLE ANOMALYARMOR_ROLE TO USER ANOMALYARMOR_USER;

-- Remove database-wide grants
REVOKE USAGE ON ALL SCHEMAS IN DATABASE YOUR_DATABASE FROM ROLE ANOMALYARMOR_ROLE;
REVOKE SELECT ON ALL TABLES IN DATABASE YOUR_DATABASE FROM ROLE ANOMALYARMOR_ROLE;

-- Grant to specific schemas only
GRANT USAGE ON SCHEMA YOUR_DATABASE.RAW TO ROLE ANOMALYARMOR_ROLE;
GRANT USAGE ON SCHEMA YOUR_DATABASE.STAGING TO ROLE ANOMALYARMOR_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA YOUR_DATABASE.RAW TO ROLE ANOMALYARMOR_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA YOUR_DATABASE.STAGING TO ROLE ANOMALYARMOR_ROLE;

# Generate key pair locally
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key.p8 -nocrypt
openssl rsa -in snowflake_key.p8 -pubout -out snowflake_key.pub

-- Assign public key to user (replace with your actual public key)
ALTER USER ANOMALYARMOR_USER SET RSA_PUBLIC_KEY='MIIBIjANBgkqhki...';

-- Run as ANOMALYARMOR_USER to verify access

-- Test 1: Can access information_schema
SELECT table_catalog, table_schema, table_name
FROM YOUR_DATABASE.INFORMATION_SCHEMA.TABLES
WHERE table_schema NOT IN ('INFORMATION_SCHEMA')
LIMIT 5;

-- Test 2: Can see column metadata
SELECT column_name, data_type, is_nullable
FROM YOUR_DATABASE.INFORMATION_SCHEMA.COLUMNS
LIMIT 5;

DROP USER IF EXISTS ANOMALYARMOR_USER;
DROP ROLE IF EXISTS ANOMALYARMOR_ROLE;