Implement Security-First Power Query Privacy Levels & VBA Trust Guidance

[sbroenne]

Security-First Implementation: Power Query Privacy & VBA Trust

🔒 Security Review Status: ✅ APPROVED

This issue has undergone comprehensive security review and follows security-first design principles:

• Principle of Least Privilege: Never grant more access than required
• Explicit Consent: Users must understand and approve security changes
• Transparency: Clear explanation of what security settings do and why
• No Hidden Actions: Never modify security settings without user knowledge
• Fail-Safe Defaults: Operations fail safely when security settings are not configured

---

Part 1: Power Query Privacy Levels - Interactive User Consent

Objective

Implement optional privacyLevel parameter that allows users to explicitly set how Excel combines data from different Power Query sources, with mandatory user consent before applying any privacy settings.

Security Principles

• ❌ NEVER auto-apply privacy levels without explicit user consent
• ✅ Always fail safely on first attempt without privacy parameter
• ✅ Educate users about privacy level meanings and security implications
• ✅ LLM as intermediary for conversational consent workflow
• ✅ Explicit in commands - privacy level visible in CLI/MCP parameters

Implementation Requirements

1. Core Layer (src/ExcelMcp.Core/)

Create new result types in Models/ResultTypes.cs:

/// <summary>
/// Power Query privacy level options for data combining
/// </summary>
public enum PowerQueryPrivacyLevel
{
    /// <summary>
    /// Ignores privacy levels, allows combining any data sources (least secure)
    /// </summary>
    None,
    
    /// <summary>
    /// Prevents sharing data with other sources (most secure, recommended for sensitive data)
    /// </summary>
    Private,
    
    /// <summary>
    /// Data can be shared within organization (recommended for internal data)
    /// </summary>
    Organizational,
    
    /// <summary>
    /// Publicly available data sources (appropriate for public APIs)
    /// </summary>
    Public
}

/// <summary>
/// Information about a query's detected privacy level
/// </summary>
public record QueryPrivacyInfo(string QueryName, PowerQueryPrivacyLevel PrivacyLevel);

/// <summary>
/// Result indicating Power Query operation requires privacy level specification
/// </summary>
public record PowerQueryPrivacyErrorResult : OperationResult
{
    /// <summary>
    /// Privacy levels detected in existing queries
    /// </summary>
    public List<QueryPrivacyInfo> ExistingPrivacyLevels { get; init; } = new();
    
    /// <summary>
    /// Recommended privacy level based on existing queries
    /// </summary>
    public PowerQueryPrivacyLevel RecommendedPrivacyLevel { get; init; }
    
    /// <summary>
    /// User-friendly explanation of the recommendation
    /// </summary>
    public string Explanation { get; init; } = "";
    
    /// <summary>
    /// Original error message from Excel
    /// </summary>
    public string OriginalError { get; init; } = "";
}

Update interface Commands/IPowerQueryCommands.cs:

/// <param name="privacyLevel">Optional privacy level for data combining. If not specified and privacy error occurs, operation returns PowerQueryPrivacyErrorResult for user to choose.</param>
Task<OperationResult> Import(string excelPath, string queryName, string mCodePath, PowerQueryPrivacyLevel? privacyLevel = null);

Task<OperationResult> Update(string excelPath, string queryName, string mCodePath, PowerQueryPrivacyLevel? privacyLevel = null);

Task<OperationResult> SetLoadToTable(string excelPath, string queryName, string targetSheet, PowerQueryPrivacyLevel? privacyLevel = null);

Task<OperationResult> SetLoadToDataModel(string excelPath, string queryName, PowerQueryPrivacyLevel? privacyLevel = null);

Task<OperationResult> SetLoadToBoth(string excelPath, string queryName, string targetSheet, PowerQueryPrivacyLevel? privacyLevel = null);

Implement privacy detection in Commands/PowerQueryCommands.cs:

