Setup script for connecting Claude.ai to Snowflake via native MCP connector with OAuth. Includes role configuration, troubleshooting guide, and known limitations (read-only not enforceable due to Claude.ai OAuth scope limitation).

claude-snowflake-mcp-integration-guide.sql

-- ============================================================================
-- CLAUDE.AI SNOWFLAKE MCP CONNECTOR - SETUP SCRIPT
-- ============================================================================
-- This script configures Snowflake for Claude.ai's native MCP connector
-- with OAuth authentication.
--
-- Run as ACCOUNTADMIN in Snowflake (Snowsight or SnowSQL)
-- 
-- Replace all <placeholders> with your actual values before running.
-- ============================================================================

-- ============================================================================
-- ⚠️  IMPORTANT LIMITATION - READ THIS FIRST
-- ============================================================================
-- This setup creates a read-only ROLE, but does NOT enforce read-only ACCESS.
-- 
-- Due to a Claude.ai connector limitation, OAUTH_USE_SECONDARY_ROLES must be 
-- set to 'IMPLICIT' for the connection to work. This means users retain 
-- permissions from ALL their roles (primary + secondary), not just the 
-- read-only role.
--
-- If a user has write permissions via any of their Snowflake roles, they
-- will have write permissions through Claude.
--
-- We have submitted a feature request to Anthropic to support passing
-- scope=session:role:<role_name> in OAuth requests, which would enable
-- true read-only enforcement.
-- ============================================================================

USE ROLE ACCOUNTADMIN;

-- ============================================================================
-- STEP 1: Create the Read-Only Role
-- ============================================================================
-- This role will only have SELECT permissions, but see limitation above.

CREATE ROLE IF NOT EXISTS CLAUDE_MCP_READONLY
    COMMENT = 'Read-only role for Claude MCP integration';

-- ============================================================================
-- STEP 2: Grant Role to PUBLIC (Auto-Assignment to All Users)
-- ============================================================================
-- Granting to PUBLIC automatically gives this role to all existing AND 
-- future users. No need to manually grant to individual users.

GRANT ROLE CLAUDE_MCP_READONLY TO ROLE PUBLIC;

-- ============================================================================
-- STEP 3: Grant Warehouse Usage
-- ============================================================================

GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE CLAUDE_MCP_READONLY;

-- ============================================================================
-- STEP 4: Grant Read Access to Databases
-- ============================================================================
-- Repeat this block for each database you want Claude to access.
-- Replace <database_name> with your actual database name.

-- ----- Database: <database_name> -----
GRANT USAGE ON DATABASE <database_name> TO ROLE CLAUDE_MCP_READONLY;
GRANT USAGE ON ALL SCHEMAS IN DATABASE <database_name> TO ROLE CLAUDE_MCP_READONLY;
GRANT SELECT ON ALL TABLES IN DATABASE <database_name> TO ROLE CLAUDE_MCP_READONLY;
GRANT SELECT ON ALL VIEWS IN DATABASE <database_name> TO ROLE CLAUDE_MCP_READONLY;

-- Future grants (automatically apply to newly created objects)
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <database_name> TO ROLE CLAUDE_MCP_READONLY;
GRANT SELECT ON FUTURE TABLES IN DATABASE <database_name> TO ROLE CLAUDE_MCP_READONLY;
GRANT SELECT ON FUTURE VIEWS IN DATABASE <database_name> TO ROLE CLAUDE_MCP_READONLY;

-- ============================================================================
-- STEP 5: Create the Security Integration (OAuth)
-- ============================================================================

CREATE OR REPLACE SECURITY INTEGRATION CLAUDE_MCP_INTEGRATION
    TYPE = OAUTH
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
    OAUTH_REDIRECT_URI = 'https://claude.ai/api/mcp/auth_callback'
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 7776000  -- 90 days
    OAUTH_USE_SECONDARY_ROLES = 'IMPLICIT'  -- Required for Claude.ai (see limitation above)
    PRE_AUTHORIZED_ROLES_LIST = ()          -- Empty = allow any non-blocked role
    BLOCKED_ROLES_LIST = ('ACCOUNTADMIN', 'ORGADMIN', 'SECURITYADMIN', 'SYSADMIN', 'USERADMIN')
    ENABLED = TRUE
    COMMENT = 'OAuth integration for Claude.ai MCP connector';

-- ============================================================================
-- STEP 6: Create the MCP Server
-- ============================================================================
-- The MCP server exposes tools that Claude can use.
-- Replace <database_name> and <schema_name> with your values.

