1. Fundamentals & System Overview

┌────────────────────────────────────────────────────────┐
│                   Client Browser                       │
└───────────────────────────┬────────────────────────────┘
                            │ HTTP Requests / REST
                            ▼
┌────────────────────────────────────────────────────────┐
│           Flask Presentation Controllers               │
│  - Routes (exam_bp, auth_bp)                           │
│  - Declarative Guard: @has_permission("exam.create")   │
└───────────────────────────┬────────────────────────────┘
                            │ Domain Calls
                            ▼
┌────────────────────────────────────────────────────────┐
│                   RBAC Service Layer                   │
│  - Authorization Business Logic                        │
│  - Ownership verification (RBAC + Scoping)             │
└───────────────────────────┬────────────────────────────┘
                            │ Abstract Query Methods
                            ▼
┌────────────────────────────────────────────────────────┐
│               RBAC Repository Layer                    │
│  - Raw SQL parameterized execution                     │
│  - Entity hydration: Rows -> Domain Objects            │
└───────────────────────────┬────────────────────────────┘
                            │ Parameterized SQL
                            ▼
┌────────────────────────────────────────────────────────┐
│               Relational Database (MySQL)              │
│  users | roles | permissions | user_roles | ...        │
└────────────────────────────────────────────────────────┘

┌──────────┐
│   User   │
└────┬─────┘
     │
     │ assigned
     ▼
┌──────────┐
│   Role   │
└────┬─────┘
     │
     │ contains
     ▼
┌──────────────┐
│ Permissions  │
└──────┬───────┘
       │
       │ grants access to
       ▼
┌─────────────────────┐
│  Resource / Action  │
└─────────────────────┘

User
 │
 │ assigned Role
 ▼
Role
 │
 │ Role contains Permissions
 ▼
Permissions
 │
 │ grants access to
 ▼
Resource / Action

┌──────────────────┐
│     Student      │
└────────┬─────────┘
         │
         │ assigned
         ▼
┌──────────────────┐
│   Student Role   │
└────────┬─────────┘
         │
         │ contains
         ▼
┌──────────────────────┐
│     Permissions      │
├──────────────────────┤
│ • exam.view          │
│ • exam.take          │
│ • submission.create  │
│ • result.view_private│
└──────────────────────┘

Why Not Store Permissions Directly on the User?

A naive alternative design would store permissions directly on the users table — for example, as a boolean column per capability, or as a serialized field such as users.permissions. This proposal rejects that design for four reasons that motivate the schema developed in Section 2:

•             Combinatorial growth. With N users and M permissions, a direct user–permission mapping can require up to N × M individual grants to be created, audited, and revoked. Role-based indirection reduces this to N role assignments plus a small, stable set of role–permission grants, because the number of roles R satisfies R ≪ N in a typical institution.

•             Inconsistent authorization. When permissions are attached per user, two users with the same job function (for example, two teachers) can silently drift out of sync as ad-hoc grants accumulate, producing unpredictable and difficult-to-audit access.

•             Costly privilege changes. Changing what a “Teacher” is permitted to do — for example, adding question.manage to the Teacher role — would require updating every teacher’s row individually rather than updating a single row in role_permissions.

•             Weak auditability. A flattened permissions column (for example, a comma-separated string) cannot be indexed, joined, or constrained by the relational database, undermining referential integrity and making automated security review far harder.

RBAC resolves all four concerns by interposing the Role entity between User and Permission, and by representing both relationships — User↔Role and Role↔Permission — as normalized, queryable, many-to-many associations. This is the subject of Sections 2 and 3.

2. Database Design — Primary Section

The PostgreSQL database design is the most detailed technical section of this proposal, since it is the proposal’s central contribution. The design is organized around five core relational tables:

users
roles
permissions
user_roles
role_permissions

The first three are entity tables; the last two are junction (associative) tables that resolve the two many-to-many relationships in the model.

2.1 Users Table

users
---------
id
email
password_hash
created_at
updated_at

Design rationale:

•             Primary key (id). A surrogate BIGSERIAL (or UUID) primary key is proposed rather than using email as the primary key, so that the identity used internally for foreign keys is immutable even if a user’s email address changes.

•             Unique email. A UNIQUE constraint on email enforces that authentication credentials map to exactly one account, which is a precondition for reliable login and for auditing “who did what.”

