Authorization in four layers

Authentication, in Part 5, decided who someone is. Authorization decides what they may do: open the organizer dashboard, approve a club, change someone's role. Turnout checks it in four layers, and every layer exists because the others can be bypassed.

The golden rule: hiding is courtesy, refusing is security. Layers 1 and 2 make the app pleasant. Layers 3 and 4 make it safe. If you only build one, build layer 4, because it protects the data however it is reached.

Before you start

You need Turnout as it was at the end of Part 5, with email-code sign-in working. If you don't have it, import the Start here app, turnout-app-part-5.zip, from the downloads at the end of this page. If you import it into a schema that already has your tables and cancel at the supporting-objects step, also run part5_login_codes.sql, to_err.sql and to_auth.sql from Part 5, in that order: sign-in needs them.

A note on order. This part sets up the rules for organizer pages, but you build those pages in Part 7. So here you create the schemes, the Admin role and the first admin page. In Part 7, each organizer page gets its scheme the moment you create it.

Lesson 6.1 The four layers

Every request passes the same four checks in order. Each one that says no stops it there, with a message written for the person.

A crafted request can skip layers 1 and 2: it doesn't need your page or your button. It can't skip layers 3 and 4, because they run in the database, whatever sent the request.

Lesson 6.2 Authorization schemes, one per zone

An authorization scheme is a named yes-or-no question APEX asks before showing a page, region, button or menu entry. Turnout has one per zone of the page map (Lesson 1.4, drawn in full in Lesson 7.0):

authorization is-organizer (
    name: Is Organizer
    type: plSqlFunctionBody
    settings {
        plsqlFunctionBody: return to_security.is_any_organizer;
    }
    error {
        errorMessage: Only club organizers can open this page.
    }
    advanced {
        evaluationType: perPageView
    }
)

Then each organizer page names it: Page › Security › Authorization Scheme: Is Organizer. You'll do that for pages 100 to 140 as you build them in Part 7.

Build it.

1. The scheme calls a new function, to_security.is_any_organizer. Upload and run the updated to_security.sql in SQL Workshop › SQL Scripts. It replaces your Part 3 version.
2. In App Builder: Shared Components › Authorization Schemes › Create, From Scratch, Next.
3. Name Is Organizer, Scheme Type PL/SQL Function Returning Boolean, PL/SQL Function Body return to_security.is_any_organizer;, Error Message Only club organizers can open this page., Validate authorization scheme Once per page view. Create.
4. Create a second one the same way: Name Is Site Admin, function body return to_security.is_site_admin;, error message Only site administrators can open this page., validate Once per session.

Check: the Authorization Schemes list shows both, with Is Organizer evaluated per page view and Is Site Admin per session.

Why "any organizer" and not "organizer of this club"

A scheme takes no parameters. It can't easily know which club page 100 is about, because APEX evaluates it before the page's processes run. So the scheme answers the zone question, "is this person an organizer anywhere?", and the page's load process answers the club question with to_security.require_club_role(:P100_CLUB_ID, to_security.c_organizer). Two checks, each answering what it can answer well.

When the answer is worked out

Never cache a scheme for longer than the answer stays true.

Lesson 6.3 Component conditions: what each person sees

Layer 2 shapes each page to the person looking at it. Two tools do it:

• An authorization scheme on a component, when the question is the same as a zone's. The Admin menu entry uses Is Site Admin, so only site admins see it.
• A server-side condition, when the question depends on this page's data. The Organize button on page 10 asks about this club:

to_security.has_club_role_yn(:P10_CLUB_ID, to_security.c_organizer) = 'Y'

Use the _yn function in conditions. has_club_role returns a PL/SQL BOOLEAN. It works in a PL/SQL expression, but the _yn version also works in SQL, so one function serves conditions, report columns and queries alike.

Lesson 6.4 Data rules: filter by person and club

Layer 3 decides which rows a page may read. Two habits cover every page in Turnout:

1. Personal pages filter by the person. My events, My ticket, My clubs and Profile all have where ... user_id = to_security.current_user_id. There is no item to tamper with, so there is nothing to protect.
2. Club pages check the club when they load. Pages 100 to 140 start with to_security.require_club_role for the club in their URL. Priya opening P100_CLUB_ID for Bay Chess Club, where she is only a member, gets "Only club organizers can do this."

The checksum on every URL (Arguments Must Have Checksum) is a third, cheaper fence: it stops editing ids by hand. But a link that was valid yesterday is still valid after someone is demoted, so the load check stays.

APEX 26.2 also adds Deep Data Security, which moves rules like these into the database so every query is filtered automatically. Lesson 13.3 compares it with this approach.

Lesson 6.5 Site admins and the Club approvals page

Club roles live in Turnout's own tables. Site-wide roles, like "runs Turnout itself", use APEX's built-in Access Control instead: it already has screens for assigning roles, and apex_acl to check them from PL/SQL.

