Using inherited grants¶

This topic describes how to issue, migrate, audit, and troubleshoot inherited grants.

Get started¶

For most customers, the right approach is to combine inherited and granular grants to create the ideal pattern. You can start by:

1. Establish a baseline with inherited grants: Grant a role the privileges it needs across all current and future objects of a type in a container.
2. Layer policies and granular controls on the exceptions: Use masking policies or row access policies to handle objects that need different access than the baseline.

This pattern keeps the grant model simple and reviewable while giving you the flexibility to handle special cases without abandoning the inherited-grant baseline.

Syntax¶

GRANT  INHERITED <privilege> [ , <privilege> ... ]
  ON   ALL <object_type> IN { ACCOUNT | DATABASE <name> | SCHEMA <name> }
  TO   { ROLE <role_name> | DATABASE ROLE <db_role_name> | APPLICATION <name> }

REVOKE INHERITED <privilege> ON ALL <object_type>
  IN   { ACCOUNT | DATABASE <name> | SCHEMA <name> }
  FROM { ROLE <role_name> | DATABASE ROLE <db_role_name> | APPLICATION <name> }

Required container privileges¶

Inherited object privileges do not replace required container privileges. For a role to access an object in a schema, the role typically also needs USAGE on the database and schema.

For example, to allow analyst_role to query tables in prod.analytics:

GRANT USAGE ON DATABASE prod TO ROLE analyst_role;
GRANT USAGE ON SCHEMA prod.analytics TO ROLE analyst_role;

GRANT INHERITED SELECT ON ALL TABLES
  IN SCHEMA prod.analytics
  TO ROLE analyst_role;

The inherited SELECT grant controls access to the tables. The USAGE grants allow the role to resolve the database and schema.

Examples¶

Set up a consistent authorization profile¶

USE ROLE sales_admin; -- sales_admin has the necessary MANAGE GRANTS privileges on sales_db
GRANT INHERITED USAGE  ON ALL SCHEMAS IN DATABASE sales_db TO ROLE analyst;
GRANT INHERITED SELECT ON ALL TABLES  IN DATABASE sales_db TO ROLE analyst;

Outcome: analyst can SELECT from every existing table in sales_db, with one grant record per privilege rather than one per table.

New objects are covered automatically¶

Continuing from the example above:

USE ROLE sales_admin;
CREATE TABLE sales_db.us_west.new_orders (id INT);

USE ROLE analyst;
SELECT * FROM sales_db.us_west.new_orders;   -- succeeds, no additional grant required

Cover multiple object types¶

Inherited grants apply to a specific object type. To extend a privilege across multiple types, issue one statement per type (TABLES, DYNAMIC TABLES, ICEBERG TABLES, VIEWS, etc.):

USE ROLE sales_admin;
GRANT INHERITED SELECT ON ALL TABLES         IN DATABASE sales_db TO ROLE analyst;
GRANT INHERITED SELECT ON ALL DYNAMIC TABLES IN DATABASE sales_db TO ROLE analyst;
GRANT INHERITED SELECT ON ALL ICEBERG TABLES IN DATABASE sales_db TO ROLE analyst;
GRANT INHERITED SELECT ON ALL VIEWS          IN DATABASE sales_db TO ROLE analyst;

Outcome: analyst can SELECT from every existing and future table, dynamic table, Iceberg table, and view in sales_db. Object types not listed (such as materialized views or external tables) are not covered and require their own GRANT INHERITED statement.

Account-scope grant¶

USE ROLE SECURITYADMIN;
GRANT INHERITED USAGE ON ALL WAREHOUSES IN ACCOUNT TO ROLE analyst;

Outcome: analyst can use any current or future warehouse in the account.

Grant inherited access to an application¶

USE ROLE SECURITYADMIN;
GRANT INHERITED USAGE  ON ALL SCHEMAS IN DATABASE sales_db TO APPLICATION trust_center;
GRANT INHERITED SELECT ON ALL TABLES  IN DATABASE sales_db TO APPLICATION trust_center;

