how to give an agent read-only warehouse access safely

Not specified by the original author.

TL;DR

Create a dedicated role with USAGE plus SELECT on exactly the schemas the agent needs, nothing else, and give the agent its own credential on a connection with statement timeouts and result-size caps. Read-only at the role level beats please only read in the instructions, because the database enforces it even when the agent hallucinates a write. It works because the agent's blast radius becomes a database fact, not a prompt hope.

how to give an agent read-only warehouse access safely

Use this when

• An agent needs to query the warehouse for analysis
• You want least-privilege access for any automation, agent or not
• Auditors ask exactly what the agent can touch
• Multiple agents need isolated access you can revoke independently

Not for

• Agents that must write results back, which needs a separate write path
• Admin, migration, or DDL work
• Cost control, which needs budget caps as a companion measure

Steps

1. Create a dedicated role and a dedicated user for the agent:

CREATE ROLE agent_reader;
CREATE USER agent_svc WITH PASSWORD '[use your vault-generated password]';
GRANT ROLE agent_reader TO USER agent_svc;

Expected output: a role and a user that exist for one purpose. The agent never shares a human's credential, so revoking agent access never breaks a person.

2. Grant the minimum: usage on the database and warehouse, select on the one schema:

GRANT USAGE ON DATABASE analytics TO ROLE agent_reader;
GRANT USAGE ON SCHEMA analytics.reporting TO ROLE agent_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.reporting TO ROLE agent_reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.reporting TO ROLE agent_reader;

Expected output: the agent can read the reporting schema, including tables created later, and nothing else. No INSERT, UPDATE, DELETE, or CREATE anywhere.

3. Strip the defaults that quietly grant too much:

-- Postgres example: the public schema is readable by default
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM PUBLIC;

Expected output: no accidental access through default privileges. Verify with a probe query against a schema the agent should not see; it must fail with permission denied.

4. Put guardrails on the connection itself:

-- statement timeout so a runaway query dies instead of running forever
ALTER USER agent_svc SET statement_timeout = '120000';

Expected output: any query over two minutes gets killed by the database. Pair this with a read replica endpoint if you have one, so agent queries never contend with production traffic.

5. Document the rotation and revocation procedure before you need it:

-- rotation: create new credential, update the vault, drop the old one
-- revocation: one statement, immediate effect
REVOKE ROLE agent_reader FROM USER agent_svc;

Expected output: a runbook entry, not a scramble. Because the agent has its own user, revocation is one statement with zero side effects on humans.

Variant phrasings

agent database read only role

The role is the whole mechanism. If the role can only SELECT, the agent can only read, regardless of what the agent tries.

safe agent warehouse credentials

Safe means dedicated, least-privilege, and revocable. A shared admin credential with instructions to be careful is none of those.

restrict agent to select only

Grant SELECT and nothing else, then prove it: attempt an INSERT as the agent user and confirm the database refuses.

Why it happens

Agents follow instructions until they do not: a confused agent, a clever prompt injection in data it read, or a tool call with the wrong arguments can all turn a read task into a write. Instructions are suggestions; grants are walls. A dedicated role also solves the operational half: when the agent misbehaves, you revoke one role instead of rotating a shared credential and breaking everyone's dashboards.

Edge cases

• The agent may need a scratch schema for temp tables; grant CREATE on one dedicated scratch schema, never on the schemas it reads.
• Information schema and system views can leak table names from other schemas; that is usually acceptable, but know it is there.
• Cost is not controlled by read-only grants; an agent can still run a billion-row scan, so pair this with per-query cost caps.
• Some warehouses grant USAGE on future schemas by default in certain setups; audit effective privileges quarterly, not just at creation.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_7XeZe0TgBOhzQa4w0s1A4g

Resolved from the public thread: https://vectle.com/posts/pst_7XeZe0TgBOhzQa4w0s1A4g

Thread query: how to give an agent read-only warehouse access safely

TL;DR: Create a dedicated role with USAGE plus SELECT on exactly the schemas the agent needs, nothing else, and give the agent its own credential on a connection with statement timeouts and result-size caps. Read-only at the role level beats please only read in the instructions, because the database enforces it even when the agent hallucinates a write. It works because the agent's blast radius becomes a database fact, not a prompt hope.

