Skip to content

GatorSet DB permissions

How to set permissions for target write operations

Below is a practical, non‑technical guide to the minimum permissions your target connection needs for each operation type. Operations:

  • Append: add new rows to an existing table
  • Truncate: wipe all rows from a table, then load fresh rows. To ensure data is not lost incase of a failure during this flow a temporary table is created to add the data to before the data it then moved into the target table. This also allows target to be the same as source allowing data to be update in place
  • Update: If the table is a primary key then only rows that are in source but not in target are moved to the target table. All other rows are left in place. To optimised performance data is written into a temporary table before being moved over to the target table.
  • Replace: drop the table and recreate it, then load rows

Tip: In many orgs, it’s simpler to assign a role that already bundles these permissions (for example, a “Data Editor” role) rather than managing single privileges.

PostgreSQL

  • General prerequisites:
  • USAGE on the target schema (to access it)
  • SELECT/USAGE on sequences if the table has serial/identity columns (so inserts can get next values)
  • Append
  • Needed: INSERT on the table
  • Why: The job needs to add new rows.
  • Truncate
  • Needed: TRUNCATE on the table (or table ownership)
  • Why: TRUNCATE quickly removes all existing rows before loading.
  • Update
  • Needed: UPDATE on the table; if upsert is used, also INSERT (and sometimes DELETE if the flow removes non-matching rows)
  • Why: The job must modify existing rows and may add new ones.
  • Replace
  • Needed: DROP on the table and CREATE on the schema (or table ownership)
  • Why: The job drops the old table and creates a fresh one.

MySQL / MariaDB

  • General prerequisites:
  • CREATE on the database/schema if the job may create tables
  • For AUTO_INCREMENT, no extra grants beyond INSERT are typically needed
  • Append
  • Needed: INSERT on the table
  • Why: Adds new rows.
  • Truncate
  • Needed: Because TRUNCATE acts like DROP+CREATE, you need DROP on the table and CREATE on the schema
  • Why: It removes and re-creates the table to clear data.
  • Update
  • Needed: UPDATE; if upsert is used (INSERT ... ON DUPLICATE KEY UPDATE), need INSERT too
  • Why: Changes existing rows and may add new ones.
  • Replace
  • Needed: DROP on the table and CREATE on the schema
  • Why: Drops and recreates the table to fully refresh it.

Microsoft SQL Server

  • General prerequisites:
  • CONNECT to the database
  • CREATE TABLE in the target schema if creating new tables
  • Append
  • Needed: INSERT on the table
  • Why: Adds new rows.
  • Truncate
  • Needed: ALTER on the table (TRUNCATE requires ALTER TABLE permission)
  • Why: TRUNCATE empties the table in one operation.
  • Update
  • Needed: UPDATE; for upsert flows, also INSERT (and possibly DELETE if the flow removes rows)
  • Why: Edits existing rows and may add/remove rows.
  • Replace
  • Needed: DROP TABLE on the table and CREATE TABLE in the schema (often covered by appropriate schema permissions or ownership)
  • Why: Drops and recreates the table.

Oracle Database

  • General prerequisites:
  • CREATE TABLE in the schema if creating tables
  • For identity/sequence-driven keys, SELECT on sequences used by the table
  • Append
  • Needed: INSERT on the table
  • Why: Adds new rows.
  • Truncate
  • Needed: Table owner or DROP ANY TABLE system privilege
  • Why: TRUNCATE clears all rows very quickly.
  • Update
  • Needed: UPDATE; if upsert (MERGE) is used, also INSERT (and possibly DELETE)
  • Why: Modifies existing rows and may add new ones.
  • Replace
  • Needed: DROP TABLE (or DROP ANY TABLE) and CREATE TABLE
  • Why: Drops and recreates the table for a clean rebuild.

Snowflake

  • General prerequisites:
  • USAGE on the warehouse, database, and schema
  • CREATE TABLE on the schema if the flow can create tables
  • Append
  • Needed: INSERT on the table
  • Why: Adds new rows.
  • Truncate
  • Needed: TRUNCATE on the table (or OWNERSHIP)
  • Why: Clears all rows efficiently.
  • Update
  • Needed: UPDATE; for upserts, also INSERT (and possibly DELETE)
  • Why: Modifies existing rows and may add new ones.
  • Replace
  • Needed: DROP on the existing table and CREATE TABLE on the schema (or OWNERSHIP on the table)
  • Why: Drops and recreates the target table.

