Delegating Role Grants in Snowflake with WITH GRANT OPTION

This article is an English translation of the following article:
https://dev.classmethod.jp/articles/snowflake-try-with-grant-option-grant-role/

Hi, this is Sagara.

Snowflake now lets you specify WITH GRANT OPTION when granting a role to another role. A role that receives a role WITH GRANT OPTION can then grant that role to other roles.

https://docs.snowflake.com/en/release-notes/2026/other/2026-10-05-role-grant-option

https://docs.snowflake.com/en/sql-reference/sql/grant-role

Previously, granting a role required either the OWNERSHIP privilege on that role or the global MANAGE GRANTS privilege. With this update, a role received WITH GRANT OPTION can be granted without OWNERSHIP or MANAGE GRANTS. This means you can delegate the ability to grant specific roles to another role.

In this article, I tried it out with the following scenario:

• Prepare a database for a department, plus three roles: a department admin role, a data engineer role, and a BI user role
• A central administrator (SECURITYADMIN) grants the data engineer role and the BI user role to the department admin role WITH GRANT OPTION
• Check whether the department admin role can grant the delegated roles to other roles

Feature Overview

With this update, you can specify WITH GRANT OPTION when granting the following three types of roles:

• Account roles (GRANT ROLE)
• Database roles (GRANT DATABASE ROLE)
• Application roles (GRANT APPLICATION ROLE)

The syntax is as follows:

GRANT ROLE <name> TO ROLE <parent_role_name> [ WITH GRANT OPTION ]

A role that receives a role WITH GRANT OPTION can grant that role to other roles. Because WITH GRANT OPTION can also be specified on that grant, you can delegate granting authority further down to lower-level roles.

To revoke a granted role, use REVOKE ROLE. Specifying REVOKE GRANT OPTION FOR revokes only the grant option. The role grant itself remains, and the recipient continues to inherit the privileges of the granted role.

REVOKE [ GRANT OPTION FOR ] ROLE <name> FROM ROLE <parent_role_name> [ RESTRICT | CASCADE ]

RESTRICT and CASCADE control how grants derived from that role grant (re-grants) are handled:

• RESTRICT (default): The revoke fails if derived grants exist
• CASCADE: Derived grants are revoked as well

https://docs.snowflake.com/en/sql-reference/sql/revoke-role

In SHOW GRANTS TO ROLE, a role grant is displayed as the USAGE privilege on the granted role. If the role was granted WITH GRANT OPTION, the grant_option column shows TRUE.

What's Different from Before

According to the Access control requirements for GRANT ROLE in the official docs, any one of the following is required to grant a role:

• The OWNERSHIP privilege on the role being granted
• Or the global MANAGE GRANTS privilege (by default, held only by SECURITYADMIN and higher roles)
• (New) A role that received that role WITH GRANT OPTION

MANAGE GRANTS is a powerful privilege that lets you grant and revoke privileges on any object in the account, so it's too strong to give to a department administrator. You could transfer OWNERSHIP to the department admin role instead, but that hands over ownership of the role itself.

With WITH GRANT OPTION, ownership of the role stays with a central administrator such as SECURITYADMIN, while only the ability to grant the role is delegated to the department administrator.

Preparation

For this test, I assume access management for a sales department. The objects are as follows:

Type	Name	Purpose
Database	SALES_DB	Database for the sales department
Role	SALES_ADMIN	Department admin role
Role	SALES_ENGINEER	Role for data engineers
Role	SALES_BI_USER	Role for BI users

1. Create the Roles

Use the USERADMIN role to create the three roles.

USE ROLE USERADMIN;

CREATE ROLE SALES_ADMIN;
CREATE ROLE SALES_ENGINEER;
CREATE ROLE SALES_BI_USER;

Place the department admin role under SYSADMIN, and also grant it to your own user for testing.

USE ROLE SECURITYADMIN;

GRANT ROLE SALES_ADMIN TO ROLE SYSADMIN;

-- Replace <your_user_name> with your own user name
GRANT ROLE SALES_ADMIN TO USER <your_user_name>;

2. Create the Database and Grant Privileges

Use the SYSADMIN role to create the department database and grant the USAGE privilege to the data engineer role and the BI user role.

USE ROLE SYSADMIN;

CREATE DATABASE SALES_DB;

GRANT USAGE ON DATABASE SALES_DB TO ROLE SALES_ENGINEER;
GRANT USAGE ON DATABASE SALES_DB TO ROLE SALES_BI_USER;

Trying It Out

1. Grant Roles to the Department Admin Role WITH GRANT OPTION

Use the SECURITYADMIN role to grant the data engineer role and the BI user role to the department admin role WITH GRANT OPTION.

USE ROLE SECURITYADMIN;

GRANT ROLE SALES_ENGINEER TO ROLE SALES_ADMIN WITH GRANT OPTION;
GRANT ROLE SALES_BI_USER TO ROLE SALES_ADMIN WITH GRANT OPTION;

Once granted, verify with SHOW GRANTS TO ROLE.

SHOW GRANTS TO ROLE SALES_ADMIN;

You should see the USAGE privilege on SALES_ENGINEER and SALES_BI_USER, with the grant_option column set to true.

2. Grant a Role from the Department Admin Role

Switch to the SALES_ADMIN role and grant the SALES_BI_USER role to the SALES_ENGINEER role. The idea is to give data engineers the same privileges as BI users.

USE SECONDARY ROLES NONE;
USE ROLE SALES_ADMIN;

GRANT ROLE SALES_BI_USER TO ROLE SALES_ENGINEER;

You should see Statement executed successfully. with no errors. Even though SALES_ADMIN has neither OWNERSHIP on SALES_BI_USER nor MANAGE GRANTS, it was able to grant the role.

Use SHOW GRANTS OF ROLE to check which roles SALES_BI_USER has been granted to.

SHOW GRANTS OF ROLE SALES_BI_USER;

You should see the grant to SALES_ENGINEER, with the granted_by column showing SALES_ADMIN.

3. Revoke the Grant Option

Finally, use REVOKE GRANT OPTION FOR to revoke only the grant option on SALES_BI_USER from SALES_ADMIN.

Since SALES_ADMIN granted the role to SALES_ENGINEER in step 2, the revoke should fail under the default RESTRICT behavior.

USE ROLE SECURITYADMIN;

REVOKE GRANT OPTION FOR ROLE SALES_BI_USER FROM ROLE SALES_ADMIN;

You should get an error indicating that dependent grants exist.

Next, revoke with CASCADE. This time it succeeds without errors.

REVOKE GRANT OPTION FOR ROLE SALES_BI_USER FROM ROLE SALES_ADMIN CASCADE;

SHOW GRANTS OF ROLE SALES_BI_USER;

You should see the following:

• The grant to SALES_ADMIN still exists (in SHOW GRANTS TO ROLE SALES_ADMIN, the grant_option column for SALES_BI_USER is false)
• The grant to SALES_ENGINEER made by SALES_ADMIN has been revoked

Conclusion

Now that Snowflake supports granting roles WITH GRANT OPTION, I tried a scenario where role-granting authority is delegated to a department-level admin role.

Previously, if you wanted a department administrator to handle role grants, your only options were to hand over the role's OWNERSHIP or grant the account-wide MANAGE GRANTS privilege. With this update, ownership of roles stays with the central administrator, while only the ability to grant the department's own roles is delegated to the department administrator. This is a very welcome update for environments where multiple departments share a single account!

If you want each department to run its own data platform operations while keeping everything in one account, give this a try.