Chapter 16 — Access control in practice
Lesson map
Lesson 16 published. Map, example, EX046.
flowchart TD
N0["Learning outcomes"]
N1["16.1 Roles and permissions"]
N2["16.2 Access reviews"]
N3["16.3 Logging"]
N4["16.4 Least privilege"]
N0 --> N1 --> N2 --> N3 --> N4
N4 --> EX["Worked example"]
EX --> Q["Exercise EX046"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["16.1 Roles and permissions"]
N2["16.2 Access reviews"]
N3["16.3 Logging"]
N4["16.4 Least privilege"]
N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX046"]
[Learning outcomes]
|
v
[16.1 Roles and permissions]
|
v
[16.2 Access reviews]
|
v
[16.3 Logging]
|
v
[16.4 Least privilege]
|
v
[Worked example] --> [Exercise EX046]
Learning outcomes
You will (1) implement role-based access for data; (2) run access reviews; (3) log access to sensitive data.
16.1 Roles and permissions
Ravi helps implement role-based access in Calder Valley's reporting database. Roles include repairs analyst, call handler, finance officer and data team. Each role has permissions to specific tables or views. Repairs analysts see jobs and properties but tenant names only through a view that masks them. The tenancy owner approves which roles see tenant details. Ravi documents each role in the dictionary and creates views rather than granting access to raw tables. The views are tested by each role before access is switched on.
16.2 Access reviews
Every quarter, Ravi produces a list of accounts with their roles and last login for each owner to review. Owners confirm, change or remove access. In the first review, the tenancy owner removes access for staff who moved departments and an external consultant whose contract ended. Ravi records the review and changes. He notes that reviews are only effective if owners actually examine lists rather than approving them automatically, so he highlights unusual entries. The tenancy owner thanks Ravi for the highlights.
16.3 Logging
For tables with tenant details, Ravi turns on logging of queries, recording who accessed what and when. Logs are kept for a year and reviewed monthly for unusual patterns, such as large exports or access outside working hours. He writes a simple script that summarises logs for the data protection officer. He explains that logs deter misuse and allow investigation, and that reviewing them regularly is what turns logging into a control. The first monthly summary shows no unusual patterns, which reassures the officer.
16.4 Least privilege
Ravi learns the principle of least privilege: people should have the minimum access needed for their work. A finance officer asks for full access to the jobs table to reconcile invoices. Ravi checks with the repairs owner, who approves a view containing job identifiers, contractors and costs without tenant details. The finance officer can do the reconciliation. Ms. Moreau notes that tailored views often satisfy requests without granting broad access. Ravi adds the finance view to the dictionary, so that similar requests can be handled quickly in future.
Worked example
Ravi implements roles with masked views, runs quarterly owner reviews that remove stale access, logs and summarises access to tenant data and satisfies a finance request with a tailored view.
Synthetic case. Roles are synthetic.
Errors and limits
Owners approving lists without examining them makes reviews meaningless.
Exercises
- EX046 — Masked access. How do repairs analysts see tenant names? Rubric: 3 = through a masking view;; 2 = partly correct;; 1 = vague;; 0 = none.
- EX047 — Log patterns. What unusual patterns are reviewed? Rubric: 3 = large exports or out-of-hours access;; 2 = partly correct;; 1 = partial;; 0 = none.
- EX048 — Finance view. What did the finance view contain? Rubric: 3 = job identifiers, contractors and costs;; 2 = partly correct;; 1 = partial;; 0 = none.
Continue studying — lesson 16 (chapter 16 of 30)
← Chapter 15 · Course chapter index · Chapter 17 →
Continue to the next authored lesson.