database-setup.sql

-- Create a restricted user for the MCP translation service
-- Replace 'your_database' with your actual database name

-- Create the translation service user
CREATE USER 'mcp_translator'@'%' IDENTIFIED BY 'STRONG_PASSWORD_HERE';

-- Grant SELECT permission on all tables (to read English content)
GRANT SELECT ON your_database.* TO 'mcp_translator'@'%';

-- Grant UPDATE permission ONLY on German translation columns
-- Repeat for each table that needs translation
GRANT UPDATE (german_content, german_title, german_description, updated_at) 
ON your_database.products TO 'mcp_translator'@'%';

GRANT UPDATE (german_content, german_name, updated_at) 
ON your_database.categories TO 'mcp_translator'@'%';

GRANT UPDATE (german_text, updated_at) 
ON your_database.pages TO 'mcp_translator'@'%';

-- Create an audit table for tracking all translations
CREATE TABLE IF NOT EXISTS translation_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INT NOT NULL,
    column_name VARCHAR(100) NOT NULL,
    old_value TEXT,
    new_value TEXT,
    translated_by VARCHAR(100),
    session_id VARCHAR(255),
    translated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_session (session_id),
    INDEX idx_timestamp (translated_at)
);

-- Grant INSERT permission on audit table
GRANT INSERT ON your_database.translation_audit TO 'mcp_translator'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

-- Verify permissions
SHOW GRANTS FOR 'mcp_translator'@'%';