Snowflake Access Control: DAC, RBAC, and Roles

1.Three access control models in one framework

Snowflake does not use a single access control model. It combines three. Discretionary Access Control (DAC) means every object has an owner, and that owner decides who else gets access. Role-based Access Control (RBAC) means privileges are granted to roles, and roles are granted to users. User-based Access Control (UBAC) means privileges are granted directly to users. UBAC has a catch: privileges granted directly to a user only count when the session has secondary roles set to ALL.

Four terms run through the rest of this lesson. A securable object is anything access can be granted on. A role is an entity that privileges are granted to. A privilege is a defined level of access to an object. A user is an identity, either a person or a service. Privileges can also be granted to users, but RBAC is the usual way to manage access. Roles can be granted to other roles, which creates a role hierarchy. In that hierarchy, a role inherits the privileges of every role below it.

The documentation adds that no super-user exists who can skip these checks: every action needs the right privileges.

Sources1

2.The securable object hierarchy and ownership

Every securable object sits inside a hierarchy of containers. The organization is at the top. Each account sits under it and holds all of its databases. Each database holds schemas. Each schema holds objects such as tables, views, functions and stages.

Ownership is what makes DAC work. Owning an object means a role holds the OWNERSHIP privilege on it. Each object has exactly one owner role, which is the role that created it unless ownership is changed. GRANT OWNERSHIP transfers ownership to another role, and that includes database roles. Every user who has the owner role shares control of the object.

In a regular schema, the owner role has every privilege on its object by default, including the right to grant or revoke privileges to other roles. A managed access schema centralizes those decisions instead. Object owners can no longer grant. Only the schema owner, or a role with the MANAGE GRANTS privilege, can grant privileges on objects in the schema, and that includes future grants.

One more ownership rule is easy to miss. Owning a *role* is not the same as inheriting from it. A role that owns another role does not get that role's privileges. Privileges are inherited only through the role hierarchy.

Sources1

3.System-defined roles

Every account comes with a small set of system-defined roles. They cannot be dropped, and the privileges Snowflake grants them cannot be revoked. You can grant them extra privileges, but Snowflake recommends against it, because these roles hold account-management privileges and mixing those with privileges on specific objects is poor practice. If one of them needs more access, grant that access to a custom role and grant the custom role to the system-defined role.

The roles also form their own hierarchy. ACCOUNTADMIN contains both SYSADMIN and SECURITYADMIN, and USERADMIN is granted to SECURITYADMIN. Note that SECURITYADMIN's MANAGE GRANTS privilege only lets it grant and revoke. It does not let SECURITYADMIN create objects. For example, to create a database role, SECURITYADMIN also needs the CREATE DATABASE ROLE privilege.

PUBLIC is also the fallback role when a session starts. If the connection does not specify a role and the user has no default role, the session uses PUBLIC. PUBLIC suits cases where no explicit access control is needed and all users are treated equally.

Sources1

4.Functional roles: account, database, and custom roles

The roles you create for business functions differ mainly in scope.

- An account role can be granted privileges on any object in the account. - A database role is limited to one database and the objects inside it. You grant it privileges on objects in that same database.

Any role you create yourself is a custom role. USERADMIN, any higher role, or any role with the CREATE ROLE privilege can create custom account roles. Custom database roles are created by the database owner. A new role starts out unattached: it is not granted to any user or to any other role.

Database roles have one important limit. They cannot be activated directly in a session. To use a database role's privileges, grant the database role to an account role, and activate the account role. The reverse grant is not allowed: account roles cannot be granted to database roles.

Snowflake recommends building custom roles into a hierarchy whose top role is granted to SYSADMIN. That way system administrators can manage every object in the account, while user and role management stays with USERADMIN. If a custom role is left outside that hierarchy, SYSADMIN cannot manage its objects. Only roles with MANAGE GRANTS can then see those objects and change their grants, and by default that means only SECURITYADMIN.

Sources1

5.Primary and secondary roles in a session

A session always has exactly one primary role, but it can have any number of secondary roles active at once. The primary role comes from the role named in the connection, or from the user's default role, or from PUBLIC if neither is set. USE ROLE changes the primary role during the session. USE SECONDARY ROLES changes the secondary roles.

The two kinds of role authorize different things. CREATE statements are authorized only by the primary role, and every new object is owned by the primary role. For any other statement, privileges from the primary role, the secondary roles and all the roles they inherit are combined. This is why secondary roles are handy for cross-database joins: without them, you would need a parent role that held access to both databases. Database roles can never be primary or secondary roles.

USE SECONDARY ROLES {
      ALL
    | NONE
    | <role_name> [ , <role_name> ... ]
  }

ALL activates every role granted to the user, in addition to the primary role. The set is worked out again for each statement, so roles granted mid-session take effect immediately and revoked roles stop working. NONE turns secondary roles off, which leaves the primary role as the only source of authorization. If you name specific roles instead, each one must already be granted to the user, or the command fails.

USE SECONDARY ROLES  ? ;

Sources12

Exam traps

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

1. 1.A database role can be activated with USE ROLE or used as a secondary role.Why is that wrong?

   A database role can be neither a primary nor a secondary role. You have to grant it to an account role, and only account roles can be activated in a session.

   Covered in Functional roles: account, database, and custom roles

2. 2.If a secondary role has the CREATE privilege in a schema, the user can create objects there.Why is that wrong?

   CREATE statements are authorized only by the primary role, and the new object is owned by the primary role. Secondary roles only authorize other kinds of SQL actions.

   Covered in Primary and secondary roles in a session

3. 3.Because SECURITYADMIN holds MANAGE GRANTS, it can create any object in the account.Why is that wrong?

   MANAGE GRANTS only lets SECURITYADMIN grant and revoke privileges. To create objects it needs the matching CREATE privilege, for example CREATE DATABASE ROLE.

   Covered in System-defined roles