Database Roles: Sharing Database Roles with Future Grants Not Allowed¶

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