- BigQuery - Home
- BigQuery - Overview
- BigQuery - Initial Setup
- BigQuery vs Local SQL Engines
- BigQuery - Google Cloud Console
- BigQuery - Google Cloud Hierarchy
- What is Dremel?
- What is BigQuery Studio?
- BigQuery - Datasets
- BigQuery - Tables
- BigQuery - Views
- BigQuery - Create Table
- BigQuery - Basic Schema Design
- BigQuery - Alter Table
- BigQuery - Copy Table
- Delete and Recover Table
- BigQuery - Populate Table
- Standard SQL vs Legacy SQL
- BigQuery - Write First Query
- BigQuery - CRUD Operations
- Partitioning & Clustering
- BigQuery - Data Types
- BigQuery - Complex Data Types
- BigQuery - STRUCT Data Type
- BigQuery - ARRAY Data Type
- BigQuery - JSON Data Type
- BigQuery - Table Metadata
- BigQuery - User-defined Functions
- Connecting to External Sources
- Integrate Scheduled Queries
- Integrate BigQuery API
- BigQuery - Integrate Airflow
- Integrate Connected Sheets
- Integrate Data Transfers
- BigQuery - Materialized View
- BigQuery - Roles & Permissions
- BigQuery - Query Optimization
- BigQuery - BI Engine
- Monitoring Usage & Performance
- BigQuery - Data Warehouse
- Challenges & Best Practices
BigQuery - Roles & Permissions
Running a query within the BigQuery Studio UI is deceptively seamless. Since a developer is signed into their Google Cloud Platform account, there is no need to authenticate. However, behind the scenes, certain restrictions and guardrails assure developers are only able to take certain actions within a project.
Identity Access and Management (IAM) Roles
These restrictions or designations are known as roles and permissions. Within GCP, these are known as Identity Access and Management (IAM) roles.
Broadly, these roles fit into 3 tiers −
- BigQuery Admin
- BigQuery Data Editor
- BigQuery User
1. BigQuery Admin
BigQuery admins can do anything within a project such as create or delete tables and start and stop jobs from runningeven those initiated by other users.
2. BigQuery Data Editor
A BigQuery data editor has slightly fewer permissions. While they can read, update and delete tables or views, they lack control and authority at a project level and cannot exert control over other users' jobs.
3. BigQuery User
BigQuery users are the lowest tier of BigQuery IAM roles. They are extremely limited when it comes to accessing and manipulating resources. Among their limited abilities: Listing tables and accessing metadata.
Playing with BigQuery on your own won't necessitate knowledge of any of these roles or permissions. However, as you work with enterprise-scale data, understanding the roles and permissions can help accelerate solutions to access issues or come in handy when provisioning service accounts.
BigQuery: Policy Tags & PII
Just like BigQuery admins can grant permissions to and exert influence over users with lower-level access, they can also control what data individuals can see and interact with. This can be accomplished using policy tags.
What are Policy Tags?
A policy tag is essentially a censor bar for an organization's data. Admins can apply this tag to block users within the organization from accessing sensitive data. While some aspect of determining what constitutes sensitive data is subjective, there is also an objective definition.
What is Personally Identifiable Information (PII)?
In data governance, sensitive data is known as Personally Identifiable Information (PII). PII includes any attribute that can be used to immediately and intimately identify a given individual. It includes the following information −
- Phone number
- Biometric information
- Social security number (US)
- Credit card numbers
Any of the above is considered to be extremely sensitive information that must be carefully guarded. To guide protection, GCP has identified 150+ PII attributes in documentation for its data governance product, Data Loss Prevention.
Policy Tags can be Configured
Policy tags can also be issued to protect an organization against internal users who shouldn't have access to business-critical information like revenue data.
Policy tags can be configured and then applied in BigQuery by −
- Selecting a table
- Hitting "edit schema"
- Selecting all columns that may contain sensitive information
- Applying configured policy tag
Developers can tell when such a tag has been applied because it will appear as a gray box next to a field name in a table's schema.
