Data Fixes

A data fix is a deployment of a deliberately partial schema package — usually a handful of migration scripts that correct a specific production issue — rather than a full release. SchemaQuench supports this as a first-class mode through four configuration flags that flip together, called the datafix profile.

The distinction matters because several SchemaQuench behaviors are correct for a full release and wrong for a partial package. A full release treats the package as the complete truth of what the database should look like: tables not in the package get dropped, migration tracking records with no matching script get pruned, helper infrastructure gets redeployed. A data fix does the opposite — it runs only what's in the partial package and leaves everything else alone.

Where a full release treats the schema package as the complete truth of what the database should look like, a data fix treats it as a surgical slice. Eight dimensions separate the two:

Under the datafix profile above, SchemaSmith performs no structural DDL of its own — it runs the migration scripts you provide. Those scripts often still need targeted rights beyond basic reader/writer access, the most common being CREATE TABLE for the rollback-backup tables a careful fix writes before it changes anything. The safe way to grant that is to give the deploy account its own schema — conventionally named datafix — to create backup tables in: creating a table in a schema you own needs no rights over the product's own tables, so the account can back up and fix data without any power to alter or drop the schema it's deploying into.

The grant sets below are a recommended starting point for a datafix_user account on each engine, scoped to about the minimum a representative multi-tenant fix needs. Treat them as a baseline to tighten per environment — a production account should carry only the grants the specific fix has been proven to need.

Per-engine grant baselines

Each engine reaches the same intent — reader/writer on the product data, an owned schema for backups, execute on ancillary routines, and temporary-table space — through its own privilege model. Substitute a strong password and repeat the per-database block for each target database.

• SQL Server
• PostgreSQL
• MySQL

-- Server-level login (run against master)
CREATE LOGIN datafix_user WITH PASSWORD = 'YourStrongPassword';

-- Repeat the block below in each target database
USE shop_tenant_a;
GO
CREATE USER datafix_user FOR LOGIN datafix_user;
GO

-- Dedicated schema the deploy user OWNS: backup tables land here
CREATE SCHEMA datafix AUTHORIZATION datafix_user;
GO

-- Reader/writer on the product data (dbo); no structural rights on dbo
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO datafix_user;

-- Create rollback-backup tables; they land in the owned 'datafix' schema
GRANT CREATE TABLE TO datafix_user;

-- Ancillary stored procedures and functions the fix may call
GRANT EXECUTE ON SCHEMA::dbo TO datafix_user;
GO

-- Cluster-level role (run on a maintenance database)
CREATE ROLE datafix_user LOGIN PASSWORD 'YourStrongPassword';

-- Repeat the block below for each target database (\connect in psql)
\connect shop_tenant_a

-- Allow the role to open a connection to this database
GRANT CONNECT   ON DATABASE shop_tenant_a TO datafix_user;

-- Temp space: allows CREATE TEMPORARY TABLE within a session
GRANT TEMPORARY ON DATABASE shop_tenant_a TO datafix_user;

-- Read/write the product data, but no CREATE in public
GRANT USAGE ON SCHEMA public TO datafix_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO datafix_user;

-- Dedicated schema the deploy user OWNS: backup tables go here
CREATE SCHEMA datafix AUTHORIZATION datafix_user;

-- Ancillary functions and procedures the fix may call
GRANT EXECUTE ON ALL FUNCTIONS  IN SCHEMA public TO datafix_user;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA public TO datafix_user;

-- User account (% = any host; tighten the host specifier in production)
CREATE USER IF NOT EXISTS 'datafix_user'@'%' IDENTIFIED BY 'YourStrongPassword';

-- Repeat for each target database
GRANT SELECT, INSERT, UPDATE, CREATE ON `shop_tenant_a`.* TO 'datafix_user'@'%';
GRANT CREATE TEMPORARY TABLES        ON `shop_tenant_a`.* TO 'datafix_user'@'%';
GRANT EXECUTE                        ON `shop_tenant_a`.* TO 'datafix_user'@'%';

FLUSH PRIVILEGES;

Privilege summary across engines