CSE 340: Web Backend Development

W05 Learning Activity: Authentication and Authorization

Overview

In this activity you will learn about authentication and authorization concepts, including role-based access control. You will also create the users and roles tables in the database that your application will need.

Preparation Material

Access Controls and Accounts

Modern web applications serve different users with different needs and permissions. A school portal might need to tell the difference between students viewing their grades and instructors entering them. An online store must separate customers browsing products from administrators managing inventory. A content management system requires different abilities for authors, editors, and administrators. This reading explores how applications make these differences through access control systems.

By the end of this reading, you will understand the basic difference between authentication and authorization, explore several methods for implementing access control, and learn how role-based systems work with relational databases. This knowledge provides the foundation for building applications that serve different types of users while maintaining security and appropriate access boundaries.

Authentication vs Authorization

Before learning about access control systems, you must understand the difference between two frequently confused concepts: authentication and authorization. Though often mentioned together, they serve different purposes in application security.

Authentication: Proving Who You Are

Authentication answers the question "Who are you?" When you log into a website with your username and password, you authenticate yourself by proving your identity. The system verifies that you are who you claim to be by checking your credentials against stored information. Authentication establishes your identity but says nothing about what you can do once inside the system.

Think of authentication like showing your ID at a building entrance. The security guard verifies your identity, confirms you are who your ID says you are, and lets you enter. However, your ID alone does not determine which floors you can access or which rooms you can enter once inside.

Authorization: Determining What You Can Do

Authorization answers the question "What are you allowed to do?" After you have authenticated (proven your identity), the system must determine your permissions. Can you view this page? Can you edit this content? Can you delete these records? Authorization controls access to resources and features based on who you are.

Continuing the building example, authorization is like the access card you receive after showing your ID. Your card might grant access to certain floors but not others, certain rooms but not the server room, certain times but not after hours. Your identity has been verified (authentication), and now your permissions determine where you can go and what you can do (authorization).

Methods of Access Control

Once users authenticate, applications need systematic ways to control what authenticated users can access and modify. Different applications require different levels of complexity in their access control systems. A simple blog might need only basic permission flags, while an enterprise system might require sophisticated role hierarchies and detailed permissions. Let us examine several common approaches, progressing from simple to more complex.

Simple Permission Flags

The simplest form of access control uses boolean flags stored directly on user accounts. A user record might include fields like isAdmin, canPost, or canModerate. When the application needs to check permissions, it simply looks at these flags.

// Simple permission flag approach
const user = {
    id: 1,
    name: 'John Smith',
    email: 'jsmith@example.com',
    isAdmin: false,
    canEditPosts: true,
    canDeleteComments: false
};

// Checking permissions
if (user.isAdmin) {
    // Allow access to admin panel
}

if (user.canEditPosts) {
    // Show edit button
}

This approach works well for simple applications with few permission types. It is easy to understand and implement, requires minimal database structure, and provides clear, direct permission checks. However, it scales poorly as applications grow more complex. Adding new permission types requires database schema changes. Managing users with many different permission combinations becomes difficult, and you might end up with dozens of boolean fields in your user table.

Access Control Lists

Access Control Lists (ACLs) offer more flexibility by explicitly listing what each user can access. Rather than storing general permission flags, ACLs associate specific resources with specific users and their allowed actions. This approach is common in file systems and content management systems where permissions vary by resource.

// ACL approach - permissions tied to specific resources
const permissions = [
    { userId: 1, resourceType: 'post', resourceId: 42, action: 'read' },
    { userId: 1, resourceType: 'post', resourceId: 42, action: 'edit' },
    { userId: 1, resourceType: 'post', resourceId: 57, action: 'read' },
    { userId: 2, resourceType: 'post', resourceId: 42, action: 'read' }
];

// Checking if user can edit a specific post
function canUserEditPost(userId, postId) {
    return permissions.some(p => 
        p.userId === userId && 
        p.resourceType === 'post' && 
        p.resourceId === postId && 
        p.action === 'edit'
    );
}

ACLs provide detailed control over individual resources. User permissions can vary by specific item, making them ideal when different users need different access to the same types of resources. A document management system might use ACLs so that users can share specific documents with specific colleagues while keeping others private.

