10 Rules of Snowflake Object Access:
- Rule 1 : An Account contains both Users and Roles at the Account Level
- Rule 2 : A User will never own an object
- Rule 3 : A Role will always own objects
- Rule 4: Each Object has only a single Role owner
- Rule 5 : Users are granted Roles to gain access to objects
- Rule 6: Only a Role owner can grant access to a object unless the Schema is Managed Access
- Rule 7: If Schema is Managed Access only the Role owner of Schema can grant access to objects within it
- Rule 8 : To access an object we need access to its base objects such as a schema and its database
- Rule 9 : Learn Rules 1 to 8
- Rule 10: Learn Rule 9
Users and Roles
If we review the RBAC Diagram above we should note that the User and Role are not attributes of a database, they exist at the account level. This means that a User can be granted access to a Role which may provide grants to many databases, schemas, objects. The point is that Users and Roles live outside of the Database and can exist prior to any Database existing.
Object Ownership
The Role a user has active when they create an object, becomes the owner of that object. That’s right Roles own objects not Users.
Object Privileges
Snowflakes refers to objects we can grant access to as a Securable Object, a table is a securable object , so are Views. For a User to be able to access an Object it needs a Role granted to it which has Grants on the object but also needs Grants on the full path to that Object. By Path we mean that Objects Schema and also that Objects Database.
With Roles being object owners, if we have many different Roles creating objects in the same Schema the Ownership and Grants could get messy. One solution to this mess is to make a Schema a Managed Access Schema.
Managed Access Schema
Under this model Roles still own objects, but only the Schema Role owner has the power to Grant privileges on objects within its Schema. Roles owners lose the ability to grant access.