private PowerQueryPrivacyErrorResult DetectPrivacyLevelsAndRecommend(dynamic workbook, string originalError)
{
    var privacyLevels = new List<QueryPrivacyInfo>();
    dynamic queries = workbook.Queries;
    
    // Scan existing queries for privacy level patterns
    for (int i = 1; i <= queries.Count; i++)
    {
        dynamic query = queries.Item(i);
        string mCode = query.Formula;
        
        var detectedLevel = DetectPrivacyLevelFromMCode(mCode);
        if (detectedLevel.HasValue)
        {
            privacyLevels.Add(new QueryPrivacyInfo(query.Name, detectedLevel.Value));
        }
    }
    
    // Determine recommendation based on existing queries
    var recommended = DetermineRecommendedPrivacyLevel(privacyLevels);
    
    return new PowerQueryPrivacyErrorResult
    {
        Success = false,
        ErrorMessage = "Privacy level required to combine data sources",
        ExistingPrivacyLevels = privacyLevels,
        RecommendedPrivacyLevel = recommended,
        Explanation = GeneratePrivacyExplanation(privacyLevels, recommended),
        OriginalError = originalError
    };
}

private PowerQueryPrivacyLevel? DetectPrivacyLevelFromMCode(string mCode)
{
    if (mCode.Contains("Privacy.None()", StringComparison.OrdinalIgnoreCase))
        return PowerQueryPrivacyLevel.None;
    if (mCode.Contains("Privacy.Private()", StringComparison.OrdinalIgnoreCase))
        return PowerQueryPrivacyLevel.Private;
    if (mCode.Contains("Privacy.Organizational()", StringComparison.OrdinalIgnoreCase))
        return PowerQueryPrivacyLevel.Organizational;
    if (mCode.Contains("Privacy.Public()", StringComparison.OrdinalIgnoreCase))
        return PowerQueryPrivacyLevel.Public;
    
    return null;
}

private void ApplyPrivacyLevel(dynamic workbook, PowerQueryPrivacyLevel privacyLevel)
{
    // Set workbook-level privacy setting
    string privacyFormula = privacyLevel switch
    {
        PowerQueryPrivacyLevel.None => "Privacy.None()",
        PowerQueryPrivacyLevel.Private => "Privacy.Private()",
        PowerQueryPrivacyLevel.Organizational => "Privacy.Organizational()",
        PowerQueryPrivacyLevel.Public => "Privacy.Public()",
        _ => throw new ArgumentException($"Unknown privacy level: {privacyLevel}")
    };
    
    // Apply to workbook queries section
    // Implementation depends on Excel COM API for privacy settings
}

Modify operation methods to catch privacy errors:

public async Task<OperationResult> SetLoadToTable(string excelPath, string queryName, string targetSheet, PowerQueryPrivacyLevel? privacyLevel = null)
{
    return ExcelHelper.WithExcel(excelPath, save: true, (excel, workbook) =>
    {
        try
        {
            // Apply privacy level if specified
            if (privacyLevel.HasValue)
            {
                ApplyPrivacyLevel(workbook, privacyLevel.Value);
            }
            
            // Attempt to load query to table
            // ... existing implementation ...
            
            return new OperationResult { Success = true, Message = "Query loaded successfully" };
        }
        catch (COMException ex) when (ex.Message.Contains("Information is needed in order to combine data"))
        {
            // Privacy error detected - return detailed error result
            return DetectPrivacyLevelsAndRecommend(workbook, ex.Message);
        }
    });
}

2. CLI Layer (src/ExcelMcp.CLI/)

Add --privacy-level parameter to PowerQuery commands:

// Commands/PowerQueryCommands.cs
public int SetLoadToTable(string[] args)
{
    // Parse arguments including optional --privacy-level
    string excelPath = args[1];
    string queryName = args[2];
    string targetSheet = args[3];
    PowerQueryPrivacyLevel? privacyLevel = ParsePrivacyLevelFromArgs(args);
    
    var result = await powerQueryCommands.SetLoadToTable(excelPath, queryName, targetSheet, privacyLevel);
    
    // Handle PowerQueryPrivacyErrorResult
    if (result is PowerQueryPrivacyErrorResult privacyError)
    {
        DisplayPrivacyConsentPrompt(privacyError);
        return 1;
    }
    
    return result.Success ? 0 : 1;
}