•             Password hash storage. The column is named password_hash, not password, and is designed to store only the output of a one-way adaptive hash function (Bcrypt, per the technology stack). Plaintext passwords are never persisted, transmitted in logs, or cached, because a database compromise or accidental log exposure would otherwise directly disclose user credentials. Bcrypt’s built-in salting also defends against precomputed rainbow-table attacks and, through its configurable work factor, against brute-force search.

•             Relationship with user_roles. The users table intentionally has no role_id or permission column. Authorization data lives exclusively in user_roles and role_permissions, keeping identity data (who the user is) fully separated from authorization data (what the user may do).

2.2 Roles Table

roles

Design Rationale

Role identity.
id is the surrogate key referenced by both user_roles and role_permissions.

Unique role names.
A UNIQUE constraint on name prevents duplicate roles (for example, two different rows both named Teacher) that would otherwise fragment permission assignments across what should be a single logical role.

Role responsibility.
description documents the intended scope of the role in natural language, supporting administrative review without requiring the reviewer to inspect role_permissions directly.

Relationship with users and permissions.
roles sits at the center of the model: it is referenced by user_roles.role_id (linking to users) and by role_permissions.role_id (linking to permissions).

Representative Roles

Representative roles for the examination-system context:

┌─────────────────────┐
│        Admin        │
├─────────────────────┤
│       Teacher       │
├─────────────────────┤
│       Student       │
└─────────────────────┘

2.3 Permissions Table

user.create
user.delete

subject.manage

exam.create
exam.edit
exam.view
exam.take

question.manage

result.view_all
result.view_private

submission.create

system.view_stats

3. Many-to-Many RBAC Relationships

The RBAC model contains exactly two many-to-many relationships, each resolved by its own junction table. This section gives both the relational justification and the schema for each.

3.1 user_roles

user_roles
---------
user_id
role_id

Cardinality.

One User -> Many Roles
One Role -> Many Users

Therefore Users <-> Roles is many-to-many. A single instructor, for example, might simultaneously hold both the Teacher role and an Admin role for departmental administration — a case that a single role_id column on users could not represent without either duplicating the user row or overloading the column’s meaning.

Why a junction table is required instead of users.role_id. A single foreign-key column on users can express only a one role per user (a one-to-many relationship from roles to users), which is materially less flexible than the requirement. It also could not be extended later — for example, to record when a role was granted or by whom — without a schema migration that effectively reconstructs a junction table anyway. Introducing user_roles from the outset keeps the schema normalized and extensible: the composite primary key (user_id, role_id) guarantees that a given role cannot be assigned twice to the same user, while still allowing arbitrarily many role assignments across the system.

3.2 role_permissions

role_permissions
---------
role_id
permission_id

Cardinality.

One Role -> Many Permissions
One Permission -> Many Roles

Therefore Roles <-> Permissions is also many-to-many: exam.view, for instance, is plausibly granted to both Teacher and Student, while Teacher is plausibly granted many permissions beyond exam.view.

Why this avoids duplicating permission data. Without role_permissions, each role would need its own copy of every permission it holds — for example, as a repeated text field per role — which is precisely the anti-pattern rejected in Section 1.1, only shifted from the users table to the roles table. By instead storing a single authoritative row per permission in permissions, and connecting it to roles through role_permissions, the schema guarantees that updating a permission’s description or metadata never requires touching more than one row, and that a permission’s identity (id, code) is unambiguous everywhere it is referenced.

4. Complete RBAC Entity-Relationship Diagram

The five tables and their relationships are summarized in the entity-relationship diagram below.

Create a clear ASCII ERD:

┌───────────────┐
│     USERS     │
├───────────────┤
│ PK id         │
│    email      │
│    password   │
└───────┬───────┘
        │
        │ 1:M
        ▼
┌───────────────┐
│   USER_ROLES  │
├───────────────┤
│ FK user_id    │
│ FK role_id    │
└───────┬───────┘
        │
        │ M:1
        ▼
┌───────────────┐
│     ROLES     │
├───────────────┤
│ PK id         │
│    name       │
│    description│
└───────┬───────┘
        │
        │ 1:M
        ▼
┌──────────────────┐
│ ROLE_PERMISSIONS │
├──────────────────┤
│ FK role_id       │
│ FK permission_id │
└────────┬─────────┘
         │
         │ M:1
         ▼
┌────────────────┐
│  PERMISSIONS   │
├────────────────┤
│ PK id          │
│    code        │
│    description │
└────────────────┘

Reading the diagram.

•             USERS → USER_ROLES (1:M). Each user row in users can correspond to many rows in user_roles, one per role held; user_roles.user_id is a foreign key referencing users.id.

