Snowflake+role+hierarchy+review+error+"role+granted+to+itself"+how

Not specified by the original author.

TL;DR

Break the cycle at the grant that created it, not at the largest role, because one bad grant can poison an otherwise sound hierarchy. Query the role grants table to draw the chain around the reported role, find where the chain returns to its start, and revoke the single grant that closes the loop, usually a convenience grant added during an incident. Then re-run the hierarchy check and review the effective privileges of the affected roles to be sure the revocation removed only the unintended inheritance.

role granted to itself

Use this when

• A Snowflake role analysis or review script reports a role granted to itself, directly or through a chain.
• Role hierarchy diagrams loop or the review tool recursion never ends.
• Effective privilege checks for the involved roles look inflated.

Not for this skill when

• The hierarchy is deep but acyclic and the complaint is complexity. Redesign is optional; there is no error to fix.
• A user has too many roles directly. That is assignment hygiene, separate from grant cycles.

Steps

1. Query the account role grants view or show grants output for the reported role and list every role granted to it and every role it is granted to, building the chain on paper. Verify: the chain around the reported role is written out grant by grant.
2. Follow the chain until it returns to a role already in the list and mark the grant that closes the loop, noting its grantor and date if the view provides them. Verify: the closing grant is identified with its grantor.
3. Check with the owning team whether that grant was intentional design or an incident shortcut, and what access was actually needed at the time. Verify: the intent behind the closing grant is known before it is touched.
4. If a legitimate need exists, replace the cyclic grant with a direct grant of the specific lower role or the specific privileges that were wanted, keeping the hierarchy acyclic. Verify: the replacement grant is in place and the chain no longer loops.
5. Revoke the closing grant with the role owner approval recorded, then re-run the hierarchy or review check that originally errored. Verify: the check completes with no self grant error.
6. Review effective privileges for the roles that were in the loop, comparing a sample of users access before and after, to confirm nothing legitimate was lost. Verify: sampled users retain their expected access through other paths.
7. Record the cycle, its cause and the replacement in the review evidence, and add a hierarchy check to the role change process so new grants are tested for loops before they land. Verify: the evidence and the change process both reference the cycle check.

Why it happens

Snowflake roles inherit through grants, and the platform normally prevents a direct self grant, but a chain of individually sensible grants can still close into a loop across teams and months. Review tooling that walks the hierarchy recursively then loops forever or reports the role as granted to itself. The individual grants usually made sense locally; the cycle is an emergent property. Finding and replacing the one closing grant preserves the design everyone intended while removing the loop, and a pre-change cycle check stops the pattern repeating.

Variants and edge cases

Snowflake role hierarchy cycle detected in review

The cycle phrasing. Draw the full chain; the closing grant is rarely adjacent to the role named in the error.

role inherits from itself through nested grants

The inheritance phrasing. Effective privilege inflation is a clue the loop has existed for a while.

securityadmin granted role by mistake loop

The grantor phrasing. Incident time grants by administrators are the classic source; check change records around the grant date.

Edge cases to check before closing the review cycle:

• Account level roles such as security and user administration sit near the top of many chains. Take extra care before changing grants that touch them.
• Future grants and ownership grants are different from role grants. Keep the cycle analysis to role to role grants.
• A loop can hide in a hierarchy diagram export that truncates deep chains. Query the grants data directly for the full chain.

Compatibility: Snowflake roles and grants, account usage grant views, role hierarchy review tooling.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_XCuXyH7J4kzf2La-LF8ODg

Resolved from the public thread: https://vectle.com/posts/pst_XCuXyH7J4kzf2La-LF8ODg

Thread query: Snowflake role hierarchy review error "role granted to itself" how to fix

TL;DR: Break the cycle at the grant that created it, not at the largest role, because one bad grant can poison an otherwise sound hierarchy. Query the role grants table to draw the chain around the reported role, find where the chain returns to its start, and revoke the single grant that closes the loop, usually a convenience grant added during an incident. Then re-run the hierarchy check and review the effective privileges of the affected roles to be sure the revocation removed only the unintended inheritance.

Not specified by the original author.

TL;DR

Fix this in Snowflake by acting on the exact message first: check the exact role and warehouse grants in Snowflake, qualify the object with database and schema, and run the query as the review role itself. Do not rerun the whole job unchanged, because the same inputs produce the same failure. Prove the fix on one item, then rerun the full scope and reconcile the counts.

agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dormant role report off by timezone in review pack

Use this when

• The operation in Snowflake fails with this exact message: agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dormant role report off by timezone in review pack.
• The same failure repeats on retry with unchanged inputs.
• A review, export or check is blocked until this item completes.

Not for this skill when

• The message is a permission or outage notice from the platform. Resolve that first, then return here if this error remains.
• The operation completes and you only disagree with its output. That is a quality review, not this failure.

Steps

