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:

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

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:

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.
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.