The tradeoff is complexity. ACL tables can grow very large, and checking permissions requires more database queries. Managing permissions becomes more involved, as you must track permissions for every user-resource combination. For many applications, this level of detail is unnecessary.

Role-Based Access Control

Role-Based Access Control (RBAC) strikes a balance between simplicity and flexibility. Instead of granting permissions directly to users or tracking permissions per resource, RBAC groups users into roles. Each role has a defined set of permissions, and users inherit the permissions of their assigned role.

Consider a school portal application. Rather than marking individual users with flags like canViewGrades, canEditGrades, canManageCourses, and canCreateAccounts, you create roles: student, instructor, and admin. Each role comes with an understood set of abilities. Students can view their own grades, instructors can enter grades for their courses, and administrators can manage the entire system.

// RBAC approach - permissions associated with roles
const user = {
    id: 1,
    name: 'John Smith',
    email: 'jsmith@example.com',
    roleId: 2  // References a role
};

const roles = {
    1: { name: 'user', permissions: ['view_profile', 'edit_own_profile'] },
    2: { name: 'admin', permissions: ['view_profile', 'edit_own_profile', 'edit_any_profile', 'manage_users'] }
};

// Checking permissions through roles
function hasPermission(user, permission) {
    const role = roles[user.roleId];
    return role.permissions.includes(permission);
}

RBAC offers several advantages that make it popular in many applications. Roles align naturally with organizational structure (employee, manager, administrator). Permission management becomes simpler because you manage role permissions rather than individual user permissions. When you need to change what managers can do, you update the manager role once rather than updating hundreds of individual user records. New users can be quickly assigned appropriate access by assigning them a role.

RBAC handles the common case well: most users fit into predefined categories with standard permissions. The system remains flexible enough to add new roles as needs change, while staying simple enough to understand and maintain. This is the approach you will implement in this course.

Role-Based Access Control and Relational Databases

Understanding how RBAC works with relational databases is important for implementation. The database structure directly impacts how efficiently you can check permissions and how easily you can modify role definitions. Let us examine the typical database design and then explore how application code uses that structure to enforce access control.

Database Structure for RBAC

A basic RBAC system typically involves at least two tables: a users table and a roles table. The users table contains account information (name, email, password hash) plus a foreign key reference to the roles table. The roles table defines available roles and can optionally store role-specific settings.

-- Roles table defines available roles
CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    role_description TEXT
);

-- Users table references roles
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role_id INTEGER REFERENCES roles(role_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Seed with basic roles
INSERT INTO roles (role_name, role_description) VALUES 
    ('user', 'Standard user with basic access'),
    ('admin', 'Administrator with full system access');

This structure establishes a relationship: each user has exactly one role, and each role can be assigned to many users (a one-to-many relationship). When you query for a user, you can easily join to the roles table to retrieve their role information. When you need to update role definitions, you modify the roles table, and all users with that role immediately reflect the change.

More sophisticated systems might include additional tables. A permissions table could list all possible actions in the system. A role_permissions join table would then associate roles with their specific permissions, allowing dynamic permission assignment without code changes. For this course, you will keep the structure simpler, with permission logic handled in application code rather than in the database.

Enforcing Access Control in Application Code

Database structure stores roles, but application code enforces them. In Express applications, middleware functions provide a good way to check permissions before allowing access to routes. These middleware functions examine the authenticated user's role and either allow the request to proceed or deny access.

// Middleware to require specific role
function requireRole(roleName) {
    return (req, res, next) => {
        // Assumes user object is attached to req by authentication middleware
        if (!req.user) {
            return res.status(401).send('Not authenticated');
        }

        if (req.user.role_name !== roleName) {
            return res.status(403).send('Insufficient permissions');
        }

        // User has required role, allow request to proceed
        next();
    };
}

// Using the middleware on routes
app.get('/admin/dashboard', requireRole('admin'), (req, res) => {
    res.render('admin/dashboard');
});

app.post('/admin/users/:id/edit', requireRole('admin'), (req, res) => {
    // Only admins can reach this code
    // Handle user editing logic
});

This pattern is common and effective. Middleware functions run before route handlers, providing a checkpoint where access can be verified. If the user lacks permission, the middleware sends an error response and stops the request. If the user has permission, the middleware calls next(), allowing the request to continue to the route handler.

Controllers can also check permissions for more detailed access control. While middleware works well for route-level protection (entire routes require specific roles), controllers handle situation-specific logic (users can edit their own profile, but admins can edit any profile).

// Controller with role-specific logic
async function editUserProfile(req, res) {
    const targetUserId = req.params.userId;
    const currentUser = req.user;

    // Admins can edit anyone, users can only edit themselves
    const canEdit = currentUser.role_name === 'admin' || 
                   currentUser.user_id === parseInt(targetUserId);

    if (!canEdit) {
        return res.status(403).send('You cannot edit this profile');
    }

    // Proceed with editing logic
    // ...
}

This combination of middleware for broad role requirements and controller logic for specific permission scenarios provides flexible, maintainable access control. Middleware keeps unauthorized users away from entire sections of the application, while controller logic handles the details of who can do what to which specific resources.

Activity Instructions

In this activity you will set up database tables for role-based access control for your application.

Create the Roles Table

The first step is to create a table to store the different roles that users can have in your application.

Complete the following:

1. Open pgAdmin and connect to your project database.
2. Open the Query tool.
3. Write a SQL statement to create a table named roles with the following columns:
   • role_id - a serial (auto-incrementing integer) that serves as the primary key
   • role_name - a variable character field (VARCHAR) with a maximum length of 50 characters that must be unique and cannot be null
   • role_description - a text field that can store a longer description of the role
4. Execute the SQL statement to create the table.
5. Verify that the table was created successfully by viewing the table structure in pgAdmin.
6. Add this SQL to your setup.sql file.

CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL,
    role_description TEXT
);

