Give the West manager a view, not the table

short · 35 min · Objective 5.3

Task

Enforce row-level and column-level security in SQLite with a view: the West manager's view shows only West orders and no personal columns.

Steps

  1. Copy data/shop.db to lab/access/shop.db and work on the copy.
  2. Create a view v_west with the columns order_id, order_date, customer_id, city, region and quantity, for orders with quantity above zero placed by customers in the West region.
  3. Check that email, first_name and last_name are not among the view's columns.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

python3 -c "import sqlite3; c = sqlite3.connect('lab/access/shop.db'); print('rows:', c.execute('SELECT COUNT(*) FROM v_west').fetchone()[0]); print('columns:', ','.join(r[1] for r in c.execute('PRAGMA table_info(v_west)'))); print('regions:', c.execute('SELECT COUNT(DISTINCT region) FROM v_west').fetchone()[0])"

460 rows, the six named columns and nothing personal, and 1 region. In a server database the manager would be granted SELECT on the view and nothing on the tables; SQLite has no users, so here the view shows the shape of the control and the file's own permissions are the only access control there is.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.