Outcome: The trust_center application can read every existing and future table in sales_db without requiring additional grants as new schemas or tables are added.

Migrate from future grants and GRANT <privileges> ON ALL¶

If you currently use future grants combined with GRANT <privileges> ON ALL to maintain a consistent authorization profile, you can replace both with a single inherited grant. Snowflake recommends migrating in the following sequence to avoid any gaps.

Step 1: Inventory the current state¶

Identify the existing future grants and bulk grants you intend to replace.

-- Future grants on a database
SHOW FUTURE GRANTS IN DATABASE sales_db;

-- Future grants on a schema
SHOW FUTURE GRANTS IN SCHEMA sales_db.us_west;

-- Existing grants held by the role you are migrating
SHOW GRANTS TO ROLE analyst;

Look for repeated patterns such as SELECT ON ALL TABLES IN SCHEMA plus a matching SELECT ON FUTURE TABLES IN SCHEMA for the same role and schema. These pairs are the strongest candidates for collapsing into a single inherited grant.

Step 2: Identify which patterns can collapse¶

A grant pair is safe to collapse to an inherited grant when all three of the following are true:

• The privilege is the same in the existing-objects grant and the future-objects grant.
• The grantee role is the same.
• No object in the container needs to be excluded from the privilege.

If some objects need to be excluded, do not collapse. Keep the granular grants for those objects, or apply row access policies or masking policies to handle the exceptions.

Step 3: Issue the inherited grant¶

Create the new inherited grant. The grant takes effect immediately for both existing and future objects.

USE ROLE sales_admin;
GRANT INHERITED SELECT ON ALL TABLES IN DATABASE sales_db TO ROLE analyst;

Step 4: Verify parity¶

Before revoking the original grants, confirm that the role still has the expected access on a representative sample of objects.

USE ROLE analyst;
SHOW GRANTS ON TABLE sales_db.us_west.orders;        -- existing object
SHOW GRANTS ON TABLE sales_db.us_west.recently_added; -- newly created object

You can use SHOW GRANTS ON TABLE <name> to confirm the inherited grant is being surfaced correctly.

Step 5: Revoke the original future and bulk grants¶

Once parity is confirmed, revoke the original grants to complete the migration.

USE ROLE ACCOUNTADMIN;
REVOKE SELECT ON ALL TABLES    IN DATABASE sales_db FROM ROLE analyst;
REVOKE SELECT ON FUTURE TABLES IN DATABASE sales_db FROM ROLE analyst;

The following table summarizes when to consider migrating to inherited grants:

Representative patterns¶

The patterns below show end-to-end role hierarchies built on inherited grants. Each pattern is a target end-state; adapt the role names and container choices to your environment.

Pattern 1: Database read-access pattern¶

Use this pattern when a database serves a single team or function and you want simple read/write/admin tiers.

USE ROLE SECURITYADMIN;

-- Functional roles for the database
CREATE ROLE sales_db_reader;
CREATE ROLE sales_db_writer;
CREATE ROLE sales_db_admin;

-- Hierarchy: writer inherits reader; admin inherits writer
GRANT ROLE sales_db_reader TO ROLE sales_db_writer;
GRANT ROLE sales_db_writer TO ROLE sales_db_admin;

-- Database admin gets MANAGE GRANTS on the database
GRANT MANAGE GRANTS ON DATABASE sales_db TO ROLE sales_db_admin;

-- The database admin defines the inherited grants for each tier
USE ROLE sales_db_admin;

GRANT USAGE ON DATABASE sales_db TO ROLE sales_db_reader;
GRANT INHERITED USAGE  ON ALL SCHEMAS IN DATABASE sales_db TO ROLE sales_db_reader;
GRANT INHERITED SELECT ON ALL TABLES  IN DATABASE sales_db TO ROLE sales_db_reader;
GRANT INHERITED SELECT ON ALL VIEWS   IN DATABASE sales_db TO ROLE sales_db_reader;