•             USER_ROLES → ROLES (M:1). Many rows in user_roles can reference the same role; user_roles.role_id is a foreign key referencing roles.id. Together, the two edges around USER_ROLES implement the Users <-> Roles many-to-many relationship described in Section 3.1.

•             ROLES → ROLE_PERMISSIONS (1:M). Each role can correspond to many rows in role_permissions, one per permission granted; role_permissions.role_id is a foreign key referencing roles.id.

•             ROLE_PERMISSIONS → PERMISSIONS (M:1). Many rows in role_permissions can reference the same permission; role_permissions.permission_id is a foreign key referencing permissions.id. Together, these two edges implement the Roles <-> Permissions many-to-many relationship described in Section 3.2.

The complete authorization path from a user to a specific permission therefore always traverses four edges: users → user_roles → roles → role_permissions → permissions. This traversal is the query pattern developed formally in Section 8.

5. Database Normalization

The RBAC schema is designed to satisfy at least Third Normal Form (3NF): every non-key attribute depends on the whole primary key and nothing but the key, and there is no transitive dependency between non-key attributes.

Elimination of duplicated role data. A role’s name and description exist in exactly one row of roles, regardless of how many users hold that role. No table other than roles stores role metadata.

Elimination of duplicated permission data. A permission’s code and description exist in exactly one row of permissions, regardless of how many roles grant it. No table other than permissions stores permission metadata.

Junction tables for many-to-many relationships. user_roles and role_permissions exist solely to resolve the two many-to-many relationships identified in Section 3, and each contains only foreign keys (plus, optionally, audit columns such as granted_at). Neither junction table duplicates data owned by another table.

Primary keys. users, roles, and permissions each use a surrogate primary key (id). user_roles and role_permissions use composite primary keys — (user_id, role_id) and (role_id, permission_id) respectively — which simultaneously identify each row and enforce that a given pairing cannot be duplicated.

Foreign keys. user_roles.user_id and user_roles.role_id reference users.id and roles.id; role_permissions.role_id and role_permissions.permission_id reference roles.id and permissions.id. These constraints ensure that an authorization record can never point to a nonexistent user, role, or permission.

Unique constraints. users.email, roles.name, and permissions.code are each constrained UNIQUE, preventing the creation of ambiguous duplicate identities at the database layer, independent of any application-level validation.

Referential integrity. Foreign-key constraints, combined with an explicit ON DELETE policy (Section 6), guarantee that the database itself — not just the application — rejects or cascades changes that would otherwise leave orphaned authorization records (for example, a user_roles row pointing to a deleted role).

Why permissions must not be flattened onto users or roles. This proposal explicitly rejects designs such as:

users.permissions

or:

roles.permissions = "exam.create,exam.edit,..."

Both violate normalization: they store a set of values inside a single column, which prevents the database from indexing, validating, or joining on individual permissions, and forces the application layer to parse and interpret a string in order to answer a question the schema should answer directly. Instead, every permission is a normalized row in permissions, and every grant of a permission to a role is a normalized row in role_permissions — the design established in Sections 2 and 3.

6. PostgreSQL Implementation

The following PostgreSQL-specific DDL implements the five-table schema. All statements use PostgreSQL syntax (BIGSERIAL, TIMESTAMPTZ, CHECK constraints, and PostgreSQL-style ON DELETE clauses); no MySQL-specific syntax is used anywhere in this proposal.

-- Enable case-insensitive email comparisons (optional, PostgreSQL extension)
CREATE EXTENSION IF NOT EXISTS citext;


-- ============================================================
-- 1. USERS
-- ============================================================
CREATE TABLE users (
    id            BIGSERIAL PRIMARY KEY,
    email         CITEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT email_format_check
        CHECK (
            email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
        )
);


-- ============================================================
-- 2. ROLES
-- ============================================================
CREATE TABLE roles (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(64) NOT NULL UNIQUE,
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);


-- ============================================================
-- 3. PERMISSIONS
-- ============================================================
CREATE TABLE permissions (
    id          BIGSERIAL PRIMARY KEY,
    code        VARCHAR(128) NOT NULL UNIQUE,
    description TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT code_format_check
        CHECK (
            code ~ '^[a-z_]+\.[a-z_]+$'
        )  -- enforces "resource.action"
);


-- ============================================================
-- 4. USER_ROLES (Users <-> Roles, many-to-many)
-- ============================================================
CREATE TABLE user_roles (
    user_id    BIGINT NOT NULL
        REFERENCES users(id) ON DELETE CASCADE,

    role_id    BIGINT NOT NULL
        REFERENCES roles(id) ON DELETE CASCADE,

    granted_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    PRIMARY KEY (user_id, role_id)
);


