PlainID Snowflake Policy Orchestration

# Objects automatically discovered during POP initialization:

DATABASES
├── Schemas
├── Tables
│   ├── Columns (with data types)
│   └── Row Access Policies
├── Views
│   ├── Columns
│   └── Access Policies
├── External Functions
└── Warehouses

SECURITY OBJECTS
├── Roles (System & Custom)
├── Users
├── Masking Policies
│   ├── Standard Masking Policies
│   └── Tag-Based Masking Policies
├── Row Access Policies
├── Network Policies
└── Tags (for Tag-Based Security)

METADATA
├── Tags
├── Classifications
├── Policy Associations
└── Data Lineage

# Example: Tag-Based Masking Policy Discovery
-- Snowflake Tag-Based Policy
CREATE TAG pii_classification;
CREATE MASKING POLICY tag_based_pii_mask AS (val STRING)
RETURNS STRING ->
  CASE 
    WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('pii_classification') = 'SENSITIVE'
      AND CURRENT_ROLE() NOT IN ('DATA_STEWARD')
    THEN '***MASKED***'
    ELSE val
  END;

-- Apply tag to columns
ALTER TABLE customer_data MODIFY COLUMN ssn SET TAG pii_classification = 'SENSITIVE';
ALTER TAG pii_classification SET MASKING POLICY tag_based_pii_mask;

-- Create PlainID integration role
CREATE ROLE PLAINID_ORCHESTRATOR;

-- Grant necessary privileges
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE PLAINID_ORCHESTRATOR;
GRANT USAGE ON ALL DATABASES IN ACCOUNT TO ROLE PLAINID_ORCHESTRATOR;
GRANT USAGE ON ALL SCHEMAS IN DATABASE TO ROLE PLAINID_ORCHESTRATOR;
GRANT SELECT ON ALL TABLES IN SCHEMA TO ROLE PLAINID_ORCHESTRATOR;
GRANT SELECT ON ALL VIEWS IN SCHEMA TO ROLE PLAINID_ORCHESTRATOR;

-- Grant policy management privileges
-- Option 1: Grant on specific schemas
GRANT CREATE ROW ACCESS POLICY ON SCHEMA PUBLIC TO ROLE PLAINID_ORCHESTRATOR;
GRANT CREATE MASKING POLICY ON SCHEMA PUBLIC TO ROLE PLAINID_ORCHESTRATOR;

-- Option 2: Grant on all schemas in database (recommended)
GRANT CREATE ROW ACCESS POLICY ON ALL SCHEMAS IN DATABASE TO ROLE PLAINID_ORCHESTRATOR;
GRANT CREATE MASKING POLICY ON ALL SCHEMAS IN DATABASE TO ROLE PLAINID_ORCHESTRATOR;

-- Grant apply privileges at account level
GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE PLAINID_ORCHESTRATOR;
GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE PLAINID_ORCHESTRATOR;

-- Create service user
CREATE USER PLAINID_SERVICE_USER
  PASSWORD = 'secure_password'
  DEFAULT_ROLE = PLAINID_ORCHESTRATOR;

GRANT ROLE PLAINID_ORCHESTRATOR TO USER PLAINID_SERVICE_USER;

{
  "displayName": "Snowflake Production POP",
  "connectionSettings": {
    "account": "mycompany.snowflakecomputing.com",
    "warehouse": "COMPUTE_WH",
    "database": "PRODUCTION",
    "authenticationType": "password",
    "username": "PLAINID_SERVICE_USER",
    "password": "${ENCRYPTED_PASSWORD}"
  },
  "discoveryScope": {
    "databases": ["PRODUCTION", "ANALYTICS"],
    "schemas": ["PUBLIC", "SENSITIVE", "REPORTS"],
    "includeMaskingPolicies": true,
    "includeRowAccessPolicies": true,
    "includeRoles": true
  },
  "syncSettings": {
    "mode": "learn",  // Always start with "learn" mode for initial discovery
    "syncInterval": "hourly",
    "conflictResolution": "plainid_wins"  // Options: "plainid_wins", "snowflake_wins", "manual"
  }
}

-- Verify service account permissions in Snowflake
SHOW GRANTS TO ROLE PLAINID_ORCHESTRATOR;
SHOW GRANTS TO USER PLAINID_SERVICE_USER;

-- Test warehouse access
USE WAREHOUSE COMPUTE_WH;
SELECT CURRENT_WAREHOUSE(), CURRENT_USER(), CURRENT_ROLE();

# Trigger discovery manually from Orchestration Workspace
1. Navigate to Snowflake POP card
2. Click three dots menu (⋮)
3. Select "Discover Now"
4. Monitor progress in Activity Log

# Discovery API endpoint (for automation)
POST /api/v1/orchestration/pop/{popId}/discover
{
  "scope": "full",  // or "incremental"
  "notify": true
}

{
  "identity": {
    "type": "user",
    "id": "snowflake_user@company.com",
    "attributes": {
      "department": "Finance",
      "clearance_level": 2,
      "region": "US_EAST"
    }
  },
  "scope": {
    "name": "snowflake-production"
  },
  "assetType": {
    "type": "table",
    "database": "ANALYTICS",
    "schema": "SALES",
    "table": "REVENUE_DATA"
  },
  "action": "SELECT",
  "context": {
    "warehouse": "COMPUTE_WH",
    "role": "ANALYST",
    "session_id": "sf_session_12345"
  }
}

{
  "decision": "PERMIT",
  "nativePolicies": {
    "rowAccessPolicy": {
      "name": "finance_region_policy",
      "sql": "region IN ('US_EAST', 'US_CENTRAL')"
    },
    "maskingPolicies": [
      {
        "column": "ssn",
        "policy": "pii_mask_policy",
        "function": "REGEXP_REPLACE(val, '[0-9]', 'X')"
      },
      {
        "column": "salary",
        "policy": "salary_mask_policy", 
        "function": "CASE WHEN current_role() = 'HR' THEN val ELSE 0 END"
      }
    ]
  },
  "deploymentInstructions": {
    "applyRowPolicy": "ALTER TABLE analytics.sales.revenue_data SET ROW ACCESS POLICY finance_region_policy ON (region)",
    "applyMaskingPolicies": [
      "ALTER TABLE analytics.sales.revenue_data MODIFY COLUMN ssn SET MASKING POLICY pii_mask_policy",
      "ALTER TABLE analytics.sales.revenue_data MODIFY COLUMN salary SET MASKING POLICY salary_mask_policy"
    ]
  }
}

# Activity Log Entry Example
{
  "timestamp": "2024-12-15T10:30:00Z",
  "popId": "snowflake-prod-pop",
  "action": "POLICY_DEPLOYED",
  "details": {
    "policyName": "revenue_access_policy",
    "targetObject": "ANALYTICS.SALES.REVENUE",
    "deploymentStatus": "SUCCESS",
    "changedBy": "admin@company.com",
    "approvedBy": "security_team",
    "snowflakeResponse": {
      "status": "APPLIED",
      "affectedRows": 1500000
    }
  }
}

# Enable debug logging for POP
{
  "popId": "snowflake-prod-pop",
  "settings": {
    "logging": {
      "level": "DEBUG",
      "includeSnowflakeQueries": true,
      "logPolicyTranslations": true,
      "capturePerformanceMetrics": true
    }
  }
}