private void DisplayPrivacyConsentPrompt(PowerQueryPrivacyErrorResult error)
{
    var panel = new Panel(new Markup(
        $"[yellow]Power Query Privacy Level Required[/]\n\n" +
        $"Your query combines data from multiple sources. Excel requires a privacy level to be specified.\n\n" +
        $"[cyan]Existing queries in this workbook:[/]\n" +
        string.Join("\n", error.ExistingPrivacyLevels.Select(q => $"  • {q.QueryName}: {q.PrivacyLevel}")) + "\n\n" +
        $"[cyan]Recommended:[/] {error.RecommendedPrivacyLevel}\n" +
        $"{error.Explanation}\n\n" +
        $"[dim]To proceed, run the command again with:[/]\n" +
        $"  --privacy-level {error.RecommendedPrivacyLevel}\n\n" +
        $"[dim]Or choose a different level:[/]\n" +
        $"  --privacy-level None          (least secure, ignores privacy)\n" +
        $"  --privacy-level Private       (most secure, prevents combining)\n" +
        $"  --privacy-level Organizational (internal data sources)\n" +
        $"  --privacy-level Public        (public data sources)"
    ))
    .Border(TableBorder.Rounded)
    .BorderColor(Color.Yellow)
    .Header("[yellow]⚠ User Consent Required[/]");
    
    AnsiConsole.Write(panel);
}

Support environment variable EXCEL_DEFAULT_PRIVACY_LEVEL:

private PowerQueryPrivacyLevel? ParsePrivacyLevelFromArgs(string[] args)
{
    // Check --privacy-level parameter
    for (int i = 0; i < args.Length - 1; i++)
    {
        if (args[i] == "--privacy-level" && i + 1 < args.Length)
        {
            if (Enum.TryParse<PowerQueryPrivacyLevel>(args[i + 1], ignoreCase: true, out var level))
                return level;
        }
    }
    
    // Check environment variable as fallback
    string? envLevel = Environment.GetEnvironmentVariable("EXCEL_DEFAULT_PRIVACY_LEVEL");
    if (!string.IsNullOrEmpty(envLevel))
    {
        if (Enum.TryParse<PowerQueryPrivacyLevel>(envLevel, ignoreCase: true, out var level))
            return level;
    }
    
    return null;
}

3. MCP Server Layer (src/ExcelMcp.McpServer/)

Add privacyLevel parameter to Tools/ExcelPowerQueryTool.cs:

[McpServerTool(Name = "excel_powerquery")]
public static string ExcelPowerQuery(
    [Required] string action,
    [Required] string excelPath,
    string? queryName = null,
    string? sourcePath = null,
    string? targetPath = null,
    string? targetSheet = null,
    
    [RegularExpression("^(None|Private|Organizational|Public)$")]
    [Description("Privacy level for Power Query data combining (optional). If not specified and privacy error occurs, LLM must ask user to choose: None (least secure), Private (most secure), Organizational (internal data), or Public (public data)")]
    string? privacyLevel = null)
{
    // Parse privacy level if provided
    PowerQueryPrivacyLevel? parsedPrivacyLevel = null;
    if (!string.IsNullOrEmpty(privacyLevel))
    {
        parsedPrivacyLevel = Enum.Parse<PowerQueryPrivacyLevel>(privacyLevel, ignoreCase: true);
    }
    
    // Pass to Core layer
    var result = await commands.SetLoadToTable(excelPath, queryName, targetSheet, parsedPrivacyLevel);
    
    // Return rich error result for LLM to present to user
    return JsonSerializer.Serialize(result, ExcelToolsBase.JsonOptions);
}

---

Part 2: VBA Trust - User Education & Guidance (NO Automatic Changes)

Objective

Detect when VBA trust is disabled and guide users to enable it manually through Excel's settings. NEVER modify trust settings automatically.

Security Principles

• ❌ NEVER modify VBA trust settings from CLI/MCP/Core production code
• ✅ Educate users about Excel security model and proper configuration
• ✅ Link to official Microsoft documentation for authoritative guidance
• ✅ One-time manual setup - user remains in control of security settings
• ✅ Test infrastructure only - registry modification isolated to test helpers

Implementation Requirements

