This pattern provides an approach for granting access to schemas containing sensitive data without creating a fork in the RBAC role hierarchy. Forking the RBAC hierarchy is commonly prescribed in order to provide one role set which grants access to non-sensitive data and another with sensitive data access. This privileged role must then be properly inherited and/or activated by the end user, and it results in a duplication of the privileges set; one for non sensitive data and one for sensitive.
This pattern proposes alternatively, to instead grant a privilege set to all objects in a database regardless of their sensitivity. It is then only the USAGE privilege, which is controlled by a separate database specific sensitive role, that would be inherited by the top level role. This effectively eliminates the fork in the hierarchy and simplifies the number of roles a user must request access to . Instead of the user having to request a sensitive role with its own access privileges, they can simply request the enabling of sensitive data access.
This pattern does not prescribe how to populate these objects, perform row or column level security, or grant roles to users; each of which may also be required. The scope of this pattern is simply how to provide visibility to the objects themselves.
This guide is part of a series on Security. The guides are:
This document is for Enterprise and Solution Architects who want to understand the connectivity capabilities and best practices of Snowflake and Snowflake Partner technologies. This document is not intended for use by implementation teams, although an implementation example is provided.
This pattern implements well when the following conditions are true:
Objects in Snowflake are contained in a hierarchy of containers. Databases contain schemas which contain objects. Each level of the hierarchy has its own set of privileges. In order to be able to successfully access data in the lowest level objects which contain data - such as table or a view - the role must have the appropriate privileges on all objects higher in the hierarchy. A role must first have the privilege to view a database, commonly granted with the database usage privilege. Then the role can only see schemas for which the schema usage privilege has been granted. Finally the user must have privileges on the underlying objects.
Although the object containers - meaning database, schema and tables (for example) - are hierarchical, the privileges can be granted out of order, which is what this pattern is suggesting. A role inherits a certain privilege set on all objects in a database - this privilege set can be any combination of CRUD privileges. The role is then granted usage on the database. At this point the role can see the database, and has common privileges on objects - but is unable to view the underlying objects because no schema level privileges have been granted. Now, a user can request permissions to specific schemas. The only privilege the security admin must grant is the usage privilege. Once that usage is granted and properly inherited by a functional role to aggregate the object level privileges along with the usage privilege, the user will be able to access the data set.
The granting of these schema level roles is commonly managed by an enterprise identity governance and access management system. Within this enterprise system, a user requests access to specific data sets which then follows an approval process. Once the proper approvals have occurred, the role containing the usage privilege on the approved schema is assigned to the requesting user's functional role. This granting and inheritance can be implemented using either SCIM2.0 API, JDBC calls to customer stored procedures, or calling procedures or executing SQL directly in Snowflake.
This is a working example of how this pattern could be implemented, within a particular context.
PROD_DBcontains two schemas,
PROD_DB_ROrole is created. The following privileges are granted to the role
PROD_DB_RWrole is created. The following privileges are granted to the role
PROD_DB_ROis granted to
SENSITIVEschema role. It also is not inherited nor does it inherit other object access roles.
IT_ANALYTICS_ROLEis created. This role will inherit the access level roles and be granted to users. This role will be activated by the user.
IT_ANALYTICSgranted to his user.
PROD_DB_RW, after following the approval process, is granted the
IT_ANALYTICSrole. Bill now has the read/write on all objects in the public schema.
PROD_DBbut also requires access to payroll data kept within the sensitive schema.
HR_ANALYSTSfunctional role granted to her user.
HR_ANALYSTSrole and Alice can now read all tables in both the
Fig 1.0 Suggested Approach
Fig 2.0 Traditional Pattern
With a traditional approach of having non-sensitive and sensitive versions of RBAC roles for a database and/or schema, the user must determine both which dataset they should have access to as well as which level of access they should have to this data - and request access to that role. This may not be intuitive to users not properly trained and experienced with Snowflake RBAC. With the model proposed in this pattern, the access level has already been determined, likely based on the organizational role of the user. The only request the user is making is which datasets the user should be able to view.
The benefit of this pattern is when a user is reviewing the possible roles to request access to, they only see three roles and must decide 1) what privilege level do I need and 2) do I need access to sensitive data. These decisions are made independently of each other. In a typical model, this same hierarchy would require at least 4 roles, and each role would be a distinct set of combined privileges. More importantly, a legacy model would require at least 9 grants to be made of privileges to roles whereas the suggested pattern only requires 5. These numbers may seem insignificant, however as implementations of snowflake grow and evolve, simplification of RBAC hierarchies will be critical to successful extensibility and ease of management.