Why this matters
As a BI Developer, you implement row-level and column-level security so people only see what they are allowed to see. Audits and access reviews prove that controls work and that access is still justified. They reduce data leaks, support compliance, and build trust in dashboards and metrics.
- Real task: Prepare evidence that only regional managers can see their region's sales rows.
- Real task: Review HR access monthly and revoke temporary access automatically when it expires.
- Real task: Investigate who queried sensitive columns (e.g., salary) last week and why.
Who this is for
BI Developers and Analytics Engineers who implement or maintain dataset permissions, RLS/CLS, and need to run or support periodic access reviews.
Prerequisites
- Basic SQL familiarity (SELECT, WHERE, JOIN).
- Understanding of roles, groups, or permissions in your BI platform.
- Familiarity with the datasets you secure (tables, columns, sensitivity).
Concept explained simply
Audit is the evidence you keep that shows who had access, why they had it, and how you verified it. Access review is the periodic activity where a data owner confirms that current access is still needed and correct.
Mental model
Think of your data as a venue with rooms (tables) and drawers (columns). RLS is the bouncer who checks which room you can enter. CLS is the lock on certain drawers. The audit is the notebook that records who asked to get in, who approved, when they left, and any incidents.
Core steps of an access review
- Inventory: List datasets, sensitive columns, roles/groups, and current users.
- Evidence of need: For each user/role, note the business purpose and data owner.
- Validate controls: Test RLS filters and CLS masks still work as intended.
- Owner attestation: Data owner confirms access is correct or requests changes.
- Remediate: Revoke outdated access, fix filters, set/renew expirations.
- Log and retain: Save what changed, who approved, timestamps, and test screenshots or queries.
What to include in a simple access inventory
- Dataset/Table
- Sensitive Columns
- Roles/Groups
- Users in each Role
- Business Purpose
- Expiration Date (if any)
- Data Owner
Reviewer checklist
- Every user has a current business purpose documented
- All temporary accesses have an expiration date
- RLS rules were tested with at least two sample users
- Sensitive columns are hidden or masked for non-privileged roles
- All changes and test results are logged with timestamps
Worked examples
Example 1: Department-based RLS
Goal: Sales Analysts should only see rows for their department.
Mapping table (user_department): user_email | department -------------------+----------- sam@company.com | West lee@company.com | East RLS filter idea: orders.department = (SELECT d.department FROM user_department d WHERE d.user_email = CURRENT_USER)
Why this works
The filter ties the current user to a single department and only returns matching rows. Test with multiple users to ensure no cross-department leakage.
Example 2: Column-level restriction for Salary
Goal: Only HR role can see the salary column; others see NULL.
Pseudo-view logic: SELECT employee_id, CASE WHEN CURRENT_ROLE = 'HR' THEN salary ELSE NULL END AS salary FROM hr_comp;
Why this works
Column masking ensures dashboards render without errors while protecting sensitive values for non-HR users.
Example 3: Temporary vendor access with audit
Goal: Grant a vendor 7-day read-only access to a non-sensitive dataset with auto-expiration and logging.
- Create a role VendorTemp with read-only permissions to specific tables only.
- Add the vendor user to VendorTemp and set an expiration date.
- Record request, approval, scope, and expiration in the audit log.
- On day 7, verify removal and log the result.
Why this works
Least privilege plus time-bound access reduces risk. The audit trail proves who approved, for how long, and that cleanup happened.
How to document and audit
Keep a simple, append-only audit log. It can be a secured table or file.
audit_log fields (example): - event_id (UUID) - timestamp_utc - actor (who did it) - subject (who/what access is about) - action (granted/revoked/tested/approved) - scope (dataset/table/column/role) - reason (business purpose/ticket number) - expires_at (nullable) - evidence_location (e.g., test note or screenshot path)
Simple retention approach
Retain audit logs for at least 12–24 months or per company policy. Ensure only admins can modify logs.
Exercises
Do these to cement the concepts. The Quick Test is available to everyone; only logged-in users get saved progress.
Exercise 1: Plan a quarterly access review
Create a short plan for the datasets below. Include roles, row filters, column rules, review owner, cadence, and expiration policy.
Datasets and roles to use
Datasets:
- sales_orders(order_id, region, amount, customer_id)
- hr_comp(employee_id, department, salary)
Roles:
- Analyst (needs sales by own region only; no salary)
- SalesManager (sees all regions in sales; no salary)
- HR (sees hr_comp including salary)
- VendorTemp (read-only sales for West; expires in 7 days)
Users:
- sam@company.com (Analyst, West)
- lee@company.com (Analyst, East)
- mia@company.com (SalesManager)
- ryan@company.com (HR)
- vendor@partner.com (VendorTemp)
- Wrote role-to-permission mapping
- Defined RLS rules and CLS masks
- Set owners, cadence, and expirations
Exercise 2: Trace an access request end-to-end
Fill in an access request template, decide, and log it.
Template and scenario
Template fields:
- requester
- dataset/scope
- reason/business purpose
- data owner approval (name/date)
- duration/expiration
- decision (approve/deny)
- reviewer name/date
- audit notes
Scenario:
Requester: alex@company.com
Scope: sales_orders, West region only
Reason: Build quarterly West sales dashboard
Owner: Sales Director (approves)
Duration: 30 days
- Completed the template
- Chose decision and constraints
- Wrote audit log entry
Common mistakes and how to self-check
- One-time setup, never reviewed: Schedule periodic reviews; use expirations for temporary access.
- Unclear business purpose: Require a concrete reason and data owner attestation.
- Unvalidated RLS: Test with at least two users per role; try to access out-of-scope rows.
- Ignoring column sensitivity: Mask or hide sensitive fields for non-privileged roles.
- No audit trail: Log requests, approvals, changes, and test evidence with timestamps.
Quick self-check
Pick one dataset. Can you name the owner, list who has access, show why, prove RLS/CLS works, and show the last review date? If not, you have gaps.
Practical projects
- RLS Validation Pack: Build a small script or manual procedure that tests each role with expected vs. actual row counts for two regions.
- CLS Smoke Tests: Create two queries: one as HR (salary visible) and one as Analyst (salary null). Save outputs as evidence.
- Access Register: Maintain a simple access register (table or sheet) with owner, purpose, expiration, and next review date.
Learning path
- List sensitive datasets and columns; assign data owners.
- Implement or review RLS by business entity (e.g., region/department).
- Implement CLS for sensitive fields (e.g., salary, PII).
- Set up an access register and schedule quarterly reviews.
- Add expirations for temporary access; verify automatic revocation.
- Create a repeatable audit evidence pack (tests, logs, screenshots).
Next steps
- Finalize your access register and share it with data owners.
- Run one pilot access review and capture findings.
- Automate one part (e.g., user list export or test queries) and keep the rest manual for now.
Mini challenge
A new contractor needs access to sales for the North region only, for 14 days, to fix a dashboard. Draft the request details, apply RLS, hide sensitive columns, and write the audit log entry you would store. Keep it to 10 lines max.
Quick Test — Note
The quick test is available to everyone. Only logged-in users get saved progress.