1. Core Layer (src/ExcelMcp.Core/)

Create VBA trust detection result in Models/ResultTypes.cs:

/// <summary>
/// Result indicating VBA operation requires trust access to VBA project object model.
/// Provides instructions for user to manually enable trust in Excel settings.
/// </summary>
public record VbaTrustRequiredResult : OperationResult
{
    /// <summary>
    /// Whether VBA trust is currently enabled
    /// </summary>
    public bool IsTrustEnabled { get; init; }
    
    /// <summary>
    /// Step-by-step instructions for enabling VBA trust
    /// </summary>
    public string[] SetupInstructions { get; init; } = new[]
    {
        "Open Excel",
        "Go to File → Options → Trust Center",
        "Click 'Trust Center Settings'",
        "Select 'Macro Settings'",
        "Check '✓ Trust access to the VBA project object model'",
        "Click OK twice to save settings"
    };
    
    /// <summary>
    /// Official Microsoft documentation URL
    /// </summary>
    public string DocumentationUrl { get; init; } = "https://support.microsoft.com/office/enable-or-disable-macros-in-office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6";
    
    /// <summary>
    /// User-friendly explanation of why trust is required
    /// </summary>
    public string Explanation { get; init; } = "VBA operations require 'Trust access to the VBA project object model' to be enabled in Excel settings. This is a one-time setup that allows programmatic access to VBA code.";
}

Add VBA trust detection in Commands/ScriptCommands.cs:

using Microsoft.Win32;

private bool IsVbaTrustEnabled()
{
    try
    {
        using var key = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\16.0\Excel\Security");
        var value = key?.GetValue("AccessVBOM");
        return value != null && (int)value == 1;
    }
    catch
    {
        return false; // Assume not enabled if cannot read registry
    }
}

private VbaTrustRequiredResult CreateVbaTrustGuidance()
{
    return new VbaTrustRequiredResult
    {
        Success = false,
        ErrorMessage = "VBA trust access is not enabled",
        IsTrustEnabled = false,
        Explanation = "VBA operations require 'Trust access to the VBA project object model' to be enabled in Excel settings. This is a one-time setup that allows programmatic access to VBA code."
    };
}

Check trust before VBA operations:

public OperationResult Run(string excelPath, string? procedureName, object[] parameters)
{
    // Check VBA trust BEFORE attempting operation
    if (!IsVbaTrustEnabled())
    {
        return CreateVbaTrustGuidance();
    }
    
    return ExcelHelper.WithExcel(excelPath, save: false, (excel, workbook) =>
    {
        try
        {
            // Existing VBA execution logic
            // ...
        }
        catch (COMException ex) when (ex.Message.Contains("programmatic access"))
        {
            // Trust was disabled during operation
            return CreateVbaTrustGuidance();
        }
    });
}

2. CLI Layer (src/ExcelMcp.CLI/)

Display user-friendly guidance in Commands/ScriptCommands.cs:

private int DisplayVbaTrustGuidance(VbaTrustRequiredResult trustError)
{
    var panel = new Panel(new Markup(
        "[yellow]VBA Trust Access Required[/]\n\n" +
        "VBA operations require 'Trust access to the VBA project object model' to be enabled in Excel.\n\n" +
        "[cyan]How to enable VBA trust:[/]\n" +
        string.Join("\n", trustError.SetupInstructions.Select((s, i) => $"{i + 1}. {s}")) + "\n\n" +
        $"[dim]This is a one-time setup. After enabling, VBA operations will work.[/]\n\n" +
        $"[cyan]📖 More information:[/]\n" +
        $"[link]{trustError.DocumentationUrl}[/]"
    ))
    .Border(TableBorder.Rounded)
    .BorderColor(Color.Yellow)
    .Header("[yellow]⚠ Setup Required[/]");
    
    AnsiConsole.Write(panel);
    
    AnsiConsole.WriteLine();
    AnsiConsole.MarkupLine("[dim]After enabling VBA trust in Excel, run this command again.[/]");
    
    return 1;
}

