Module 17: Security & Enterprise Governance

%sql
SELECT current_user() AS me,
       is_account_group_member('users') AS in_users_group,
       is_member('admins') AS is_workspace_admin

• Manage identities at the account level, from your identity provider. Avoid ad-hoc workspace users
• Least privilege: the smallest grant that lets the work happen
• Owners of production objects should be groups

• A service principal has its own identity, so production jobs don't depend on a person who may leave
• Run production jobs as a service principal (run_as in a job or bundle)
• Give it only the privileges the pipeline needs: USE CATALOG, USE SCHEMA, SELECT or MODIFY on specific schemas
• Only service principal users can run a job as it. Managers don't get this automatically
• Prefer OAuth to personal access tokens: short-lived and revocable. In CI use OIDC (workload identity federation) where supported
• Rotate secrets and tokens, and set expiry

Concept: a secret scope holds secrets. Code reads them with dbutils.secrets.get. Output shows [REDACTED].

# try this from a notebook: create a scope and a secret with the SDK
from databricks.sdk import WorkspaceClient

w = WorkspaceClient()
w.secrets.create_scope(scope="course_scope")
w.secrets.put_secret(scope="course_scope", key="demo_key", string_value="not-a-real-secret")

value = dbutils.secrets.get(scope="course_scope", key="demo_key")
print(value)                  # [REDACTED]
print(len(value))             # 17: the value is usable, just hidden in output

• Only the literal value is redacted: if you transform it (value[::-1]), it prints in clear
• Scope names are case-insensitive. Create scopes per application or role, not per person
• Anyone with READ can use the value in code. Workspace admins and the scope creator can read it too
• Keep secrets out of widgets, parameters, logs, and tables
• For cloud access prefer Unity Catalog credentials over keys in a secret

• Serverless and classic use different mechanisms: classic is in your VPC, serverless is governed by network policies
• Private connectivity to your own databases and APIs from serverless uses private endpoint rules
• Networking features are admin, account-level settings
• Free Edition already restricts outbound traffic to trusted domains (Module 2): a small taste of egress control

• Typical: dev, test, prod catalogs. Prod is bound to the prod workspace, and only service principals can write
• One metastore per region. Sharing across regions uses sharing, not a shared metastore
• Don't reuse DBFS buckets as metastore storage

• Customer-managed keys let you control and revoke the key: an account-level setting
• Encryption doesn't replace access control: authorised users read plain data
• Column-level protection (masking, hashing) covers what disk encryption can't (topic 8)
• Compliance profiles (for example for regulated industries) are workspace-level choices made at creation

1. A view that exposes only some columns:

%sql
CREATE OR REPLACE VIEW customers_public AS
SELECT customer_id, country, signup_date FROM customers_sec;

SELECT * FROM customers_public LIMIT 3

2. A row filter (recap of Module 6.5):

%sql
CREATE OR REPLACE FUNCTION nl_only(country STRING) RETURNS BOOLEAN
RETURN is_account_group_member('admins') OR country = 'NL';

ALTER TABLE customers_sec SET ROW FILTER nl_only ON (country)

%sql
ALTER TABLE customers_sec DROP ROW FILTER

• ABAC (attribute-based access control) attaches policies to a catalog or schema and applies them to every object carrying a governed tag, such as pii. One policy covers many tables
• Filters and masks live with the table: every tool sees the same result
• Test as a different user or group, not only as yourself

%sql
CREATE OR REPLACE VIEW customers_masked_view AS
SELECT customer_id,
       CASE WHEN is_account_group_member('pii_readers') THEN name
            ELSE concat(left(name, 1), '*****') END AS name,
       sha2(cast(customer_id AS STRING), 256)        AS customer_hash,
       country
FROM customers_sec;

SELECT * FROM customers_masked_view LIMIT 3

• Classify first: tag columns that are personal or sensitive (ALTER TABLE ... ALTER COLUMN ... SET TAGS ('pii' = 'true'))
• Prefer column masks (Module 6.4) over many one-off views: the rule lives with the column
• Hash with a secret salt so values can't be looked up from a table of guesses
• Don't copy raw personal data into dev: use masked or synthetic data (Module 14.4)
• Mask in the serving layer, keep raw data in restricted bronze/silver

%sql
-- 1. delete the person's rows
DELETE FROM customers_sec WHERE customer_id IN (1, 2)

%sql
-- 2. physically remove the data files that held them (needed when deletion vectors are on)
REORG TABLE customers_sec APPLY (PURGE)

%sql
-- 3. remove old files that are no longer referenced (default retention: 7 days)
VACUUM customers_sec DRY RUN

• Delete in bronze first, then propagate to silver and gold. Rebuild or refresh materialized views
• Streaming tables from Delta sources: add skipChangeCommits to the streaming read, or use the change data feed
• Keep a control table of requests and what was done: auditable
• Full deletion is safer than pseudonymising: reidentification risk remains otherwise
• Predictive optimization on managed tables runs VACUUM for you

• Delta Sharing / OpenSharing: you create a share (tables, views, filtered data, notebooks, models), a recipient, and grant access
• Recipients read live data without you copying it
• Lineage and access control don't cross metastore boundaries: recipients apply their own
• Use a view or filter to share only the right rows and columns
• Rule of thumb: inside your metastore use grants. To another region, cloud or company use sharing
• You can revoke a recipient's access at any time

• Name owners for each catalog and schema (a group), and a data steward per domain
• Change governance through code (Terraform, bundles), reviewed like any change
• Review access regularly: remove what isn't used

%sql
-- who can do what on a table
SHOW GRANTS ON TABLE customers_sec

%sql
-- every change to a table: who, when, which operation
DESCRIBE HISTORY customers_sec

%sql
-- account-wide audit events (system tables: account-level, may not exist on Free Edition)
SELECT event_time, user_identity.email, service_name, action_name
FROM system.access.audit
WHERE event_date >= current_date() - INTERVAL 7 DAYS
ORDER BY event_time DESC LIMIT 20

• Filter audit queries on event_date
• Alert on sensitive actions: new grants to broad groups, changes to row filters and masks, token creation
• Keep audit data longer than operational data if compliance needs it
• Review should be routine, not only after an incident