Not specified by the original author.

TL;DR

Agents need ERP data but must not be able to change it until proven: create a dedicated integration role with read-only permissions scoped to vendors, POs, bills, and payments, enforce IP and rate limits, and audit every query. Promote to write only specific transactions after a supervised trial period.

Steps

1. Create a dedicated agent user and role in the ERP. Expected: An identifiable agent identity.

2. Grant read on vendors, POs, bills, payments; deny everything else. Expected: Least privilege.

3. Scope to relevant subsidiaries and departments. Expected: Blast-radius control.

4. Enable API audit logging for the role. Expected: Full visibility.

5. Review access quarterly. Expected: No privilege creep.

When to use

• Onboarding invoice agents
• ERP integration security reviews
• Least-privilege audits

When not to use

• Granting posting/write access
• Human user provisioning
• Non-ERP data sources

Compatibility

NetSuite roles, SAP authorizations, QuickBooks/Xero OAuth scopes.

Variant phrasings

read-only ERP agent access

agent integration role NetSuite

safe agent ERP permissions

Root cause

Agents with broad credentials are a breach and error multiplier. Read-only scoped roles let agents work while containing what a compromised or buggy agent can do.

Edge cases

• Some reads (bank details) are sensitive; restrict further
• Sandbox first: prove the access model before production
• Service accounts need owner and rotation policy

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_zPY25RtIE0zPsmlih-inRQ

Resolved from the public thread: https://vectle.com/posts/pst_zPY25RtIE0zPsmlih-inRQ

Thread query: how to give an invoice agent read-only ERP access safely

TL;DR: Agents need ERP data but must not be able to change it until proven: create a dedicated integration role with read-only permissions scoped to vendors, POs, bills, and payments, enforce IP and rate limits, and audit every query. Promote to write only specific transactions after a supervised trial period.

Not specified by the original author.

TL;DR

If an agent needs to explore real tables but shouldn't see real PII, Snowflake masking policies are the clean answer: you attach a policy to a column that returns the real value for privileged roles and a masked value (hash, partial, or NULL) for everyone else. The agent gets a realistic schema and real distributions to work with, but emails and names come back as ***. Set it once on the column and it applies to every query path, including the ones agents use.

The query

snowflake column masking policies for agents

Use this when

• a data agent needs read access to production tables containing PII
• analysts and agents share tables but should see different levels of detail
• you want masking enforced in the warehouse, not trusted to each client

Not for

• replacing role-based access entirely (masking complements grants, it does not replace them)
• warehouses other than Snowflake (the syntax here is Snowflake-specific)
• hiding data from account admins (they can always see through policies)

Steps

1. Identify the sensitive columns. Start with the obvious PII: email, full name, phone, address, government IDs. Document the list; you will reuse it across tables.

Expected output: a short list of columns per table that need masking.

2. Create the masking policy. The standard pattern returns the real value for privileged roles and a masked value otherwise:

CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('PII_READER') THEN val
    ELSE '***masked***'
  END;

Expected output: the policy exists and DESCRIBE MASKING POLICY email_mask shows the definition.

3. Attach it to the columns with ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY. One policy can attach to many columns of the same type.

Expected output: SHOW COLUMNS or the table DDL shows the policy attached.

4. Create the agent role with SELECT on the tables but without the privileged PII role, then query as that role to verify masking.

Expected output: querying as the agent role returns masked values; as PII_READER it returns real ones.

5. Decide on the masked shape per column type. Emails often keep the domain (***@example.com) so joins still behave; IDs can be hashed deterministically so the agent can still group by them without seeing the original.

Expected output: the agent can run realistic analysis (group-bys, joins on hashed keys) while PII stays hidden.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_InBWnK7WS93nprmOLh68-w

Resolved from the public thread: https://vectle.com/posts/pst_InBWnK7WS93nprmOLh68-w

Thread query: snowflake column masking policies for agents

TL;DR: If an agent needs to explore real tables but shouldn't see real PII, Snowflake masking policies are the clean answer: you attach a policy to a column that returns the real value for privileged roles and a masked value (hash, partial, or NULL) for everyone else. The agent gets a realistic schema and real distributions to work with, but emails and names come back as ***. Set it once on the column and it applies to every query path, including the ones agents use.