Database Roles: Sharing Database Roles with Future Grants Not Allowed

Attention

This behavior change is in the 2023_05 bundle.

For the current status of the bundle, refer to Bundle History.

The behavior of future grants and database roles is as follows:

Previously:

You can grant future privileges to a database role and grant the database role to a share. There are two scenarios:

  1. Grant the privileges to the database role, and then grant the database role to the share.

    GRANT SELECT ON FUTURE TABLES IN SCHEMA sh TO DATABASE ROLE dbr1;
GRANT DATABASE ROLE dbr1 TO SHARE myshare;
    Copy

  2. Grant the database role to a share, and then grant the future privileges to the database role.

    GRANT DATABASE ROLE dbr1 TO SHARE myshare;
GRANT SELECT ON FUTURE TABLES IN SCHEMA sh TO DATABASE ROLE dbr1;
    Copy

You can use the following commands to identify whether you have database roles that are affected by the pending changes:

SHOW FUTURE GRANTS IN DATABASE parent_db;
SHOW FUTURE GRANTS IN shared_schema;
Copy
Currently:

You will not be able to grant future grants on objects when the database role is granted to a share. Snowflake returns a unique error message depending on the scenario that you try:

  1. With scenario one, the error message is:

    Cannot share a database role with future grants to it.

    Use a REVOKE <privileges> statement to revoke the future grant from the database role. If necessary, update the GRANT <privileges> statement so that it does not specify future grants. Finally, grant the database role to the share.

  2. With scenario two, the error message is:

    Cannot grant future grants to a database role that is granted to a share.

    Modify the GRANT <privileges> statement so that it does not specify future grants.

Ref: 1144

Language: English