Using container-level MANAGE GRANTS¶

This topic describes how to grant, delegate, revoke, and audit container-level MANAGE GRANTS.

Get started¶

Container-level MANAGE GRANTS lets you delegate grant administration on a single database or schema to a role, without granting that role access across the entire account. A typical workflow is as follows:

1. Identify the container and the delegated role. Decide which database or schema the role should administer, and choose the narrowest practical scope of authority.
2. Grant MANAGE GRANTS on the container. Use SECURITYADMIN (or another role that already holds account-level MANAGE GRANTS WITH GRANT OPTION) to grant the privilege to the delegated role.
3. Optionally allow further delegation. Include WITH GRANT OPTION if the delegated role should be able to grant MANAGE GRANTS on lower-level containers (for example, schemas inside its database).
4. Audit periodically. Review who holds MANAGE GRANTS on each container, and revoke when no longer required.

Syntax¶

GRANT  MANAGE GRANTS ON { ACCOUNT | DATABASE <name> | SCHEMA <name> }
  TO   { ROLE <role_name> | DATABASE ROLE <db_role_name> }
  [ WITH GRANT OPTION ]

REVOKE MANAGE GRANTS ON { ACCOUNT | DATABASE <name> | SCHEMA <name> }
  FROM { ROLE <role_name> | DATABASE ROLE <db_role_name> }
  [ RESTRICT | CASCADE ]

RESTRICT (default) fails the revocation if any dependent MANAGE GRANTS privileges exist. CASCADE also revokes those dependent MANAGE GRANTS privileges.

How to obtain MANAGE GRANTS on a container¶

A role obtains MANAGE GRANTS on a database or schema in one of two ways:

1. From an account administrator holding MANAGE GRANTS ON ACCOUNT WITH GRANT OPTION:

   Copy code

   USE ROLE SECURITYADMIN;
   GRANT MANAGE GRANTS ON DATABASE sales_db TO ROLE sales_admin WITH GRANT OPTION;
   

2. From an existing role that holds container-level MANAGE GRANTS on the same or a higher container, with WITH GRANT OPTION:

   Copy code

   USE ROLE sales_admin;   -- holds MANAGE GRANTS ON DATABASE sales_db WITH GRANT OPTION
   GRANT MANAGE GRANTS ON SCHEMA sales_db.us_west TO ROLE us_west_lead;
   

The WITH GRANT OPTION clause is required to delegate MANAGE GRANTS further.

Examples¶

Delegate grant management for a database¶

USE ROLE SECURITYADMIN;
GRANT MANAGE GRANTS ON DATABASE sales_db TO ROLE sales_admin WITH GRANT OPTION;

Outcome: sales_admin now holds MANAGE GRANTS on sales_db and can also delegate MANAGE GRANTS on schemas inside it.

Skip-level delegation¶

USE ROLE SECURITYADMIN;
GRANT MANAGE GRANTS ON SCHEMA sales_db.us_west TO ROLE us_west_lead;

Outcome: us_west_lead becomes the role that holds MANAGE GRANTS for sales_db.us_west. No intermediate grant on sales_db is required.

Revoke MANAGE GRANTS from a role with cascade¶

USE ROLE SECURITYADMIN;
REVOKE MANAGE GRANTS ON ACCOUNT FROM ROLE sales_admin CASCADE;

Outcome: Dependent MANAGE GRANTS granted by sales_admin (for example, MANAGE GRANTS on database sales_db or on schemas inside sales_db) are revoked. Regular grants, and non-MANAGE GRANTS inherited grants created by sales_admin, are preserved.

Audit container-level MANAGE GRANTS¶

Snowflake supports the following approaches to facilitate container-level MANAGE GRANTS auditing.

• Use SHOW GRANTS ON DATABASE <name> and SHOW GRANTS ON SCHEMA <name> to enumerate every grant inside a delegated container, including grants made by the role that holds MANAGE GRANTS.
• Use the GRANTS_TO_ROLES view in ACCOUNT_USAGE to reconstruct the history of MANAGE GRANTS delegations, including revoked grants (which appear with a non-null DELETED_ON value).

Find all roles that hold MANAGE GRANTS in the account:

SELECT grantee_name, granted_on, name AS container_name, granted_by, created_on
  FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
  WHERE privilege = 'MANAGE GRANTS'
    AND granted_on IN ('DATABASE', 'SCHEMA')
    AND deleted_on IS NULL
  ORDER BY granted_on, name;

Find all roles that hold MANAGE GRANTS for a specific database:

SHOW GRANTS ON DATABASE sales_db;

Find all containers where a specific role holds MANAGE GRANTS:

SHOW GRANTS TO ROLE sales_admin;