Managing Snowflake privilege grants, future grants and SCIM-provisioned roles

1.Granting privileges: containers, roles and users

Building a custom role has four steps. Create the role. Grant privileges to it. Grant it to the users who need it. Grant it to a parent role so it joins the hierarchy. The last step is optional, but Snowflake strongly recommends it. Only USERADMIN or higher, or a role with CREATE ROLE, can create roles, and SECURITYADMIN can grant privileges and roles.

Database objects always sit inside a schema, and the schema sits inside a database. A privilege on a table is useless without USAGE (or some other privilege) on the database and schema that contain it. A role that runs queries also needs USAGE on a warehouse to supply the compute. A read-only role for one schema therefore needs four grants.

GRANT USAGE
  ON DATABASE d1
  TO ROLE read_only;

GRANT USAGE
  ON SCHEMA d1.s1
  TO ROLE read_only;

GRANT SELECT
  ON ALL TABLES IN SCHEMA d1.s1
  TO ROLE read_only;

GRANT USAGE
  ON WAREHOUSE w1
  TO ROLE read_only;

When you grant one role to another, the recipient inherits the granted role's privileges whether or not you add WITH GRANT OPTION. The option only adds the right to pass the granted role on to other roles. Privileges can also go directly to users, which is UBAC. Those grants only take effect when the user has all secondary roles enabled. An account that doesn't want direct user grants can turn them off with an account parameter.

ALTER ACCOUNT SET DISABLE_USER_PRIVILEGE_GRANTS = TRUE;

Sources1

2.Future grants for objects that don't exist yet

The ON ALL TABLES grant in the read-only example only covers tables that exist when it runs. Tables created later aren't included. Future grants fill that gap. You define privileges on a type of object in a database or schema, and Snowflake grants them automatically to the chosen role whenever a new object of that type is created. They only set the starting privileges, so you can still grant more or revoke some on each object afterwards.

GRANT SELECT ON  ?  TABLES IN SCHEMA d1.s1 TO ROLE read_only;

Database-level future grants apply in managed access schemas as well as regular ones. You add them with GRANT … ON FUTURE and remove them with REVOKE … ON FUTURE, and in Snowsight that means running SQL in a worksheet. Cloning a database or schema copies its future grants to the clone. Cloning a single object inside a schema applies that schema's future grants to the clone, unless you specify COPY GRANTS. In that case the clone keeps the original object's privileges and doesn't pick up the future grants.

Sources1

3.Automating RBAC with the Snowflake Python API

The grants above can all be scripted. The Snowflake Python API represents account roles with Role and RoleResource, and database roles with DatabaseRole and DatabaseRoleResource. Through RoleResource you can grant and revoke privileges, privileges on all objects, future privileges and roles. Following the advice for automated scripts, run these under a role other than ACCOUNTADMIN.

from snowflake.core.role import Securable

root.roles['my_role'].grant_role(role_type="ROLE", role=Securable(name='my_role_1'))

Database roles are created inside a specific database collection, which matches the fact that a database role is a database-level object. The API can also clone a database role into another database. That helps when every domain database needs the same set of access roles.

from snowflake.core.role import ContainingScope

root.roles['my_role']. ? (
    privileges=["SELECT", "INSERT"],
    securable_type="TABLE",
    containing_scope=ContainingScope(database='my_db', schema='my_schema'),
)

Sources23

4.Driving role membership from the IdP with SCIM

SCIM 2.0 lets an identity provider push users and groups into Snowflake. Each IdP group becomes a Snowflake role, with a one-to-one mapping, and group members become users who hold that role. When someone's group changes in the IdP, their Snowflake access changes with it. You set this up with a SCIM security integration whose RUN_AS_ROLE owns everything the IdP imports. Creating the integration requires CREATE INTEGRATION, which only ACCOUNTADMIN has by default.

use role accountadmin;
create role if not exists generic_scim_provisioner;
grant create user on account to role generic_scim_provisioner;
grant create role on account to role generic_scim_provisioner;
grant role generic_scim_provisioner to role accountadmin;
create or replace security integration generic_scim_provisioning
    type=scim
    scim_client='generic'
    run_as_role='GENERIC_SCIM_PROVISIONER';

Ownership is what makes syncing work. If a role was created by hand, or ownership was moved away from the provisioner, IdP updates to it stop reaching Snowflake. Granting the provisioner MONITOR ROLE on the account lets it see every group, not just those it owns. If you'd rather use something less privileged than ACCOUNTADMIN for setup, use a role with global MANAGE GRANTS, or a custom role that owns all the roles SCIM will manage. For hierarchies, keep in mind that a custom SCIM integration may not support nested groups, so confirm that with your IdP first.

Sources4567

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.GRANT SELECT ON ALL TABLES IN SCHEMA also covers tables created later.Why is that wrong?

   ON ALL only covers objects that exist when the grant runs. New tables need a separate ON FUTURE grant.

   Covered in Future grants for objects that don't exist yet

2. 2.Database-level and schema-level future grants on the same object type add together.Why is that wrong?

   The schema-level future grants win and the database-level ones are ignored for that schema, even when they target a different role.

   Covered in Future grants for objects that don't exist yet

3. 3.Once SCIM is set up, any Snowflake role with a matching name follows IdP group changes.Why is that wrong?

   Only roles owned by the SCIM provisioner role are kept in sync. Roles owned by other roles won't receive IdP updates.

   Covered in Driving role membership from the IdP with SCIM

Practise it for real

Build a least-privilege read-only role for schema d1.s1 that also covers future tables and sits in the SYSADMIN hierarchy

1. 1.As USERADMIN, run CREATE ROLE r1 COMMENT = 'This role has all privileges on schema_1'; (or name it read_only)

   Why: Only USERADMIN or higher, or a role with CREATE ROLE, can create roles

   You should see: The role exists but isn't granted to any user or role yet

2. 2.Grant USAGE on database d1, schema d1.s1 and warehouse w1, then GRANT SELECT ON ALL TABLES IN SCHEMA d1.s1 to the role

   Why: Table privileges only work with USAGE on the containers, and a warehouse is needed to run queries

   You should see: The role can query every table that currently exists in d1.s1

3. 3.Run GRANT SELECT ON FUTURE TABLES IN SCHEMA d1.s1 TO ROLE read_only;

   Why: ON ALL doesn't cover tables created later

   You should see: Tables created in d1.s1 from now on are readable by the role without further grants

4. 4.Run GRANT ROLE r1 TO ROLE sysadmin; using the role you created

   Why: Puts the role in the hierarchy so SYSADMIN inherits its privileges and can manage what it owns

   You should see: The Snowsight roles graph shows the role as a child of SYSADMIN

5. 5.Run GRANT ROLE r1 TO USER smith; and optionally ALTER USER smith SET DEFAULT_ROLE = r1;

   Why: Users only get a role's privileges once the role is granted to them

   You should see: smith can activate the role and query d1.s1, but can't modify data or create objects