-- ============================================================
-- 5. ROLE_PERMISSIONS (Roles <-> Permissions, many-to-many)
-- ============================================================
CREATE TABLE role_permissions (
    role_id       BIGINT NOT NULL
        REFERENCES roles(id) ON DELETE CASCADE,

    permission_id BIGINT NOT NULL
        REFERENCES permissions(id) ON DELETE CASCADE,

    granted_at    TIMESTAMPTZ NOT NULL DEFAULT now(),

    PRIMARY KEY (role_id, permission_id)
);


-- ============================================================
-- Helpful indexes for authorization lookups
-- ============================================================
CREATE INDEX idx_user_roles_role_id
    ON user_roles (role_id);

CREATE INDEX idx_role_permissions_perm_id
    ON role_permissions (permission_id);

CREATE INDEX idx_permissions_code
    ON permissions (code);

Design notes.

•             ON DELETE CASCADE on both junction tables ensures that deleting a user, role, or permission automatically removes only the associations that reference it, never silently leaving a user_roles or role_permissions row that points at nothing.

•             The CHECK constraint on permissions.code enforces the resource.action naming convention (Section 2.3) at the database level, not only in application code.

•             CITEXT (case-insensitive text) is proposed for email so that Student@Example.com and student@example.com are treated as the same account, avoiding a common source of duplicate-registration bugs.

7. RBAC Sample Data

The following seed data illustrates a representative Admin / Teacher / Student configuration. It is proposed data for the eventual implementation phase, not a report of data already loaded.

Roles

INSERT INTO roles (name, description) VALUES

    ('Admin', 'Full administrative control over accounts, subjects, and system configuration'),

    ('Teacher', 'Creates and manages exams, questions, and views results for their subjects'),

    ('Student', 'Takes exams and views their own results');

Permissions

INSERT INTO permissions (code, description) VALUES

    ('user.create',         'Create a new user account'),

    ('user.delete',         'Delete an existing user account'),

    ('subject.manage',      'Create, edit, or remove academic subjects'),

    ('exam.create',         'Create a new examination'),

    ('exam.edit',           'Edit an existing examination'),

    ('exam.view',           'View examination details'),

    ('exam.take',           'Attempt/take an examination'),

    ('question.manage',     'Create, edit, or remove exam questions'),

    ('result.view_all',     'View results for all students'),

    ('result.view_private', 'View only the current user''s own results'),

    ('submission.create',   'Submit answers for an examination'),

    ('system.view_stats',   'View system-wide usage statistics');

Admin: Administrative Permissions

INSERT INTO role_permissions (role_id, permission_id)

SELECT r.id, p.id
FROM roles r, permissions p

WHERE r.name = 'Admin'
  AND p.code IN (
      'user.create',
      'user.delete',
      'subject.manage',
      'system.view_stats'
  );

Teacher: Examination and Question Permissions

INSERT INTO role_permissions (role_id, permission_id)

SELECT r.id, p.id
FROM roles r, permissions p

WHERE r.name = 'Teacher'
  AND p.code IN (
      'exam.create',
      'exam.edit',
      'exam.view',
      'question.manage',
      'result.view_all'
  );

Student: Examination-Taking Permissions

INSERT INTO role_permissions (role_id, permission_id)

SELECT r.id, p.id
FROM roles r, permissions p

WHERE r.name = 'Student'
  AND p.code IN (
      'exam.view',
      'exam.take',
      'submission.create',
      'result.view_private'
  );

Example Users

Password_hash values are illustrative Bcrypt placeholders.

INSERT INTO users (email, password_hash) VALUES

    ('admin1@university.edu',   '$2b$12$examplehash...................'),

    ('teacher1@university.edu', '$2b$12$examplehash...................'),

    ('student1@university.edu', '$2b$12$examplehash...................');

Role Assignment

Admin

INSERT INTO user_roles (user_id, role_id)

SELECT u.id, r.id
FROM users u, roles r

WHERE u.email = 'admin1@university.edu'
  AND r.name = 'Admin';

Teacher

INSERT INTO user_roles (user_id, role_id)

SELECT u.id, r.id
FROM users u, roles r

WHERE u.email = 'teacher1@university.edu'
  AND r.name = 'Teacher';

Student

INSERT INTO user_roles (user_id, role_id)

SELECT u.id, r.id
FROM users u, roles r

WHERE u.email = 'student1@university.edu'
  AND r.name = 'Student';