public int Run(string[] args)
{
    // ... existing argument parsing ...
    
    var result = scriptCommands.Run(excelPath, procedureName, parameters);
    
    // Handle VBA trust guidance
    if (result is VbaTrustRequiredResult trustError)
    {
        return DisplayVbaTrustGuidance(trustError);
    }
    
    return result.Success ? 0 : 1;
}

3. MCP Server Layer (src/ExcelMcp.McpServer/)

Return structured guidance in Tools/ExcelVbaTool.cs:

// No changes to parameters - trust cannot be modified from MCP
// Just return VbaTrustRequiredResult as JSON for LLM to present

private static string RunVbaScript(ScriptCommands commands, string filePath, string? moduleName, string? parameters)
{
    var result = commands.Run(filePath, moduleName, paramArray);
    
    // If VbaTrustRequiredResult, return it as JSON
    // LLM will present the guidance to user
    return JsonSerializer.Serialize(result, ExcelToolsBase.JsonOptions);
}

LLM will present guidance like:

"I cannot run the VBA macro because Excel's VBA trust access is not enabled. This is a security setting that must be configured manually.

**To enable VBA trust access:**

1. Open Excel
2. Go to File → Options → Trust Center
3. Click 'Trust Center Settings'
4. Select 'Macro Settings'
5. Check '✓ Trust access to the VBA project object model'
6. Click OK twice to save

This is a one-time setup. After enabling this setting, VBA operations will work.

📖 More information: https://support.microsoft.com/office/enable-or-disable-macros-in-office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6

Would you like me to try again after you've enabled this setting?"

4. Test Infrastructure (tests/ExcelMcp.Core.Tests/)

Create test helper Helpers/TestVbaTrustScope.cs:

using Microsoft.Win32;

namespace Sbroenne.ExcelMcp.Core.Tests.Helpers;

/// <summary>
/// TEST INFRASTRUCTURE ONLY - Temporarily modifies VBA trust registry setting 
/// for isolated test execution. NEVER expose this to end users!
/// 
/// This class is INTERNAL and located in the test project only.
/// It should NEVER be referenced by Core/CLI/MCP production code.
/// </summary>
internal sealed class TestVbaTrustScope : IDisposable
{
    private readonly bool _wasEnabled;
    private bool _isDisposed;
    
    public TestVbaTrustScope()
    {
        _wasEnabled = IsVbaTrustEnabled();
        if (!_wasEnabled)
        {
            EnableVbaTrust();
            Thread.Sleep(150); // Registry propagation delay
            
            if (!IsVbaTrustEnabled())
                throw new InvalidOperationException("Test setup failed: Could not enable VBA trust");
        }
    }
    
    public void Dispose()
    {
        if (!_isDisposed && !_wasEnabled)
        {
            try { DisableVbaTrust(); }
            catch (Exception ex)
            {
                // Log but don't throw in Dispose
                Console.Error.WriteLine($"Test cleanup warning: Could not disable VBA trust: {ex.Message}");
            }
            finally { _isDisposed = true; }
        }
    }
    
    private static bool IsVbaTrustEnabled()
    {
        using var key = Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\16.0\Excel\Security");
        var value = key?.GetValue("AccessVBOM");
        return value != null && (int)value == 1;
    }
    
    private static void EnableVbaTrust()
    {
        using var key = Registry.CurrentUser.CreateSubKey(@"Software\Microsoft\Office\16.0\Excel\Security");
        key.SetValue("AccessVBOM", 1, RegistryValueKind.DWord);
    }
    
    private static void DisableVbaTrust()
    {
        using var key = Registry.CurrentUser.CreateSubKey(@"Software\Microsoft\Office\16.0\Excel\Security");
        key.SetValue("AccessVBOM", 0, RegistryValueKind.DWord);
    }
}

Usage in tests:

[Fact]
[Trait("Category", "Integration")]
[Trait("RequiresExcel", "true")]
public void VbaOperation_WithTrust_ShouldExecuteSuccessfully()
{
    using var _ = new TestVbaTrustScope(); // Enables trust for this test only
    
    var result = scriptCommands.Run("test.xlsm", "Module.Procedure", Array.Empty<object>());
    
    Assert.True(result.Success);
} // Trust automatically reverted here

---

Testing Requirements