Insert Initial Role Data

Now that you have created the roles table, you need to add some initial roles to the table.

Complete the following:

1. In your SQL query file, write an INSERT statement to add two roles to the roles table:
   • A role named "user" with the description "Standard user with basic access"
   • A role named "admin" with the description "Administrator with full system access"
2. Execute the INSERT statement to add the roles to the table.
3. Write and execute a SELECT statement to verify that the roles were inserted successfully.
4. Add this SQL to your setup.sql file.

INSERT INTO roles (role_name, role_description) VALUES 
    ('user', 'Standard user with basic access'),
    ('admin', 'Administrator with full system access');

-- Verify the data was inserted
SELECT * FROM roles;

Create the Users Table

Next, you will create a table to store user account information. This table will include a foreign key reference to the roles table.

Complete the following:

1. Write an SQL statement to create a table named users with the following columns:
   • user_id - a serial (auto-incrementing integer) that serves as the primary key
   • name - a VARCHAR(100) field that cannot be null (this will store the user's display name)
   • email - a VARCHAR(100) field that must be unique and cannot be null (this will serve as the username)
   • password_hash - a VARCHAR(255) field that cannot be null (this will store the hashed password)
   • role_id - an integer field that references the role_id column in the roles table
   • created_at - a timestamp field that defaults to the current timestamp
2. Make sure to include the foreign key constraint that references the roles table.
3. Execute the SQL statement to create the table.
4. Add this SQL to your setup.sql file.

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role_id INTEGER REFERENCES roles(role_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Verify the Database Structure

Finally, you should verify that both tables were created correctly and that the relationship between them is working as expected.

Complete the following:

1. In your database management tool, examine the structure of both the roles and users tables.
2. Verify that the foreign key constraint on the users table correctly references the roles table.
3. Try inserting a test user record with a valid role_id (either 1 for "user" or 2 for "admin"). You can use a placeholder password hash like 'placeholder_hash' for now since you have not yet learned how to hash passwords.
4. Write and execute a SELECT statement that joins the users and roles tables to see the user information along with their role name.
5. Delete the test user record after verifying the join works correctly.

-- Insert a test user
INSERT INTO users (name, email, password_hash, role_id) 
VALUES ('testuser', 'test@example.com', 'placeholder_hash', 1);

-- Join users and roles to see complete information
SELECT u.user_id, u.name, u.email, r.role_name, r.role_description
FROM users u
JOIN roles r ON u.role_id = r.role_id;

-- Delete the test user
DELETE FROM users WHERE email = 'test@example.com';

Next Step

Complete the other Week 05 Learning Activities

After you have completed all the learning activities for this lesson, return to Canvas to submit a quiz.

Other Links:

• Return to: Week Overview | Course Home