Intended RBAC Mapping

This seed pattern demonstrates the intended mapping:

RBAC Relationship

Admin
  ↓
Administrative Permissions

Teacher
  ↓
Examination / Question Permissions

Student
  ↓
Examination-Taking Permissions

This provides a clear representation of how users → roles → permissions are mapped within the proposed RBAC database design.

8. RBAC Authorization Queries

This section specifies the three canonical authorization queries the RBAC repository layer (Section 11) will implement.

8.1 Find a User’s Roles

Traversal:
User → User_Roles → Roles

SELECT r.id, r.name, r.description
FROM roles r
JOIN user_roles ur ON ur.role_id = r.id
WHERE ur.user_id = %(user_id)s;

8.2 Find All Permissions of a User

Traversal:
User → User_Roles → Roles → Role_Permissions → Permissions

SELECT DISTINCT p.id, p.code, p.description
FROM permissions p
JOIN role_permissions rp ON rp.permission_id = p.id
JOIN user_roles ur       ON ur.role_id = rp.role_id
WHERE ur.user_id = %(user_id)s
ORDER BY p.code;

Why DISTINCT is required

DISTINCT is required because a user may hold multiple roles that both grant the same permission (for example, both Admin and Teacher might grant exam.view); without it, the same permission code could appear once per granting role.

8.3 Check Whether a User Has a Specific Permission

Example question:
does user X have exam.create?

SELECT EXISTS (
    SELECT 1
    FROM user_roles ur
    JOIN role_permissions rp ON rp.role_id = ur.role_id
    JOIN permissions p       ON p.id = rp.permission_id
    WHERE ur.user_id = %(user_id)s
      AND p.code = %(permission_code)s
) AS has_permission;

How it works

The query starts from the user’s role assignments (user_roles), joins to every permission granted to those roles (role_permissions → permissions), and filters to the single permission code in question.

Wrapping the correlated lookup in SELECT EXISTS (...) allows PostgreSQL to short-circuit as soon as a single matching row is found, and returns a single boolean rather than a result set — the shape the RBAC service (Section 11) needs for a fast has_permission() check.

Because both %(user_id)s and %(permission_code)s are bound parameters rather than interpolated strings, this query is also immune to SQL injection (Section 12).

9. Object-Oriented RBAC Design

The relational schema of Sections 2–8 is mirrored, at the application layer, by three Python domain classes: User, Role, and Permission. Their relationship reproduces the database’s structure directly:

Domain Relationship

User
 │
 └── Role[]
       │
       └── Permission[]

Python Domain Classes

from dataclasses import dataclass, field


@dataclass(frozen=True)
class Permission:
    id: int
    code: str          # e.g. "exam.create"
    description: str = ""

    def __post_init__(self):
        if "." not in self.code:
            raise ValueError(
                f"Permission code must follow 'resource.action': {self.code!r}"
            )


@dataclass(frozen=True)
class Role:
    id: int
    name: str
    description: str = ""
    _permissions: tuple[Permission, ...] = field(default_factory=tuple)

    @property
    def permissions(self) -> tuple[Permission, ...]:
        return self._permissions

    def has_permission(self, code: str) -> bool:
        return any(perm.code == code for perm in self._permissions)


class User:
    def __init__(self, id_: int, email: str, roles: list[Role] | None = None):
        self.id = id_
        self.email = email
        self._roles: list[Role] = roles or []

    @property
    def roles(self) -> tuple[Role, ...]:
        return tuple(self._roles)

    def add_role(self, role: Role) -> None:
        if role not in self._roles:
            self._roles.append(role)

    def has_permission(self, code: str) -> bool:
        return any(role.has_permission(code) for role in self._roles)

9.1 Composition Over Inheritance

A tempting but ultimately weaker alternative design would model each role as a subclass of User:

User
├── AdminUser
├── TeacherUser
└── StudentUser

This proposal deliberately avoids that design.

Subclassing ties a user’s class to a role that is, in reality, a piece of mutable, run-time data: a person can be promoted from Student to Teaching Assistant, or can simultaneously be both a Teacher and a departmental Admin.

Under a subclass-per-role design, such a change would require constructing an entirely new object of a different class and migrating all references to it — an operation with no natural counterpart in the relational schema, where changing a role assignment is simply an INSERT/DELETE on user_roles.

Composition-Based Design

By instead composing a single User class from a collection of Role objects — each of which is itself composed from a collection of Permission objects — role changes become ordinary mutations of the _roles list, exactly mirroring an INSERT or DELETE against user_roles:

