Third-Party Integration Security Weakness

Vendor Access Management Schema:

CREATE TABLE vendor_registry (
    id SERIAL PRIMARY KEY,
    vendor_name VARCHAR(255) UNIQUE NOT NULL,
    vendor_type VARCHAR(100),
    risk_rating INTEGER CHECK (risk_rating BETWEEN 1 AND 10),
    security_assessment_date DATE,
    security_assessment_status VARCHAR(50),
    contract_start_date DATE,
    contract_end_date DATE,
    primary_contact_email VARCHAR(255),
    security_contact_email VARCHAR(255)
);

CREATE TABLE vendor_access_grants (
    id SERIAL PRIMARY KEY,
    vendor_id INTEGER REFERENCES vendor_registry(id),
    access_type VARCHAR(50) CHECK (access_type IN ('api', 'database', 'sftp', 'web_portal')),
    resource_name VARCHAR(255),
    permission_level VARCHAR(50),
    granted_date DATE NOT NULL,
    expires_date DATE NOT NULL,
    approved_by VARCHAR(255) NOT NULL,
    business_justification TEXT NOT NULL,
    CHECK (expires_date > granted_date)
);

CREATE TABLE vendor_access_reviews (
    id SERIAL PRIMARY KEY,
    vendor_id INTEGER REFERENCES vendor_registry(id),
    review_date DATE NOT NULL,
    reviewer VARCHAR(255) NOT NULL,
    access_still_needed BOOLEAN NOT NULL,
    findings TEXT,
    actions_taken TEXT
);

API Gateway for Vendors:

CREATE TABLE vendor_api_keys (
    id SERIAL PRIMARY KEY,
    vendor_id INTEGER REFERENCES vendor_registry(id),
    api_key_hash VARCHAR(64) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    last_used_at TIMESTAMP WITH TIME ZONE,
    allowed_endpoints TEXT[],
    rate_limit_per_hour INTEGER DEFAULT 1000,
    allowed_ip_ranges INET[],
    is_active BOOLEAN DEFAULT true
);

CREATE TABLE vendor_api_usage (
    id BIGSERIAL PRIMARY KEY,
    api_key_hash VARCHAR(64) REFERENCES vendor_api_keys(api_key_hash),
    endpoint VARCHAR(255),
    request_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    response_code INTEGER,
    data_accessed TEXT[],
    source_ip INET
);

-- Detect suspicious vendor activity
CREATE OR REPLACE FUNCTION check_vendor_anomaly(
    p_api_key_hash VARCHAR,
    p_endpoint VARCHAR,
    p_source_ip INET
) RETURNS BOOLEAN AS $
DECLARE
    v_key vendor_api_keys;
    v_recent_requests INTEGER;
BEGIN
    SELECT * INTO v_key FROM vendor_api_keys WHERE api_key_hash = p_api_key_hash;
    
    -- Check IP allowlist
    IF v_key.allowed_ip_ranges IS NOT NULL THEN
        IF NOT p_source_ip <<= ANY(v_key.allowed_ip_ranges) THEN
            RETURN FALSE; -- Suspicious: IP not in allowlist
        END IF;
    END IF;
    
    -- Check endpoint allowlist
    IF v_key.allowed_endpoints IS NOT NULL THEN
        IF NOT p_endpoint = ANY(v_key.allowed_endpoints) THEN
            RETURN FALSE; -- Suspicious: Endpoint not allowed
        END IF;
    END IF;
    
    -- Check rate limit
    SELECT COUNT(*) INTO v_recent_requests
    FROM vendor_api_usage
    WHERE api_key_hash = p_api_key_hash
    AND request_time > NOW() - INTERVAL '1 hour';
    
    IF v_recent_requests >= v_key.rate_limit_per_hour THEN
        RETURN FALSE; -- Suspicious: Rate limit exceeded
    END IF;
    
    RETURN TRUE; -- All checks passed
END;
$ LANGUAGE plpgsql;

Data Isolation for Vendors:

-- Create isolated schemas for vendor access
CREATE SCHEMA vendor_accessible;

-- Create views with limited data
CREATE VIEW vendor_accessible.customer_summary AS
SELECT 
    customer_id,
    created_date,
    status,
    -- Exclude sensitive fields like SSN, payment info
    COUNT(*) OVER() as total_customers
FROM customers
WHERE data_sharing_consent = true;

-- Row-level security for vendor access
ALTER TABLE vendor_accessible.customer_summary ENABLE ROW LEVEL SECURITY;

CREATE POLICY vendor_data_access ON vendor_accessible.customer_summary
FOR SELECT
USING (
    EXISTS (
        SELECT 1 FROM vendor_data_permissions vdp
        JOIN vendor_api_keys vak ON vdp.vendor_id = vak.vendor_id
        WHERE vak.api_key_hash = current_setting('app.current_api_key')
        AND vdp.customer_segment = customer_segment
    )
);

Vendor Activity Monitoring:

CREATE TABLE vendor_activity_alerts (
    id SERIAL PRIMARY KEY,
    vendor_id INTEGER REFERENCES vendor_registry(id),
    alert_type VARCHAR(100),
    severity VARCHAR(20),
    details JSONB,
    detected_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    resolved_at TIMESTAMP WITH TIME ZONE,
    resolved_by VARCHAR(255)
);

-- Real-time monitoring view
CREATE VIEW vendor_security_dashboard AS
SELECT 
    v.vendor_name,
    COUNT(DISTINCT vau.endpoint) as endpoints_accessed,
    COUNT(vau.id) as total_requests_24h,
    MAX(vau.request_time) as last_activity,
    COUNT(DISTINCT vau.source_ip) as unique_ips,
    COALESCE(
        (SELECT COUNT(*) FROM vendor_activity_alerts vaa 
         WHERE vaa.vendor_id = v.id 
         AND vaa.resolved_at IS NULL), 0
    ) as open_alerts
FROM vendor_registry v
LEFT JOIN vendor_api_keys vak ON v.id = vak.vendor_id
LEFT JOIN vendor_api_usage vau ON vak.api_key_hash = vau.api_key_hash
    AND vau.request_time > NOW() - INTERVAL '24 hours'
GROUP BY v.id, v.vendor_name
ORDER BY total_requests_24h DESC;

Additional Best Practices:

• Implement OAuth2/SAML instead of shared credentials
• Use temporary, scoped credentials (AWS STS style)
• Regular vendor security assessments
• Encrypt data before sharing with vendors
• Implement vendor access kill switches
• Separate environments for vendor testing