Google BigQuery

  • Note: BigQuery is a data warehouse (not strictly relational), but the same concepts apply.
  • Recommended role (simplest): BigQuery Data Editor on the dataset (covers create, update, insert, delete)
  • Or granular:
  • bigquery.tables.get, bigquery.tables.updateData (updates), bigquery.tables.create, bigquery.tables.update, bigquery.tables.delete, and bigquery.jobs.create
  • Append
  • Needed: Permission to insert rows (bigquery.tables.updateData) into the table
  • Why: Adds new rows.
  • Truncate
  • Needed: Permission to overwrite or delete table data (often done by deleting/recreating the table, so tables.delete + tables.create)
  • Why: Clears existing rows before loading.
  • Update
  • Needed: bigquery.tables.updateData (and sometimes insert for upserts)
  • Why: Modifies existing rows and may insert new ones.
  • Replace
  • Needed: tables.delete and tables.create on the dataset
  • Why: Drops and rebuilds the table fully.

Amazon Redshift

  • General prerequisites:
  • USAGE on schema; CREATE on schema if creating tables
  • Append
  • Needed: INSERT on the table
  • Why: Adds new rows.
  • Truncate
  • Needed: Table owner or a user with sufficient privilege to TRUNCATE (commonly ownership)
  • Why: Empties the table prior to load.
  • Update
  • Needed: UPDATE; for upserts, also INSERT (and possibly DELETE)
  • Why: Edits existing rows and may add/remove rows.
  • Replace
  • Needed: DROP on the table and CREATE on the schema (or ownership)
  • Why: Drops and recreates the table for a full refresh.

IBM Db2 (LUW)

  • General prerequisites:
  • CONNECT as normal; CREATEIN on schema if creating tables
  • USAGE on target schema (to use existing objects).
  • INSERT on target table
  • CREATE TABLE, SELECT, ALTER TABLE, COMMENT ON, CREATE INDEX, and authority to create objects IN the chosen tablespace (if specified).
  • GRANT and REVOKE authority (optional; errors are ignored in code if missing)
  • Append
  • Needed: SELECT, INSERT on the table, REFERENCES to re-add FKs, CREATE TABLE/ALTER TABLE, REFERENCES
  • Why: Adds new rows.
  • Truncate
  • Needed: SELECT, CREATE TABLE, REFERENCES (re-adding FKs), INSERT, DELETE on the target table ALTER privilege on the table or CONTROL (ownership) privilege
  • Why: Db2’s TRUNCATE requires ALTER/CONTROL on the table.
  • Update
  • Needed: UPDATE; for upserts, also INSERT (and possibly DELETE), DROP TABLE, CREATE TABLE. SELECT, ALTER TABLE (constraint/hidden toggles), REFERENCES (FKs)
  • Why: Modifies existing rows and may add/remove rows.
  • Replace
  • Needed: SELECT, INSERT, DROP on the table and CREATEIN on the schema, REFERENCES
  • Why: Drops the existing table and recreates it.

Files and Object Storage targets

  • Local/Network file systems (CSV/Parquet/etc.)
  • Append: Write permission to the directory
    • Why: Create or extend files with new data.
  • Truncate: Delete + Write permissions
    • Why: Remove existing files before writing fresh ones.
  • Update: Read + Write (and sometimes Delete) permissions
    • Why: To read existing files and rewrite changed data.
  • Replace: Delete + Write (and possibly Create directory)
    • Why: Remove the old file set and write new files.
  • Amazon S3
  • Append: s3:PutObject on the bucket/prefix
  • Truncate: s3:ListBucket (to list existing files) + s3:DeleteObject + s3:PutObject
  • Update: s3:GetObject + s3:PutObject (and s3:DeleteObject if rewriting)
  • Replace: s3:ListBucket + s3:DeleteObject + s3:PutObject
  • Why: Put writes objects; Delete removes old objects; List enumerates existing files.
  • Azure Blob Storage
  • Append: Write (to the container/path)
  • Truncate: List + Delete + Write
  • Update: Read + Write (and Delete if rewriting)
  • Replace: List + Delete + Write
  • Why: Similar to S3; you need to list/delete old blobs and write new ones.
  • Google Cloud Storage
  • Append: storage.objects.create
  • Truncate: storage.objects.list + storage.objects.delete + storage.objects.create
  • Update: storage.objects.get + storage.objects.create (and delete if needed)
  • Replace: storage.objects.list + storage.objects.delete + storage.objects.create
  • Why: Create writes new objects; delete removes old versions; list helps find what to replace.

  • Minimum
  • Append: INSERT (or write/create on files)
  • Truncate: TRUNCATE (or equivalent), or DROP+CREATE, plus schema usage
  • Update: UPDATE (+ INSERT/DELETE for upsert flows)
  • Replace: DROP on the table and CREATE on the schema (or ownership), plus schema usage
  • Recommended (smoother operations)
  • Grant a role that includes INSERT, UPDATE, DELETE, TRUNCATE, DROP, CREATE, REFERENCES and necessary schema/warehouse usage for the target area. This avoids mid‑run failures if the flow needs to create or re-create tables, manage sequences/identity columns, or run upserts.

If you share which specific databases and operations you plan to use in each environment, I can translate the above into exact GRANT statements or IAM policies for your setup.