Snowflake Access Control : Mastering the Features for Data Privacy and Regulatory Compliance

Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Part I: Background Chapter 1: What Is Access Control? Access Control Paradigms Role-Based Access Control (RBAC) Data Democratization Principle of Least Privilege (PLP) Rule-Based Access Control Attribute-Based Access Control (ABAC) Principle of Separation of Duties Access Control Methods Groups or Roles Lookup Tables and Mappings Miscellaneous Rules Wrapping Up Chapter 2: Data Types Requiring Access Control Personally Identifiable Information General PII Multimedia Protected Health Information (PHI) Financial Information Social Security Numbers and National Identity Numbers Passwords Non-PII Personal Data Anonymized Data Tokenized Data Tokenizing with Mock Data Using a One-Way Hash Encrypting Data Aggregated Data Internal Non-sensitive Information Publicly Available Information Other Sensitive Data Free Text Fields Financial Reporting Data Key Takeaways Chapter 3: Data Privacy Laws and Regulatory Drivers Internet Privacy GDPR Definitions Who Is Affected? Special Category of Personal Data Data Processing Principles Data Minimization Storage Limitation Integrity and Confidentiality Handling Data with GDPR Compliance APPI Definitions Who Is Affected? Handling Data with APPI Compliance CCPA Definitions Who Is Affected? Handling Data with CCPA Compliance US State General Privacy Regulations SOX Definitions Who Is Affected? Handling Data with SOX Compliance SOX Scope Creating Financial Reports Restricting Access HIPAA Definitions Who Is Affected? Handling Data with HIPAA Compliance Future Regulations Generalizing Data Privacy Regulations Key Takeaways Sources Chapter 4: Permission Types Permission Type Concepts Privilege Scope Categories Global and Account Databases, Warehouses, and Other Account Objects Schemas Schema Objects Snowflake Permission Types General Global and Account Databases Schemas Tables and Views Warehouses Granting and Revoking Privileges Granting Privileges Revoking Privileges Privilege Constraints Permission Modifiers All Future Grants With Grant Option Working with Permissions Read Write Dataset Admin System Admin Account Admin Other Specialized Roles Key Takeaways Part II: Creating Roles Chapter 5: Functional Roles: What a Person Does What Are Roles in Snowflake? How Many Users Should Be in Each Role? What Are Functional Roles? Using Functional Roles for Job Function Using Functional Roles for a Dataset Why Should I Use Functional Roles? How Do I Use Functional Roles? Who Owns a Functional Role? Key Takeaways Chapter 6: Team Roles: Who a Person Is What Are Team Roles? Why Use Team Roles? How Do I Use Team Roles? Who Owns Team Roles? When Should I Use Team Roles and When Should I Use Functional Roles? How Can I Use Team Roles and Functional Roles Together? Key Takeaways Chapter 7: Assuming a Primary Role What Is a Primary Role? Default Roles How Do I Assume a Primary Role? Using SQL Using the Snowflake User Interface Using Python Connector Other Services How Do Primary Roles Work? Key Takeaways Chapter 8: Secondary Roles What Are Secondary Roles? Why Should I Use Secondary Roles? How Do I Use Secondary Roles? Enabling Secondary Roles for Users Enabling Secondary Roles on Integrations Logging and Ownership Disabling Secondary Roles Key Takeaways Part III: Granting Permissions to Roles Chapter 9: Role Inheritance What Is Role Inheritance? Why Should I Use Role Inheritance? How Do I Use Role Inheritance? Creating a Hierarchy Functional Role Hierarchy Team Role Hierarchy Functional and Team Role Hierarchy Privilege Hierarchy Role Inheritance and Specialty Roles End User Experience Naming Conventions Role Inheritance and Secondary Roles Logging with Role Inheritance Key Takeaways Chapter 10: Account- and Database-Level Privileges Account Level User and Role Management Creating Account-Level Objects Monitoring Activity Miscellaneous Database Level Key Takeaways Chapter 11: Schema-Level Privileges What Is Schema-Level Access? Why Use Schema-Level Access? Schema-Level Privileges Administrative Privileges Managed Access Monitoring Privileges Read Privileges Write Privileges Data Engineering Platform Privileges Bulk Grants on Schema Objects All Future Grants Mapping Roles to Schemas Team Roles Functional Roles Specialized Schemas Key Takeaways Chapter 12: Table- and View-Level Privileges What Is Table-Level Access Control? Why Use Table-Level Access Control? What Are the Different Types of Views? Standard Views Materialized Views Secure Views Table- and View-Level Privileges Read Privileges Write Privileges Admin Privileges How Do I Implement Table-Level Access Control? How Does Table Level Work with Schema Level? Key Takeaways Chapter 13: Row-Level Permissioning and Fine-Grained Access Control What Is Row-Level Permissioning? Why Use Row-Level Permissioning? How Do I Use Row-Level Permissioning? Row Access Policies vs. Secure Views Creating Row Access Policies Creating a Basic Row Access Policy Creating a Row Access Policy Using a Mapping Table Creating More Complex Row Access Policies Removing a Row Access Policy Altering Row Access Policies Creating a Python Script to Generate Row Access Policies Constraints Using Secure Views Creating a Basic Secure View Creating a Secure View Using a Mapping Table Creating More Complex Secure Views Creating a Python Script to Generate Secure Views Tips and Tricks User-Defined Functions Aggregate Statistics Row-Level Permissioning with Secondary Roles Key Takeaways Chapter 14: Column-Level Permissioning and Data Masking What Is Column-Level Permissioning? Why Use Column-Level Permissioning? How Do I Implement Column-Level Permissioning? Using Dynamic Masking Policies Creating a Basic Masking Policy Creating a Python Script to Generate Masking Policies Masking JSON Data Constraints Using Secure Views Creating a Basic Secure View Creating a Python Script to Generate Secure Views Using Tokenization Combining Column-Level and Row-Level Permissions Tips and Tricks Categorize Data Types Create UDFs Use Consistent Naming Conventions Key Takeaways Part IV: Operationally Managing Access Control Chapter 15: Secure Data Sharing What Is Secure Data Sharing? Why Use Secure Data Shares? How Do I Use Secure Data Shares? Provider Account Create a Share Grant Object Privileges to Share Verify Share Privileges Optionally Create a Reader Account Share with External Account Consumer Account Create Database from Share Grant Privileges on Share Revoking Access and Dropping Shares Revoking Object Privileges from a Share Revoking Access to a Share Dropping a Share Constraints Secure Views Cross-Region and Cross-Cloud Storage Platform CURRENT_USER and CURRENT_ROLE Functions Granular Access Control with Shares Access Control by Account Consuming Account RBAC Key Takeaways Chapter 16: Separating Production from Development What Does It Mean to Separate Production from Development? Why Separate Production from Development? How Do I Separate Production from Development? Types of Users to Support Platform Developers Pipeline Developers Working with External Sources SQL Developers Transforming Data Visualization Developers Creating a Dev Environment Using Separate Schemas Using Separate Databases Using Separate Accounts Populating Dev Data Production Data in Separate Schemas Production Data in Separate Databases Production Data in a Separate Account Synthetic Data RBAC in Dev For All Setups Separate Schemas Separate Database Separate Account Connecting to Dev Key Takeaways Chapter 17: Upstream and Downstream Services Upstream Services What Are Upstream Services? How Do I Maintain RBAC with Upstream Services? Data Sources Compute and Orchestration Cloud Storage Downstream Services What Are Downstream Services? How Do I Maintain RBAC with Downstream Services? Services That Are Upstream and Downstream Key Takeaways Chapter 18: Managing Access Requests Role Creation and Population Role Discovery Requests for Existing Roles Role Owners With Grant Option Using a Ticketing System Building a Custom Tool Key Takeaways Index Understand the different access control paradigms available in the Snowflake Data Cloud and learn how to implement access control in support of data privacy and compliance with regulations such as GDPR, APPI, CCPA, and SOX. The information in this book will help you and your organization adhere to privacy requirements that are important to consumers and becoming codified in the law. You will learn to protect your valuable data from those who should not see it while making it accessible to the analysts whom you trust to mine the data and create business value for your organization. Snowflake is increasingly the choice for companies looking to move to a data warehousing solution, and security is an increasing concern due to recent high-profile attacks. This book shows how to use Snowflake's wide range of features that support access control, making it easier to protect data access from the data origination point all the way to the presentation and visualization layer. Reading this book helps you embrace the benefits of securing data and provide valuable support for data analysis while also protecting the rights and privacy of the consumers and customers with whom you do business. What You Will Learn Identify data that is sensitive and should be restricted Implement access control in the Snowflake Data Cloud Choose the right access control paradigm for your organization Comply with CCPA, GDPR, SOX, APPI, and similar privacy regulations Take advantage of recognized best practices for role-based access control Prevent upstream and downstream services from subverting your access control Benefit from access control features unique to the Snowflake Data Cloud Who This Book Is For Data engineers, database administrators, and engineering managers who want to improve their access control model; those whose access control model is not meeting privacy and regulatory requirements; those new to Snowflake who want to benefit from access control features that are unique to the platform; technology leaders in organizations that have just gone public and are now required to conform to SOX reporting requirements