User Authentication System Project

Project Overview

A security-focused database project for implementing a complete user authentication system with best practices including password hashing, session management, two-factor authentication, and role-based access control.

Learning Objectives

• Implement secure authentication
• Manage user sessions
• Handle password resets
• Implement two-factor authentication (2FA)
• Create role-based access control (RBAC)
• Track login attempts and security events
• Implement account verification
• Handle OAuth/Social login

Database Schema

Tables Overview

1. users: Core user accounts
2. roles: User roles
3. permissions: System permissions
4. role_permissions: Role-permission mapping
5. user_roles: User-role mapping
6. sessions: Active user sessions
7. password_resets: Password reset tokens
8. email_verifications: Email verification tokens
9. two_factor_auth: 2FA settings
10. login_attempts: Track login attempts
11. security_logs: Security event logging
12. oauth_providers: Social login providers
13. oauth_accounts: Linked social accounts

Implementation Steps

Step 1: Create Database

CREATE DATABASE auth_system_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE auth_system_db;

Step 2: Create Users Table

CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    avatar_url VARCHAR(255),

    -- Status flags
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    is_locked BOOLEAN DEFAULT FALSE,

    -- Security
    failed_login_attempts INT UNSIGNED DEFAULT 0,
    locked_until TIMESTAMP NULL,
    password_changed_at TIMESTAMP NULL,
    must_change_password BOOLEAN DEFAULT FALSE,

    -- Two-factor authentication
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    two_factor_secret VARCHAR(255),

    -- Timestamps
    email_verified_at TIMESTAMP NULL,
    last_login_at TIMESTAMP NULL,
    last_login_ip VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_active (is_active),
    INDEX idx_verified (is_verified),
    INDEX idx_locked (is_locked),
    INDEX idx_last_login (last_login_at)
) ENGINE=InnoDB;

Step 3: Create Roles Table

CREATE TABLE roles (
    role_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    role_key VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    is_system_role BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_role_key (role_key)
) ENGINE=InnoDB;

Step 4: Create Permissions Table

CREATE TABLE permissions (
    permission_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    permission_name VARCHAR(100) NOT NULL,
    permission_key VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255),
    resource VARCHAR(50),
    action VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_permission_key (permission_key),
    INDEX idx_resource (resource),
    INDEX idx_action (action)
) ENGINE=InnoDB;

Step 5: Create Role_Permissions Junction Table

CREATE TABLE role_permissions (
    role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
        ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(permission_id)
        ON DELETE CASCADE,
    INDEX idx_role (role_id),
    INDEX idx_permission (permission_id)
) ENGINE=InnoDB;

Step 6: Create User_Roles Junction Table

CREATE TABLE user_roles (
    user_id INT UNSIGNED NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by INT UNSIGNED,

    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
        ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(user_id)
        ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_role (role_id)
) ENGINE=InnoDB;

Step 7: Create Sessions Table