GRANT INHERITED INSERT, UPDATE, DELETE ON ALL TABLES IN DATABASE sales_db TO ROLE sales_db_writer;

-- Assign roles to users
USE ROLE SECURITYADMIN;
GRANT ROLE sales_db_reader TO USER alice;
GRANT ROLE sales_db_writer TO USER bob;
GRANT ROLE sales_db_admin  TO USER carol;

Outcome: Three role tiers with consistent read, write, and admin access across every current and future table and view in sales_db. Adding a new table requires no additional grant administration.

Pattern 2: Per-domain pattern (schemas as domains)¶

Use this pattern when a single database holds multiple domains (each represented by a schema) that need independent grant administration.

USE ROLE SECURITYADMIN;

-- One database admin who can delegate further
CREATE ROLE sales_db_admin;
GRANT MANAGE GRANTS ON DATABASE sales_db TO ROLE sales_db_admin WITH GRANT OPTION;

-- Per-domain roles
CREATE ROLE us_west_reader;
CREATE ROLE us_west_lead;
CREATE ROLE eu_reader;
CREATE ROLE eu_lead;

USE ROLE sales_db_admin;

-- Each domain lead is the role that holds MANAGE GRANTS for its schema
GRANT MANAGE GRANTS ON SCHEMA sales_db.us_west TO ROLE us_west_lead;
GRANT MANAGE GRANTS ON SCHEMA sales_db.eu      TO ROLE eu_lead;

-- Each domain lead defines its own inherited grants
USE ROLE us_west_lead;
GRANT USAGE ON DATABASE sales_db TO ROLE us_west_reader;
GRANT USAGE ON SCHEMA sales_db.us_west TO ROLE us_west_reader;
GRANT INHERITED SELECT ON ALL TABLES IN SCHEMA sales_db.us_west TO ROLE us_west_reader;

USE ROLE eu_lead;
GRANT USAGE ON DATABASE sales_db TO ROLE eu_reader;
GRANT USAGE ON SCHEMA sales_db.eu TO ROLE eu_reader;
GRANT INHERITED SELECT ON ALL TABLES IN SCHEMA sales_db.eu TO ROLE eu_reader;

Outcome: Each domain (US West, EU) has its own lead role holding MANAGE GRANTS on its schema, plus its own reader role. Domain leads operate independently without involving the database admin or SECURITYADMIN for routine grant management.

Pattern 3: Application access pattern¶

Use this pattern when a Snowflake-managed application (for example, a governance scanner or data-quality service) needs to read every current and future object in a container.

USE ROLE SECURITYADMIN;
GRANT INHERITED USAGE  ON ALL SCHEMAS IN DATABASE sales_db TO APPLICATION trust_center;
GRANT INHERITED SELECT ON ALL TABLES  IN DATABASE sales_db TO APPLICATION trust_center;
GRANT INHERITED SELECT ON ALL VIEWS   IN DATABASE sales_db TO APPLICATION trust_center;

Outcome: The application can scan every current and future schema, table, and view in sales_db without future-grant proliferation. New objects are covered automatically.

Audit inherited grants¶

Snowflake supports the following approaches to facilitate inherited-grant auditing.

• Use SHOW INHERITED GRANTS IN <container> to enumerate every inherited grant defined in a database or schema. This is the most direct way to see what container-level grants are in effect.
• Use the GRANTS_TO_ROLES view in ACCOUNT_USAGE with IS_INHERITED = 'YES' to find all inherited grants account-wide, and to reconstruct the history of inherited-grant creation and revocation (including revoked grants, which appear with a non-null DELETED_ON value).
• For per-object access reviews, use SHOW GRANTS ON <object>. The response includes both direct and inherited grants applicable to the object, with the source container identified in the INHERITED_FROM column.

Three SHOW commands return inherited grant information, each populating four new columns.

The same four columns appear in the GRANTS_TO_ROLES view in ACCOUNT_USAGE and the INFORMATION_SCHEMA.

Troubleshoot inherited grants¶