Create comprehensive tests in tests/ExcelMcp.Core.Tests/:

Privacy Level Tests

1. Privacy detection tests - Verify detection of existing privacy levels in queries
2. Error result generation - Verify PowerQueryPrivacyErrorResult creation
3. Recommendation logic - Verify correct privacy level recommendation
4. Privacy application - Verify privacy level correctly applied when specified
5. CLI parameter parsing - Verify --privacy-level parameter handling
6. MCP parameter handling - Verify privacyLevel parameter passed correctly

VBA Trust Tests

1. Trust detection - Verify correct detection of VBA trust status
2. Guidance generation - Verify VbaTrustRequiredResult creation
3. Error messages - Verify instructions are clear and actionable
4. TestVbaTrustScope tests - Verify temporary registry modification and cleanup
5. Cleanup verification - Verify trust always reverted after test

---

Documentation Requirements

Update documentation to include:

1. Privacy Level Guide - Meanings, security implications, when to use each
2. User Consent Workflow - How LLM asks for permission, how users respond
3. VBA Trust Setup Guide - Step-by-step with screenshots
4. Security-First Principles - Why we don't auto-modify security settings
5. CLI Examples - Using --privacy-level parameter
6. MCP Examples - LLM conversation flows with privacyLevel parameter
7. Environment Variables - EXCEL_DEFAULT_PRIVACY_LEVEL usage
8. Troubleshooting - Common issues and solutions
9. Test Infrastructure - Clear statement about TestVbaTrustScope for tests only

---

Commands to Remove

These existing commands will be REMOVED as they modify security settings:

• ❌ setup-vba-trust - Remove command that modifies VBA trust
• ❌ check-vba-trust - Not needed, detection built into operations

---

Acceptance Criteria

Power Query Privacy

• PowerQueryPrivacyLevel enum created with None/Private/Organizational/Public
• PowerQueryPrivacyErrorResult created with existing levels and recommendation
• Privacy detection logic implemented for existing queries
• Privacy level parameter added to Core interface methods
• CLI --privacy-level parameter implemented
• MCP privacyLevel parameter implemented
• Environment variable EXCEL_DEFAULT_PRIVACY_LEVEL supported
• Privacy error returns detailed guidance, never auto-applies
• All privacy tests passing

VBA Trust

• VbaTrustRequiredResult created with setup instructions
• VBA trust detection implemented (registry check)
• CLI displays formatted guidance with Spectre.Console
• MCP returns structured guidance for LLM
• TestVbaTrustScope helper created in test project only
• No VBA trust modification in Core/CLI/MCP production code
• setup-vba-trust and check-vba-trust commands removed
• All VBA trust detection tests passing
• Documentation clearly states no automatic trust modification

Documentation

• Privacy level guide with security implications
• VBA trust setup guide with screenshots
• CLI examples with --privacy-level
• MCP conversation flow examples
• Security-first principles documented
• Clear statement: VBA trust modification only in test infrastructure

---

Branch Strategy

1. Wait for PR Fixed MCP Server implementation #4 (feature/nuget) to be merged
2. Create new branch feature/security-privacy-vba-trust from main
3. Implement both features together (they are related security concerns)
4. Create PR with comprehensive security review in description

---

Security Review

✅ This implementation has been security-reviewed and approved:

• Power Query Privacy: Optional parameter, explicit consent, fail-safe default
• VBA Trust: Detection and guidance only, never automatic modification
• Test Infrastructure: Properly isolated registry access for tests only
• User Education: Transparent security model, user remains in control
• No Privilege Escalation: Tool cannot weaken security without explicit user action

See comprehensive security review in PR discussion thread for detailed threat analysis.

---

Related Documentation

• Microsoft: Enable or disable macros in Office files
• Power Query M Language: Privacy levels
• excelcli: .github/copilot-instructions.md - Security-first design principles

---

Priority: High - Blocks Power Query operations for users combining data sources
Complexity: Medium - Requires changes across Core/CLI/MCP layers
Security: Critical - Must follow security-first principles
Type: Security Enhancement + Bug Fix

[sbroenne]

PR #4 (feature/nuget) has been merged