CREATE OR REPLACE MCP SERVER <database_name>.<schema_name>.CLAUDE_MCP_SERVER
  FROM SPECIFICATION $$
    tools:
      - title: "SQL Execution Tool"
        name: "sql_exec_tool"
        type: "SYSTEM_EXECUTE_SQL"
        description: "Execute SQL statements against Snowflake."
  $$;


-- ============================================================================
-- STEP 7: Retrieve Credentials
-- ============================================================================
-- Save these securely - you'll need them to configure Claude.ai

-- Get OAuth client credentials
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('CLAUDE_MCP_INTEGRATION') AS oauth_credentials;

-- Get MCP endpoint URL
SELECT 'https://' || CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() || 
       '.snowflakecomputing.com/api/v2/databases/<database_name>/schemas/<schema_name>/mcp-servers/CLAUDE_MCP_SERVER' 
       AS mcp_endpoint_url;

-- ============================================================================
-- STEP 8: Verification Queries
-- ============================================================================

-- Verify role was created
SHOW ROLES LIKE 'CLAUDE_MCP_READONLY';

-- Verify role is granted to PUBLIC
SHOW GRANTS OF ROLE CLAUDE_MCP_READONLY;

-- Verify grants on the role (should show SELECT only)
SHOW GRANTS TO ROLE CLAUDE_MCP_READONLY;

-- Verify security integration
DESCRIBE INTEGRATION CLAUDE_MCP_INTEGRATION;

-- Verify MCP server
DESCRIBE MCP SERVER <database_name>.<schema_name>.CLAUDE_MCP_SERVER;

-- ============================================================================
-- STEP 9: Configure Claude.ai
-- ============================================================================
-- 1. Go to Claude.ai Settings > Connectors > Snowflake
-- 2. Enter your Snowflake server URL (from Step 7 output)
-- 3. Enter the OAuth Client ID (from Step 7 output)
-- 4. Enter the OAuth Client Secret (from Step 7 output)
-- 5. Authenticate with your Snowflake credentials
-- 6. Test the connection
-- ============================================================================


-- ============================================================================
-- TROUBLESHOOTING GUIDE
-- ============================================================================
--
-- ISSUE: "Invalid consent request" error
-- CAUSE: User's default_role isn't compatible with the integration
-- FIX:   Ensure PRE_AUTHORIZED_ROLES_LIST = () (empty list)
--
-- ISSUE: Connection fails silently after Snowflake authentication
-- CAUSE: OAUTH_USE_SECONDARY_ROLES set to 'NONE'
-- FIX:   Must use 'IMPLICIT' - Claude.ai doesn't support 'NONE'
--
-- ISSUE: MCP Server CREATE syntax error ("unexpected 'INTEGRATION'" or "unexpected 'AS'")
-- CAUSE: Wrong syntax for CREATE MCP SERVER
-- FIX:   Use SPEC = '{"version":1,...}' not AS or SECURITY INTEGRATION clause
--
-- ISSUE: Users can still INSERT/UPDATE/DELETE through Claude
-- CAUSE: IMPLICIT secondary roles grants all user's role permissions
-- FIX:   None available - this is a Claude.ai limitation (feature request submitted)
--
-- ============================================================================


-- ============================================================================
-- OPTIONAL: Delegated Authorization (Pre-approve consent for users)
-- ============================================================================
-- This pre-authorizes users so they don't see a consent screen.
-- It also sets CLAUDE_MCP_READONLY as the primary role (but secondary roles
-- are still active with IMPLICIT).
--
-- Run for each user:
-- ALTER USER <username> ADD DELEGATED AUTHORIZATION 
--     OF ROLE CLAUDE_MCP_READONLY 
--     TO SECURITY INTEGRATION CLAUDE_MCP_INTEGRATION;
--
-- Verify delegated authorizations:
-- SHOW DELEGATED AUTHORIZATIONS TO SECURITY INTEGRATION CLAUDE_MCP_INTEGRATION;
-- ============================================================================


-- ============================================================================
-- USEFUL DIAGNOSTIC QUERIES (Run from Claude after connecting)
-- ============================================================================
--
-- Check current session context:
-- SELECT CURRENT_USER(), CURRENT_ROLE(), CURRENT_SECONDARY_ROLES();
--
-- Check effective permissions:
-- SELECT CURRENT_AVAILABLE_ROLES();
--
-- ============================================================================