User
├── Role: Admin
├── Role: Teacher
└── Role: Student

A user can therefore hold any combination of roles, and gain or lose a role at run time, without ever changing the user’s class.

This is the object-oriented counterpart of the relational many-to-many design established in Section 3, and it is why Section 10 treats composition as the organizing OOP principle of the whole proposal.

10. OOP Concepts Applied to RBAC

This section makes explicit how each core object-oriented concept required by the course is realized in the RBAC domain model.

10.1 Encapsulation

Both Role and User store their underlying collections in a private-by-convention attribute (_permissions, _roles) and expose them only through a read-only property. Calling code can read user.roles but cannot reassign it directly (user.roles = [...] raises, since no setter is defined); the only sanctioned way to change membership is through the explicit add_role() method, which keeps invariants (such as “no duplicate role”) enforced in one place rather than scattered across the codebase.

10.2 Composition

As developed in Section 9.1, User contains Role objects, and Role contains Permission objects. This “has-a” relationship, rather than an “is-a” (inheritance) relationship, is the structural backbone of the domain model and is what allows the object graph to be rebuilt directly from the result of the Section 8.2 query.

10.3 Abstraction

Flask route handlers are designed to interact with authorization purely through a single method call:

if not user.has_permission("exam.create"):
    abort(403)

The route has no knowledge of user_roles, role_permissions, joins, or SQL. All of that complexity is abstracted behind has_permission(), which is itself backed by the layered architecture of Section 11. This separation means the database schema in Sections 2–8 can evolve (for example, gaining a granted_at audit column) without requiring any change to route-level code.

10.4 Polymorphism

Authorization-related domain objects are designed to expose a consistent interface so calling code can treat them uniformly wherever this makes the RBAC service simpler. Both Role and User implement a has_permission(code: str) -> bool method with the same signature and the same meaning (“does this entity ultimately grant this permission?”), even though their internal implementations differ (Role checks its own Permission list directly; User delegates the check across all of its Role objects). Any future authorization-bearing type — for example, a proposed Group entity representing a cohort of students — could implement the same has_permission() interface and be checked by the RBAC service without the service needing to know which concrete type it is inspecting.

10.5 Dataclasses

Permission and Role are implemented as @dataclass(frozen=True), reflecting that once constructed from a database row, these objects represent a stable, immutable snapshot of authorization data for the duration of a request. frozen=True prevents accidental mutation of, for example, a Permission’s code after it has been loaded. __post_init__ is used on Permission to validate, at construction time, that every code follows the resource.action convention enforced at the database level by the CHECK constraint in Section 6 — giving the same invariant a second, application-layer guarantee.

11. RBAC Application Architecture

The application is organized into four layers, each with a single, well-defined responsibility. Authorization logic never appears outside of the RBAC-specific layers.

Application Flow

┌─────────────────────────┐
│      Flask Route        │
└────────────┬────────────┘
             │
             ▼
┌─────────────────────────┐
│     RBAC Decorator      │
│  has_permission(...)    │
└────────────┬────────────┘
             │
             ▼
┌─────────────────────────┐
│      RBAC Service       │
└────────────┬────────────┘
             │
             ▼
┌─────────────────────────┐
│    RBAC Repository      │
└────────────┬────────────┘
             │
             ▼
┌─────────────────────────┐
│       PostgreSQL        │
│                         │
│  users                  │
│  roles                  │
│  permissions            │
│  user_roles             │
│  role_permissions       │
└─────────────────────────┘

Layer Responsibilities

Flask Route. Handles HTTP concerns only — parsing the request, calling the appropriate service method, and formatting the response. As stated in Section 10.3, the route must not contain SQL queries of any kind; its only interaction with authorization is through the decorator described below.

RBAC Decorator. A thin function wrapper, @has_permission("exam.create"), applied directly to route functions. It resolves the current authenticated user, calls into the RBAC Service to evaluate the permission, and returns an HTTP 403 Forbidden response before the wrapped route function ever executes if the check fails. This keeps the “who is allowed to reach this route” decision declarative and visible at the route definition, rather than buried inside route logic.

RBAC Service. Contains the authorization business logic — for example, user_has_permission(user_id, code) — expressed in terms of the User, Role, and Permission domain objects from Section 9. The service does not construct SQL; it calls the repository and reasons about the returned domain objects.

RBAC Repository. The only layer permitted to communicate with PostgreSQL. It implements the three queries specified in Section 8 using parameterized queries exclusively (%(name)s-style placeholders, never string concatenation or f-string interpolation of user-supplied values), and maps raw rows into User / Role / Permission domain objects for the service layer.