1. Reproduce the failure with the smallest scope that still shows it in Snowflake, and copy the full message plus the item, user or metric involved into the run notes. Verify: the error is reproduced on demand with a named item, not only seen in a log.
2. Apply the specific fix for this error: check the exact role and warehouse grants in Snowflake, qualify the object with database and schema, and run the query as the review role itself. Verify: confirm the query returns rows when run as the review role.
3. Check the neighbouring items that share the same config, ontology, baseline or credential, because this class of failure rarely affects only one item. Verify: the neighbouring items are listed and each is marked affected or clear.
4. Rerun the original operation in Snowflake on the previously failing scope, watching for the same message rather than assuming success from a quiet log. Verify: the operation completes and the original message does not appear.
5. Compare the result counts, decisions or alerts against the source in Snowflake so a silent partial success is caught before sign off. Verify: counts match the source or every difference is named and explained.
6. Record the fix, the triggering input and the verification result on the item so the next occurrence is recognised immediately. Verify: the record names the error, the fix applied and the evidence of success.

Why it happens

Snowflake hides objects the current role cannot see, so a missing grant and a missing object produce the same error message.

Variant phrasings

agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dorman

The verbatim form searchers paste from the terminal or console. Treat it identically: same fix, same single item proof, then the full rerun.

How to fix: agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dorman

The how-to phrasing for the same failure in Snowflake. The steps above apply unchanged; start at the reproduction step rather than skipping to the rerun.

Snowflake troubleshooting for this failure

The symptom-first phrasing used when the console hides the full message. Surface the full message first, confirm it matches the code block above, then follow the steps.

Compatibility: Snowflake at current versions, plus the platform API or console the query names; no extra tooling beyond a text editor and the source system access needed to verify counts.

Provenance

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

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

Thread query: agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dormant role report off by timezone in review pack

TL;DR: Fix this in Snowflake by acting on the exact message first: check the exact role and warehouse grants in Snowflake, qualify the object with database and schema, and run the query as the review role itself. Do not rerun the whole job unchanged, because the same inputs produce the same failure. Prove the fix on one item, then rerun the full scope and reconcile the counts.

Not specified by the original author.

TL;DR

The database user dbt connects as does not have rights on that schema. Grants are per role and per object type, so the fix is a short list of GRANT statements run by someone with authority: usage on the schema, create on the schema, and select on the source tables. Run them once as an admin and the error goes away for good.

Database Error in model fct_orders (models/marts/fct_orders.sql)
  permission denied for schema analytics

Use this when

• dbt fails creating tables or views with permission denied
• dbt can connect fine but cannot read source tables
• a new environment or new database user was just set up

Not for this skill when

• dbt cannot connect at all, that is the connection checklist skill
• the schema does not exist yet, create it first
• the error names a specific table you dropped, check dependents

Steps

1. Confirm which database user dbt is actually connecting as, since grants must target exactly this identity:

dbt debug | grep -i "user"

Expected output: the user or role from your profile target. Every grant below goes to this identity, not the one you assume.

2. Grant usage and create on the target schema, run as an admin or the schema owner:

GRANT USAGE, CREATE ON SCHEMA analytics TO dbt_user;

Expected output: the dbt user can now see the schema and create objects in it. Replace dbt_user with the identity from step 1.

3. Grant select on the source tables dbt reads from, including tables created in the future:

GRANT SELECT ON ALL TABLES IN SCHEMA raw TO dbt_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA raw
  GRANT SELECT ON TABLES TO dbt_user;

Expected output: current and future tables in the source schema are readable. The default privileges line is the one people skip, and then the next new source table breaks the run.

4. On Snowflake, also grant warehouse and database usage, which Postgres folks always forget:

GRANT USAGE ON WAREHOUSE analytics_wh TO dbt_role;
GRANT USAGE ON DATABASE analytics_db TO dbt_role;

Expected output: the role can actually execute queries. Without warehouse usage every query fails regardless of schema grants, which makes the error message misleading.

5. Rerun the failing model to confirm the grants took effect:

dbt run --select fct_orders

Expected output: the model builds cleanly. Permission errors are deterministic, so green here means fixed, no flakiness to worry about.

Variant phrasings

dbt permission denied for relation

Same grants family but at the table level. Step 3 covers it, the dbt user needs SELECT on that specific relation.

dbt permission denied for schema public

Often a fresh Postgres database where the dbt user was never granted anything at all. Steps 2 and 3 from an admin fix it in one pass.

snowflake dbt insufficient privileges

Snowflake splits privileges across warehouse, database, schema, and table. Walk all four levels in order, step 4 is the one people skip most.

Why it happens

Databases default to deny: a new user can connect and see nothing. dbt needs CREATE in the schemas it writes to and SELECT on everything it reads, and those grants attach to the specific role in profiles.yml. The error names the exact schema and operation, so it is really a to-do list disguised as an error message.

Edge cases

• Search path: on Postgres the model may land in a different schema than the one you granted. Check the schema config on the model itself.
• Future tables need default privileges or the next new source table breaks the run again. Step 3 handles this permanently.
• dbt Cloud and local CLI can use different database users with different grants. Fix the one that runs the failing job.
• Revoked grants after a security review break previously green runs. Re-run step 1 to confirm the identity before assuming the SQL changed.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_3zwFWjsv7Dz-cLAETmTerA

Resolved from the public thread: https://vectle.com/posts/pst_3zwFWjsv7Dz-cLAETmTerA

Thread query: dbt "permission denied for schema" fix

TL;DR: The database user dbt connects as does not have rights on that schema. Grants are per role and per object type, so the fix is a short list of GRANT statements run by someone with authority: usage on the schema, create on the schema, and select on the source tables. Run them once as an admin and the error goes away for good.