CREATE TABLE sessions (
    session_id VARCHAR(255) PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    payload TEXT,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_last_activity (last_activity),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB;

Step 8: Create Password_Resets Table

CREATE TABLE password_resets (
    reset_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    email VARCHAR(255) NOT NULL,
    token VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45),
    used BOOLEAN DEFAULT FALSE,
    used_at TIMESTAMP NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_email (email),
    INDEX idx_token (token),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB;

Step 9: Create Email_Verifications Table

CREATE TABLE email_verifications (
    verification_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    email VARCHAR(255) NOT NULL,
    token VARCHAR(255) NOT NULL,
    verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_token (token),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB;

Step 10: Create Two_Factor_Auth Table

CREATE TABLE two_factor_auth (
    tfa_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL UNIQUE,
    secret_key VARCHAR(255) NOT NULL,
    backup_codes JSON,
    enabled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used_at TIMESTAMP NULL,

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

Step 11: Create Login_Attempts Table

CREATE TABLE login_attempts (
    attempt_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    username VARCHAR(50),
    email VARCHAR(255),
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    success BOOLEAN NOT NULL,
    failure_reason VARCHAR(100),

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_ip (ip_address),
    INDEX idx_attempted_at (attempted_at),
    INDEX idx_success (success)
) ENGINE=InnoDB;

Step 12: Create Security_Logs Table

CREATE TABLE security_logs (
    log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    event_type VARCHAR(50) NOT NULL,
    event_description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_event_type (event_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

Step 13: Create OAuth_Providers Table

CREATE TABLE oauth_providers (
    provider_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_name VARCHAR(50) NOT NULL UNIQUE,
    provider_key VARCHAR(50) NOT NULL UNIQUE,
    client_id VARCHAR(255),
    client_secret VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_provider_key (provider_key),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

Step 14: Create OAuth_Accounts Table

CREATE TABLE oauth_accounts (
    oauth_account_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    provider_id INT UNSIGNED NOT NULL,
    provider_user_id VARCHAR(255) NOT NULL,
    access_token TEXT,
    refresh_token TEXT,
    token_expires_at TIMESTAMP NULL,
    profile_data JSON,
    linked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    FOREIGN KEY (provider_id) REFERENCES oauth_providers(provider_id)
        ON DELETE CASCADE,
    UNIQUE KEY unique_provider_user (provider_id, provider_user_id),
    INDEX idx_user (user_id),
    INDEX idx_provider (provider_id)
) ENGINE=InnoDB;

Initial Data Setup

Insert Default Roles

INSERT INTO roles (role_name, role_key, description, is_system_role) VALUES
    ('Administrator', 'admin', 'Full system access', TRUE),
    ('Moderator', 'moderator', 'Content moderation access', TRUE),
    ('User', 'user', 'Standard user access', TRUE),
    ('Guest', 'guest', 'Limited guest access', TRUE);

Insert Default Permissions

INSERT INTO permissions (permission_name, permission_key, description, resource, action) VALUES
    ('Manage Users', 'users.manage', 'Create, edit, delete users', 'users', 'manage'),
    ('View Users', 'users.view', 'View user list', 'users', 'view'),
    ('Edit Own Profile', 'profile.edit', 'Edit own profile', 'profile', 'edit'),
    ('Manage Roles', 'roles.manage', 'Create, edit, delete roles', 'roles', 'manage'),
    ('Manage Permissions', 'permissions.manage', 'Assign permissions', 'permissions', 'manage'),
    ('View Logs', 'logs.view', 'View security logs', 'logs', 'view'),
    ('Manage Content', 'content.manage', 'Create, edit, delete content', 'content', 'manage'),
    ('Moderate Content', 'content.moderate', 'Moderate user content', 'content', 'moderate');

Assign Permissions to Roles

-- Admin gets all permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, permission_id FROM permissions;

-- Moderator gets specific permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT 2, permission_id FROM permissions
WHERE permission_key IN ('users.view', 'profile.edit', 'content.moderate', 'logs.view');

-- User gets basic permissions
INSERT INTO role_permissions (role_id, permission_id)
SELECT 3, permission_id FROM permissions
WHERE permission_key IN ('profile.edit');

Insert OAuth Providers

INSERT INTO oauth_providers (provider_name, provider_key, is_active) VALUES
    ('Google', 'google', TRUE),
    ('Facebook', 'facebook', TRUE),
    ('GitHub', 'github', TRUE),
    ('Twitter', 'twitter', TRUE);

Common Operations

User Registration

-- Insert new user
INSERT INTO users (username, email, password_hash, created_at)
VALUES ('john_doe', 'john@example.com', '$2y$10$...', NOW());

SET @user_id = LAST_INSERT_ID();

-- Assign default role
INSERT INTO user_roles (user_id, role_id)
SELECT @user_id, role_id FROM roles WHERE role_key = 'user';

-- Create email verification token
INSERT INTO email_verifications (user_id, email, token, expires_at)
VALUES (
    @user_id,
    'john@example.com',
    SHA2(CONCAT(@user_id, NOW(), RAND()), 256),
    DATE_ADD(NOW(), INTERVAL 24 HOUR)
);

-- Log security event
INSERT INTO security_logs (user_id, event_type, event_description, ip_address)
VALUES (@user_id, 'registration', 'New user registered', '192.168.1.100');

User Login

-- Record login attempt
INSERT INTO login_attempts (user_id, username, ip_address, success)
VALUES (1, 'john_doe', '192.168.1.100', TRUE);

-- Update user record
UPDATE users
SET
    last_login_at = NOW(),
    last_login_ip = '192.168.1.100',
    failed_login_attempts = 0
WHERE user_id = 1;

-- Create session
INSERT INTO sessions (session_id, user_id, ip_address, user_agent, expires_at)
VALUES (
    SHA2(CONCAT(1, NOW(), RAND()), 256),
    1,
    '192.168.1.100',
    'Mozilla/5.0...',
    DATE_ADD(NOW(), INTERVAL 2 HOUR)
);

Failed Login

-- Record failed attempt
INSERT INTO login_attempts (username, ip_address, success, failure_reason)
VALUES ('john_doe', '192.168.1.100', FALSE, 'invalid_password');

-- Increment failed attempts
UPDATE users
SET failed_login_attempts = failed_login_attempts + 1
WHERE username = 'john_doe';

-- Lock account if too many failures
UPDATE users
SET
    is_locked = TRUE,
    locked_until = DATE_ADD(NOW(), INTERVAL 30 MINUTE)
WHERE username = 'john_doe'
  AND failed_login_attempts >= 5;

Password Reset Request

-- Create reset token
INSERT INTO password_resets (user_id, email, token, expires_at)
SELECT
    user_id,
    email,
    SHA2(CONCAT(user_id, NOW(), RAND()), 256),
    DATE_ADD(NOW(), INTERVAL 1 HOUR)
FROM users
WHERE email = 'john@example.com';

-- Log security event
INSERT INTO security_logs (user_id, event_type, event_description, ip_address)
SELECT user_id, 'password_reset_request', 'Password reset requested', '192.168.1.100'
FROM users
WHERE email = 'john@example.com';

Check User Permissions

-- Check if user has specific permission
SELECT COUNT(*) > 0 AS has_permission
FROM users u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN role_permissions rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.permission_id
WHERE u.user_id = 1
  AND p.permission_key = 'users.manage';

-- Get all user permissions
SELECT DISTINCT p.permission_key, p.permission_name
FROM users u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN role_permissions rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.permission_id
WHERE u.user_id = 1;

Security Best Practices

1. Password Hashing: Always use bcrypt or Argon2
2. Password Policies: Enforce strong passwords
3. Account Lockout: Lock after failed attempts
4. Session Management: Expire sessions, regenerate IDs
5. Two-Factor Auth: Implement for sensitive operations
6. Audit Logging: Log all security events
7. Input Validation: Validate all user inputs
8. Rate Limiting: Limit login attempts per IP
9. HTTPS Only: Require secure connections
10. Regular Audits: Review security logs

Practice Exercises

Exercise 1: Basic Auth

1. Register new user
2. Verify email
3. Login user
4. Create session

Exercise 2: Password Management

1. Request password reset
2. Validate reset token
3. Change password
4. Force password change

Exercise 3: Role Management

1. Create custom role
2. Assign permissions
3. Assign role to user
4. Check user permissions

Exercise 4: Security Monitoring

1. Track failed logins
2. Identify suspicious activity
3. Generate security reports
4. Lock compromised accounts

Next Steps

• Security Best Practices
• Performance Optimization
• Backup Strategies

Additional Resources

• OWASP Authentication Cheat Sheet
• NIST Password Guidelines
• OAuth 2.0 Specification