Mastering the Basics with RBAC, Part III: Padlocking Snowflake Schemas

A Snowflake Managed Access Schemas Story…

In May, my son completes an undergraduate degree in mechanical engineering. He was born to be an engineer. At four he built Lego’s 593-part Red Baron triplane. At nine, he dismantled and rebuilt engines on his RC S-Maxx. At thirteen, he built a robot that navigated a labyrinth and then extinguished a flame. He’s about to embark on a career in motorsport.

But he never learned to put away his dad’s tools. I’d find my screwdrivers in cardboard boxes of old toys. Needle nose pliers under his bed. Socket wrench sets with infuriating gaps. I threatened, but never made good on a plan to padlock my tools. “You’ll have to come and ask me when you want to use my tools. This has to stop!” 

Snowflake’s managed access schema solves this problem in your database (you can download a trial version and work with the permissions here). It provides control over who grants permissions. Building out a solid RBAC implementation necessitates taking control over the source and governance of permissions. A managed access schema is the padlock to control discretionary access control, grant options, and hard-to-detect grants from object owners. RBAC without it will be unsustainable. 

In the same way that some housekeeping on the system roles cleans up administration as preparation for RBAC, battening down schemas makes the creation and intentional RBAC structure possible. Arbitrary and ad-hoc grants within Snowflake schemas break the boundaries between functional and data access roles. They create security problems through unanticipated access to database objects, and, if you have a strategic framework for your roles, subvert them. Specifically, object owners can grant discretionary permissions and grant options, distorting the intentions of the role definition. 

When a schema owner can’t centralize control over her schema, she is working with an object–based managed access approach and the best-laid plans for RBAC governance won’t. Essentially, it’s unbridled, discretionary access. Snowflake’s managed access schema protects the integrity of the roles and any RBAC approach you intend to implement and consolidate. 

In the interactive visualization, you can get a quick glimpse at the success and error messages that Snowflake returns when managed access is enabled. You can explore the relationship between roles, usage and how ownerships play out. Additionally, you can become familiar with Snowflake’s managed access error codes #003509, #003514 and #003510. To understand why these occur is to gain insight into administering a managed access schema. 

Ideally, you will see errors returned with the following query. Assuming you’re already using managed access Snowflake schemas, the result set shows your managed access schemas are doing what they’re supposed to: 

select database_name, 
       schema_name, 
       error_code, 
       error_message, 
       query_text, 
       role_name, 
       user_name 
  from "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" 
 where error_code in ('003509', '003514', '003510');

To bring this home:  

I never made good on my threat to padlock the toolbox. The best engineer in the house needed the flexibility to create. A little chaos in the development environment was worth the final result. But there’s an epilogue. My son was home last summer, and he brought all of his tools with him, and nobody–nobody–was allowed to touch them without his permission.  

Managed Access. 

Signing off with a question and a query… 

Do you know who is making grants in your unmanaged Snowflake schemas that isn’t the schema owner? Consider whether a managed access schema will tighten the controls and governance around the permissions for your roles. 

select table_catalog,  
        table_schema,  
        schema_owner,        
        privilege,  
        granted_by,  
        granted_on,  
        name,  
        granted_to,  
        grantee_name,  
        grant_option 
   from grants_to_roles gtr 
   join schemata s 
     on s.catalog_name = gtr.table_catalog 
    and s.schema_name = gtr.table_schema 
  where deleted_on is null 
    and deleted is null 
    and granted_by not in ('ACCOUNTADMIN', 'SECURITYADMIN') //add other roles with MANAGE GRANTS if applicable 
    and is_managed_access = 'NO' 
    and schema_owner <> granted_by 
  order by  
        table_catalog,  
        table_schema;

Want to see how you can take your Snowflake migration to the next level? Learn more about how our experts can help your business today.

About the Author

Adam Nathan is the Director of Sales Engineering at CoEnterprise and leads our consultancy’s Snowflake practice. With over two decades in data warehousing, business intelligence, and advanced analytics, he’s passionate about supporting clients in the adoption and growth of their Snowflake implementations. Outreach for Snowflake consulting support, thoughtful suggestions, and irate critiques on these posts are all welcome, in that order. You can reach him at adam.nathan@coenterprise.com.