SQL from zero · Chapter 31 of 37

Who can see what

By the end of this chapter you can decide which role may read or change which tables and columns, and hand out a safe view instead of a whole table. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Safe and correct
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Rohan wants his own login for the dashboards. "Read-only," says Anya, before he can finish. "And no emails. And definitely not the messages." Meera's member directory page needs names and cities, and nothing else about a member.

So far everyone connects as app, which owns every table and can do anything: read every email, delete every post. The only way to keep Rohan's dashboards away from the messages would be to ask him nicely, and one wrong query in a dashboard could change real data.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    analyst-read.sql: read-only access for analyst

    Let the analyst role (Rohan's dashboards) read five tables: posts, comments, likes, tags and post_tags.

    It gets nothing else: it cannot read any other table (users, messages, reports, follows), and it cannot insert, update, delete or truncate anywhere.

  2. Task 2

    hide-email.sql: five columns of users for analyst

    Let the analyst role read these five columns of users, and only these: id, username, display_name, city and created_at. It must see every member (all the rows), but not email or any other column, and it may not insert, update or delete.

  3. Task 3

    member-directory.sql: a safe directory for member

    Create a view named member_directory with the username, display_name and city of every member who is not deleted. Let the member role read the view, and only read it.

    member gets no access to the users table itself. The view's columns: username, display_name and city.

  4. Task 4 · interview question

    Interview: too-much.sql, cut back a hurried GRANT ALL

    Run setup.sql first: it gives moderator ALL privileges on posts and messages. Fix it so that moderator keeps exactly SELECT and UPDATE on posts (to read posts and hide them), loses every other privilege on posts, and has no privileges at all on messages.

What you will learn

  • GRANT and REVOKE: hand a role a privilege on a table, and take it back.
  • Column privileges: let a role read some columns of a table and not others.
  • Views as a safe window: a role reads the view without any privilege on the table underneath.

More in Safe and correct

Build "Who can see what" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.