1. The app defines one role, Administrator (shared-components/acl-roles.apx).
2. Assign it: Shared Components › Application Access Control › Add User Role Assignment. User name: priya.raman@example.com. Role: Administrator.
3. to_security.is_site_admin asks apex_acl.has_user_role(p_user_name => APP_USER, p_role_static_id => 'ADMINISTRATOR').

Because Part 5 made APP_USER the person's email whatever way they signed in, one assignment covers email codes and Google alike.

Build it.

1. Shared Components › Application Access Control. Under Roles, click Add Role: Name Administrator, Static ID ADMINISTRATOR. Create.
2. Under User Role Assignments, click Add User Role Assignment. User Name priya.raman@example.com, check Administrator. Create. (APEX may show the name in capitals; that's fine.)

Page 200: Club approvals

New clubs start PENDING. Page 200 lists them with who asked, and has Approve and Turn down buttons that call to_club_api.approve_club and suspend_club. Both procedures begin with to_security.require_site_admin, so even a hand-crafted request from someone else is refused. Approving also queues a "your club is live" email to the owner through the outbox (Part 10 sends it).

The page carries Is Site Admin, and so does the Admin menu entry: the entry is hidden from everyone who couldn't open the page anyway.

Build it.

1. Create Page › Blank Page, number 200, name Club Approvals. Under Navigation, switch Use Navigation on and create a New Navigation Menu Entry called Admin. Create.
2. Page properties: Alias CLUB-APPROVALS, Title Club approvals, Security › Authorization Scheme Is Site Admin, Page Access Protection Arguments Must Have Checksum.
3. Right-click Body, Create Region, and set Layout › Slot to Breadcrumb Bar (a new page only lists the slots it uses). Make it Static Content with template Hero: Title Club approvals, Source › HTML Code New clubs wait here until a site admin approves them. Approved public clubs appear on Clubs straight away.
4. In Body, a Classic Report region, Name and Title Waiting for approval, with this query. Under Attributes › Messages, set When No Data Found to Nothing waiting.

select c.name,
       cat.name as category,
       c.city,
       u.display_name || ' (' || u.email || ')' as owner,
       c.description,
       c.created_at
  from to_clubs c
  join to_categories cat on cat.id = c.category_id
  left join to_club_members m on m.club_id = c.id and m.role = 'OWNER'
  left join to_users u        on u.id = m.user_id
 where c.status = 'PENDING'
 order by c.created_at

5. Below it, a Static Content region Decide. In it, a Select List item P200_CLUB_ID, Label Club, Value Required on, template Required - Above, with List of Values type SQL Query, Display Null Value on, Null Display Value Choose a club:

select name || ', ' || city as d, id as r
  from to_clubs
 where status = 'PENDING'
 order by created_at

6. Two buttons in the Decide region, position Edit: APPROVE, label Approve, Hot. And SUSPEND, label Turn down, with Requires Confirmation on and the message Turn this club down? It stays hidden and its owner can't add events.
7. Two processes. Approve club, When Button Pressed APPROVE, Success Message Approved. The club is live.:

to_club_api.approve_club(:P200_CLUB_ID);

Turn down club, When Button Pressed SUSPEND, Success Message Turned down.:

to_club_api.suspend_club(:P200_CLUB_ID);

8. A branch After Processing to page 200, Clear Cache 200, Include Process Success Message on.
9. Shared Components › Navigation Menu, open the Admin entry, and set Authorization Scheme to Is Site Admin.

Check: sign in as Priya. Admin is in the menu, and the page lists Wesley Chapel Readers. Sign out and sign in as Grace: no Admin entry. Type the page's address by hand and you get "Only site administrators can open this page."

Lesson 6.6 What people see when they're refused

A refusal should say what happened and what to do, never show an ORA- number.

Every message names the rule, not the person: "only the owner can", not "you can't". It reads as information, and it tells them who to ask.

Lesson 6.7 Testing permissions as three people

Permissions are easy to get wrong in the direction nobody notices: someone can do too much. Test each layer as someone who should be refused.

The API tests from Part 3 already cover layer 4 without a browser: turnout_api_tests.sql proves Priya can't cancel a Bay Chess Club event.

Checkpoint

Every page in Turnout now belongs to a zone with a scheme, every button that matters has a condition, every page reads only the rows it may, and every change goes through an API that checks again.

Exercises

• Add page 210, Categories, for site admins: an Interactive Grid on TO_CATEGORIES with Is Site Admin.
• Give the Members page a second scheme, Is Owner, and use it to hide the Role column from organizers who aren't owners.
• Write a test in turnout_api_tests.sql proving an organizer can't approve a club.
• Stretch: log every refusal from to_security to a table, and show the last 50 on an admin page.