Manage data security and dynamic data masking in BigQuery using IAM, Column-level Security, Row-level Security, and Policy Tags, while Snowflake leverages its robust RBAC model, Row Access Policies, and Dynamic Data Masking Policies. Both platforms enable granular control over sensitive data at query time without altering the underlying data.
Here's how to implement these controls:
BigQuery Data Security & Masking
1. Identity and Access Management (IAM): Control access at the dataset, table, and job level. Grant roles like roles/bigquery.dataViewer or custom roles.
2. Column-level Security (CLS) & Dynamic Data Masking (DDM) via Policy Tags: These features are managed through BigQuery Policy Tags, which are part of Data Catalog taxonomies.
Create Policy Tags: Define a taxonomy and policy tags (e.g., PII, Sensitive_Financial) in Data Catalog. Configure masking rules (e.g., SHA256, NULL, custom SQL) for each policy tag.
Apply Policy Tags to Columns: Associate columns with relevant policy tags using DDL.
```sql
-- Apply a policy tag to a column
ALTER TABLE my_dataset.my_table ALTER COLUMN ssn SET OPTIONS(description='{"policy_tags": ["projects/PROJECT_ID/locations/LOCATION/taxonomies/TAXONOMY_ID/policyTags/SSN_POLICY_TAG_ID"]}');
```
Grant Access: Control who sees unmasked data (CLS) or specific masking levels (DDM) by granting IAM roles on the policy tags. Users without roles/bigquery.policyTagViewer for a specific policy tag will see masked data (if a masking rule is defined) or have no access to the column at all (CLS).
```sql
-- Grant access to see unmasked data for a policy tag (CLI command)
bq set-policy-tag-iam-policy --policy-tag=projects/PROJECT_ID/locations/LOCATION/taxonomies/TAXONOMY_ID/policyTags/SSN_POLICY_TAG_ID --member=group:data_stewards@example.com --role=roles/bigquery.policyTagViewer
```
3. Row-level Security (RLS): Filter rows based on user identity or attributes.
```sql
CREATE ROW ACCESS POLICY my_policy
ON my_dataset.my_table
GRANT TO ('group:analysts@example.com')
FILTER USING (user_id = SESSION_USER());
```
Snowflake Data Security & Masking
1. Role-Based Access Control (RBAC): Define roles and grant privileges to databases, schemas, tables, and columns.
2. Row Access Policies: Filter rows for specific roles or users.
```sql
CREATE OR REPLACE ROW ACCESS POLICY my_row_access_policy
AS (user_id VARCHAR) RETURNS BOOLEAN ->
CASE
WHEN CURRENT_ROLE() IN ('ADMIN_ROLE') THEN TRUE
WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') THEN user_id = CURRENT_USER()
ELSE FALSE
END;
ALTER TABLE my_schema.my_table ADD ROW ACCESS POLICY my_row_access_policy ON (user_id);
```
3. Dynamic Data Masking: Apply masking policies to columns. The policy function determines the masked value based on the querying role.
```sql
CREATE OR REPLACE MASKING POLICY my_masking_policy AS (val VARCHAR) RETURNS VARCHAR ->
CASE
WHEN CURRENT_ROLE() IN ('ADMIN_ROLE') THEN val
WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') THEN '**--' || SUBSTR(val, 10, 4) -- Mask SSN except last 4
ELSE '*'
END;
ALTER TABLE my_schema.my_table MODIFY COLUMN ssn SET MASKING POLICY my_masking_policy;
```
Both platforms allow dbt to manage the creation and application of these policies through post-hooks or custom materializations, integrating security directly into your data transformation pipeline.