PostgreSQL. The five-table schema of Section 6, which is the sole source of truth for all authorization decisions.

Authorization Request Flow

This layering means every request for “can this user do X?” flows through exactly one path:

Route
  ↓
Decorator
  ↓
Service
  ↓
Repository
  ↓
Database

which is what makes the security analysis in Section 12 tractable: there is only one code path to audit.

12. Security Analysis

This section analyzes how the RBAC database design defends against the threat categories most relevant to an authorization system. Throughout, the guiding distinction is:

Authentication answers “Who are you?” Authorization (RBAC) answers “What are you allowed to do?”

The proposed system assumes authentication (verifying identity via email / password_hash and, plausibly, a session or token) is handled upstream of — and separately from — the RBAC layer described in this proposal; RBAC governs only what an already-authenticated identity may subsequently do.

Unauthorized access. Because every protected route is guarded by the @has_permission(...) decorator (Section 11), no route can be reached by a user whose role set does not resolve to the required permission via the Section 8.3 query. Access is denied by default: a user with no matching role–permission chain is rejected, rather than the system needing to enumerate every case in which access should be denied.

Privilege escalation. Because permissions are never stored on the users row itself, there is no user-controlled field an attacker could tamper with (for example, via a mass-assignment vulnerability) to directly grant themselves a permission. Escalation would require an INSERT into user_roles or role_permissions, both of which are reachable only through the Admin-permission-gated management endpoints (user.create, subject.manage, etc.), not through any student- or teacher-facing route.

Insecure Direct Object Reference (IDOR). Permissions such as result.view_private are scoped, by convention and by service-layer logic, to the requesting user’s own records, while result.view_all is a distinct permission required to view other users’ results. This means that even a correctly authenticated Student cannot access another student’s submission by guessing or manipulating an identifier in the URL, because the service layer is designed to check both “does the user have result.view_private?” and “is the requested record the user’s own?” before returning data.

Permission duplication. The (role_id, permission_id) composite primary key on role_permissions (Section 6) makes it structurally impossible for the same permission to be granted twice to the same role; a duplicate INSERT is rejected by the database itself via a unique-constraint violation, not merely discouraged by application logic.

Incorrect role assignment. Similarly, the (user_id, role_id) composite primary key on user_roles prevents a role from being assigned to the same user more than once, and the ON DELETE CASCADE foreign keys ensure that deleting a role or user cannot leave a dangling, incorrect assignment behind.

SQL injection. All queries specified in Section 8, and all queries implemented by the RBAC Repository (Section 11), use parameterized placeholders bound by the PostgreSQL driver rather than string concatenation of request input into SQL text. This is a structural rather than a convention-based defense: because permission_code and user_id are always passed as bound parameters, no value derived from user input is ever interpreted as SQL syntax.

Direct URL manipulation. Because the @has_permission(...) decorator runs on every request to a protected route regardless of how the route was reached (typed URL, forged form, replayed request), simply knowing or guessing a route’s URL does not bypass authorization — the decorator re-evaluates the current user’s role/permission chain on every single request, rather than relying on the client to only expose links the user is allowed to click.

13. RBAC Use Case Examples

Each example is resolved through the same database traversal: User -> User_Roles -> Role -> Role_Permissions -> Permission.

Example 1 — Teacher creates an exam.

Teacher -> exam.create

Result: Allowed. The Section 7 seed data grants exam.create to the Teacher role; the Section 8.3 query returns true for any user whose user_roles includes Teacher.

Example 2 — Student attempts to create an exam.

Student -> exam.create

Result: Denied, with 403 Forbidden. The Student role’s granted permissions (exam.view, exam.take, submission.create, result.view_private) do not include exam.create; the Section 8.3 query returns false, and the RBAC Decorator (Section 11) short-circuits before the route body executes.

Example 3 — Student takes an exam.

Student -> exam.take

Result: Allowed. exam.take is explicitly granted to Student in the Section 7 seed data.

Example 4 — Teacher requests system-wide statistics.

Teacher -> system.view_stats

Result: Denied, unless system.view_stats is explicitly added to the Teacher role via a new role_permissions row. In the proposed seed data, system.view_stats is granted only to Admin; this illustrates that expanding a role’s capabilities is a single, auditable, additive database change, not a code change.

14. RBAC Testing Plan

Testing focuses specifically on the RBAC subsystem — role assignment, permission assignment, permission resolution, and both positive and negative authorization outcomes — using Python’s built-in unittest framework against a dedicated test database.

