Custom Roles, Role Inheritance and Granting Object Access in Snowflake

1.Why and how to create custom roles

System-defined roles exist to manage the account. They aren't meant to carry access to business data. Snowflake advises against adding privileges to them. Its reason is that account-management privileges and entity-specific privileges shouldn't be mixed in one role. If a system role needs extra access, grant the privileges to a custom role and then grant that custom role to the system role.

Custom roles are also how you apply least privilege. Snowflake recommends creating custom roles that match your organisation's business functions, so that each one allows SQL actions on a narrow set of objects. USERADMIN, any role above it, or any role granted CREATE ROLE can create account-level custom roles. A database owner can create custom database roles.

A new role starts out isolated. It isn't assigned to any user and isn't granted to any other role. A role that nobody holds does nothing, so creating it is the first of four steps.

Sources12

2.Role inheritance when granting and revoking

Granting one role to another builds a hierarchy. Privileges flow upward: every role above a role inherits that role's privileges. If r1 is granted to a parent role, and the parent is granted to SYSADMIN, then SYSADMIN holds everything r1 holds. That includes ownership. If a custom role owns objects, the roles above it own those objects indirectly and can manage them.

GRANT ROLE r1 TO ROLE sysadmin WITH GRANT OPTION;

WITH GRANT OPTION does not control inheritance. A parent inherits the role with or without it. The option only lets the parent grant the role on to other roles.

A custom role that isn't connected to SYSADMIN leaves a gap. By default, not even ACCOUNTADMIN can modify or drop objects created by that role. Only roles with MANAGE GRANTS, which is SECURITYADMIN by default, can see those objects and change their grants. This is why the recommended design puts the top-most custom role under SYSADMIN.

Inheritance follows grants, not ownership. Owning a role lets you manage that role, but it doesn't give you the role's privileges. The only way to inherit them is to have the role granted to yours.

The same rule tells you what a revoke does. Parents get their privileges *through* the child, so removing a privilege from a child role also removes it from every role above that got it only through the child. Revoking the role grant itself, with REVOKE ROLE, cuts the whole branch off from the parent. A grant to PUBLIC needs particular care, because PUBLIC is automatically granted to every user and role. A privilege granted to PUBLIC reaches everyone until it is revoked from PUBLIC. Granting it to a narrower role afterwards doesn't remove that wide access. There is one limit on revoking: privileges that Snowflake granted to the system-defined roles can't be revoked. SECURITYADMIN can revoke any other grant through MANAGE GRANTS.

Sources21

3.Granting access to objects inside a database

Database objects sit inside containers: tables, views, functions and stages belong to a schema, and the schema belongs to a database. A privilege on the object alone isn't enough. The role also needs USAGE, or another privilege, on both the database and the schema that contain it. To run a query, it also needs USAGE on a warehouse for compute.

The documentation shows this with a read-only role. The role can query every table in d1.s1 but can't change data, create objects or drop tables:

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;

ON ALL TABLES only covers tables that exist now. Tables created later need a future grant:

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

GRANT  ? 
  ON SCHEMA d1.s1
  TO ROLE read_only;

Sources3

4.Access roles and functional roles

Combining these grants with inheritance gives the pattern Snowflake recommends for matching access to business functions. Grant privileges on database objects and account objects, such as warehouses, to access roles. Then grant the access roles to functional roles that match job functions. A higher-level functional role can be the parent of lower-level ones when its job should include theirs. Finally, grant the top functional roles to SYSADMIN.

Take the documentation's example. An account has a fin database for payroll and an hr database for employee data. Accountants need read-write access to fin and read-only access to hr. Analysts need read-only access to both. You would build three access roles: fin read-write, fin read-only and hr read-only. Then you would grant the right combination to an accountant role and an analyst role. When a requirement changes, you change one grant between roles instead of re-granting privileges on many objects.

Sources3

Exam traps

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

1. 1.The role that owns another role automatically gets that role's privileges.Why is that wrong?

   Owning a role only gives control over the role itself. To inherit its privileges, the role must be granted to yours as part of a role hierarchy.

   Covered in Role inheritance when granting and revoking

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

   It only covers tables that already exist. New tables need a separate GRANT ... ON FUTURE TABLES.

   Covered in Granting access to objects inside a database

Practise it for real

Create a read-only custom role for schema d1.s1, connect it to the role hierarchy, and assign it to a user.

1. 1.As USERADMIN, run CREATE ROLE read_only;

   Why: USERADMIN, or a role with CREATE ROLE, can create custom roles.

   You should see: The role exists, but no user or role holds it yet.

2. 2.Grant USAGE on database d1, USAGE on schema d1.s1, SELECT on all tables in d1.s1, and USAGE on warehouse w1 to read_only.

   Why: A query needs privileges on the database and schema that contain the table, plus a warehouse for compute.

   You should see: SHOW GRANTS ON SCHEMA d1.s1 lists USAGE granted to READ_ONLY.

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

   Why: ON ALL TABLES only covers tables that already exist.

   You should see: Tables created later in d1.s1 are readable by read_only.

4. 4.Grant read_only to SYSADMIN, then grant it to a user and set it as their default role.

   Why: Connecting the role to SYSADMIN lets system administrators manage it, and the user grant puts it to use.

   You should see: The user can query d1.s1 tables but cannot insert, create or drop objects.