Note: No test results are reported here, since this is a proposal for work not yet performed; the plan below specifies the intended cases.

Test Cases

import unittest


class RBACTestCase(unittest.TestCase):

    def test_assign_role_to_user(self):
        """A role can be assigned to a user via user_roles."""

    def test_assign_permission_to_role(self):
        """A permission can be assigned to a role via role_permissions."""

    def test_lookup_permissions_for_user(self):
        """Section 8.2 query returns the correct permission set for a user."""

    def test_valid_authorization_allowed(self):
        """A user with the required permission passes has_permission()."""

    def test_invalid_authorization_denied(self):
        """A user without the required permission fails has_permission()."""

    def test_user_with_multiple_roles(self):
        """Permissions from all of a user's roles are combined (union)."""

    def test_role_with_multiple_permissions(self):
        """A role correctly reports has_permission() for each of its grants."""

    def test_duplicate_role_assignment_rejected(self):
        """Re-inserting an existing (user_id, role_id) pair raises an
        integrity error due to the composite primary key."""

    def test_duplicate_permission_assignment_rejected(self):
        """Re-inserting an existing (role_id, permission_id) pair raises
        an integrity error due to the composite primary key."""

    def test_unauthorized_route_returns_403(self):
        """A Flask test client request to a permission-gated route, made
        by a user lacking that permission, returns HTTP 403."""

    def test_privilege_escalation_attempt_blocked(self):
        """A non-Admin user's attempt to call role/permission-management
        endpoints is rejected by the has_permission('subject.manage')-
        style guard before any row is written."""


if __name__ == "__main__":
    unittest.main()

Coverage Rationale

The eleven cases above cover, in order:

1. User–role assignment

2. Role–permission assignment

3. Multi-table permission lookup

4. The two possible outcomes of an authorization check (allow/deny)

5. The two structural multiplicities in the schema (many roles per user, many permissions per role)

6. The two uniqueness constraints that prevent data corruption

7. Route-level enforcement

8. Resistance to privilege escalation

These final two end-to-end concerns connect the database design back to the application architecture of Section 11.

15. Development Timeline

The twelve-week schedule below sequences the work so that the RBAC database design is established first and is then progressively wrapped by the domain model, service layer, and Flask integration described in Sections 9–11.

16. Conclusion

This proposal has argued, and structurally supported, a single technical thesis: that a normalized PostgreSQL RBAC database — five tables, two of them junction tables resolving the model’s two many-to-many relationships — combined with a composition-based, rather than inheritance-based, object-oriented authorization model in Python, provides a flexible, maintainable, and secure authorization foundation for an Online Examination System.

Every section of this proposal returns to that same relational core: the users → user_roles → roles → role_permissions → permissions traversal introduced in Section 1 reappears as the entity-relationship diagram in Section 4, as the DDL in Section 6, as the authorization queries in Section 8, as the User/Role/Permission composition in Section 9, as the layered architecture in Section 11, and as the concrete allow/deny outcomes worked through in Section 13. The Online Examination System itself supplies only the concrete roles (Admin, Teacher, Student) and permissions (exam.create, exam.take, and so on) that populate this structure — the structure itself is the contribution, and is designed to generalize to any domain in which a fixed, auditable set of roles must govern access to a larger, evolving set of protected actions.

The twelve-week timeline in Section 15 and the eleven-case testing plan in Section 14 together define a concrete path from this proposal to a working, security-reviewed implementation, without at any point requiring speculation about results not yet produced.

17. References

1. Ferraiolo, D. F., Sandhu, R., Gavrila, S., Kuhn, D. R., & Chandramouli, R. (2001). Proposed NIST Standard for Role-Based Access Control. ACM Transactions on Information and System Security (TISSEC), 4(3), 224–274.

2. Sandhu, R. S., Coyne, E. J., Feinstein, H. L., & Youman, C. E. (1996). Role-Based Access Control Models. IEEE Computer, 29(2), 38–47.

3. Open Web Application Security Project (OWASP). (2021). OWASP Top 10: Broken Access Control (A01:2021). OWASP Foundation.

4. Grinberg, M. (2018). Flask Web Development: Developing Web Applications with Python (2nd ed.). O'Reilly Media.

5. MySQL Documentation Team. (2024). MySQL 8.0 Reference Manual: Constraints and Referential Integrity. Oracle Corporation.

6. Python Software Foundation. (2024). PEP 557 – Data Classes. Python.org.