# User Management System Documentation

## Overview

A complete user management system with authentication, authorization, and security features using SQLite via the Sqler module.

## Features

### Security
- ✅ **Argon2 password hashing** - OWASP recommended algorithm
- ✅ **Encrypted session tokens** - SHA-256 hashed
- ✅ **Account lockout** - After 5 failed login attempts
- ✅ **Session expiration** - 24-hour default
- ✅ **Audit logging** - Complete activity trail
- ✅ **Permission-based access** - Granular authorization
- ✅ **Password complexity** - Minimum 8 characters
- ✅ **2FA ready** - Schema includes 2FA fields

### User Information
- `id` - Unique auto-generated timestamp ID
- `username` - Unique login identifier
- `email` - Unique email address
- `password_hash` - Argon2 hashed password
- `permission_keys` - JSON array of permission keys
- `created_at` - Account creation timestamp
- `updated_at` - Last update (for optimistic locking)
- `last_login_at` - Last successful login
- `last_login_ip` - IP address of last login
- `failed_login_attempts` - Counter for failed logins
- `locked_until` - Account lock expiration
- `is_active` - Account enabled/disabled
- `session_token` - Current valid session (hashed)
- `session_expires_at` - Session expiration time
- `password_changed_at` - Last password change
- `must_change_password` - Force password change flag
- `two_factor_enabled` - 2FA status
- `two_factor_secret` - 2FA secret (encrypted)
- `metadata` - JSON for additional data

## Setup

### 1. Install Dependencies

Add to `mix.exs`:
```elixir
{:argon2_elixir, "~> 4.0"}
```

Then run:
```bash
mix deps.get
```

### 2. Start Application

The users database and tables are automatically created on application startup (configured in `lib/mcp/application.ex`).

### 3. Create First Admin User

```elixir
# In IEx
iex> User.create("admin", "admin@example.com", "SecurePass123", [123456, 11111])
{:ok, 1738265123456}

# With metadata
iex> User.create("alice", "alice@example.com", "Pass123", [123456],
  metadata: %{department: "Engineering", role: "Developer"})
{:ok, 1738265234567}
```

## Usage Examples

### Creating Users

```elixir
# Basic user with permissions
{:ok, user_id} = User.create(
  "bob",
  "bob@example.com",
  "SecurePassword123",
  [123456]  # Has "index:view" permission
)

# Admin user with multiple permissions
{:ok, admin_id} = User.create(
  "admin",
  "admin@example.com",
  "AdminPass456",
  [123456, 11111],  # Has both "index:view" and "admin:manage"
  metadata: %{department: "IT", role: "Administrator"}
)

# Inactive user requiring password change
{:ok, temp_id} = User.create(
  "temporary",
  "temp@example.com",
  "TempPass789",
  [],
  is_active: false,
  must_change_password: true
)
```

### Authentication

```elixir
# Login with username and password
case User.authenticate("alice", "Pass123", "192.168.1.100") do
  {:ok, user} ->
    IO.puts("Welcome #{user.username}!")
    IO.inspect(user.permission_keys)

  {:error, :invalid_credentials} ->
    IO.puts("Wrong username or password")

  {:error, :account_locked} ->
    IO.puts("Account locked due to too many failed attempts")

  {:error, :account_disabled} ->
    IO.puts("Account has been disabled")
end

# Create session after successful login
{:ok, user} = User.authenticate("alice", "Pass123")
{:ok, session_token} = User.create_session(user.id)

# Store session_token in encrypted cookie (see router integration)

# Validate session later
case User.validate_session(session_token) do
  {:ok, user} ->
    # Session is valid, user is authenticated
    IO.puts("Authenticated as #{user.username}")

  {:error, :session_expired} ->
    # Need to log in again
    IO.puts("Session expired, please log in")

  {:error, :invalid_session} ->
    IO.puts("Invalid session")
end

# Logout
:ok = User.clear_session("alice")
```

### Permission Management

```elixir
# Get user's current permissions
{:ok, permissions} = User.get_permissions(user_id)
# => [123456]

# Add new permission
{:ok, updated_permissions} = User.add_permissions(user_id, [11111])
# => [123456, 11111]

# Remove permission
{:ok, new_permissions} = User.remove_permissions(user_id, [11111])
# => [123456]

# Set permissions (replace all)
{:ok, permissions} = User.set_permissions(user_id, [123456, 11111, 99999])
# => [123456, 11111, 99999]
```

### Password Management

```elixir
# User changes their own password
case User.change_password(user_id, "OldPass123", "NewPass456") do
  :ok ->
    IO.puts("Password changed successfully")
    # Note: Current session is invalidated

  {:error, :invalid_current_password} ->
    IO.puts("Current password is incorrect")

  {:error, reason} ->
    IO.puts("Error: #{inspect(reason)}")
end

# Admin resets user's password
:ok = User.reset_password(user_id, "TemporaryPass789")
# User will be forced to change password on next login
```

### Account Management

```elixir
# Activate account
:ok = User.activate_account(user_id)

# Deactivate account
:ok = User.deactivate_account(user_id)

# Unlock locked account
:ok = User.unlock_account(user_id)

# Find user
{:ok, user} = User.find_by_username("alice")
{:ok, user} = User.find_by_email("alice@example.com")
{:ok, user} = User.get_user(user_id)

# List all active users
active_users = User.list_active_users()
Enum.each(active_users, fn user ->
  IO.puts("#{user.username} - #{user.email}")
end)
```

### Audit Logging

```elixir
# Get user's audit log
log_entries = User.get_audit_log(user_id, limit: 50)

Enum.each(log_entries, fn [id, user_id, action, details, ip, user_agent, created_at, _] ->
  IO.puts("#{action} at #{created_at} from #{ip}")
end)

# Filter by action
login_attempts = User.get_audit_log(user_id, action: "login_failed", limit: 10)

# All audit events:
# - user_created
# - login_success
# - login_failed
# - session_created
# - session_cleared
# - password_changed
# - password_reset_by_admin
# - permissions_added
# - permissions_removed
# - permissions_set
# - account_activated
# - account_deactivated
# - account_unlocked
```

## Integration with Router

### Update Router to Use User Management

```elixir
# In lib/my_mcp_server_router.ex

get "/login" do
  # Show login form
  send_resp(conn, 200, login_form_html())
end

post "/login" do
  username = conn.body_params["username"]
  password = conn.body_params["password"]
  ip_address = to_string(:inet.ntoa(conn.remote_ip))

  case User.authenticate(username, password, ip_address) do
    {:ok, user} ->
      # Create session
      {:ok, session_token} = User.create_session(user.id)

      # Encrypt and store in cookie
      encrypted_value = encrypt_cookie_value(to_string(user.id))

      conn
      |> put_resp_cookie("user", encrypted_value,
        max_age: 60 * 60 * 24,  # 24 hours
        path: "/",
        secure: true,
        http_only: true,
        same_site: "Lax"
      )
      |> put_resp_cookie("session", session_token,
        max_age: 60 * 60 * 24,
        path: "/",
        secure: true,
        http_only: true,
        same_site: "Lax"
      )
      |> put_resp_header("location", "/dashboard")
      |> send_resp(302, "Redirecting...")

    {:error, reason} ->
      conn
      |> send_resp(401, "Login failed: #{reason}")
  end
end

post "/logout" do
  # Get user from cookie
  user = get_user(conn)

  # Clear session in database
  User.clear_session(user.id)

  # Clear cookies
  conn
  |> delete_resp_cookie("user", path: "/")
  |> delete_resp_cookie("session", path: "/")
  |> put_resp_header("location", "/login")
  |> send_resp(302, "Logged out")
end

# Update get_user to use database
defp get_user(conn) do
  conn = fetch_cookies(conn)

  case conn.cookies["user"] do
    nil ->
      %{id: 0, keys: %{}}

    encrypted ->
      case decrypt_cookie_value(encrypted) do
        {:ok, user_id_str} ->
          user_id = String.to_integer(user_id_str)

          case User.get_user(user_id) do
            {:ok, db_user} ->
              # Convert permission keys list to map
              keys_map = db_user.permission_keys
              |> Enum.map(&{&1, true})
              |> Map.new()

              %{id: db_user.id, keys: keys_map, username: db_user.username}

            {:error, _} ->
              %{id: 0, keys: %{}}
          end

        {:error, _} ->
          %{id: 0, keys: %{}}
      end
  end
end
```

## Security Considerations

### Password Security
- ✅ Argon2 hashing (OWASP recommended)
- ✅ Minimum 8 character requirement
- ✅ Protection against timing attacks
- ⚠️ Consider adding password complexity rules (uppercase, numbers, symbols)
- ⚠️ Consider password expiration policy
- ⚠️ Consider password history (prevent reuse)

### Session Security
- ✅ 24-hour expiration
- ✅ SHA-256 hashed tokens
- ✅ Secure random generation
- ✅ Invalidated on password change
- ⚠️ Consider session rotation on privilege escalation
- ⚠️ Consider device fingerprinting

### Account Security
- ✅ Account lockout after 5 failed attempts
- ✅ 15-minute lockout duration
- ✅ Audit logging of all security events
- ✅ Account activation/deactivation
- ⚠️ Consider CAPTCHA after 2 failed attempts
- ⚠️ Consider email notifications for security events

### Data Security
- ✅ Email stored lowercase for consistency
- ✅ Passwords never stored in plain text
- ✅ Session tokens hashed before storage
- ⚠️ Consider encrypting two_factor_secret
- ⚠️ Consider field-level encryption for sensitive metadata

## Database Schema

```sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  permission_keys TEXT NOT NULL DEFAULT '[]',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  last_login_at INTEGER,
  last_login_ip TEXT,
  failed_login_attempts INTEGER DEFAULT 0,
  locked_until INTEGER,
  is_active INTEGER DEFAULT 1,
  session_token TEXT,
  session_expires_at INTEGER,
  password_changed_at INTEGER,
  must_change_password INTEGER DEFAULT 0,
  two_factor_enabled INTEGER DEFAULT 0,
  two_factor_secret TEXT,
  metadata TEXT DEFAULT '{}'
);

CREATE TABLE user_audit_log (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  action TEXT NOT NULL,
  details TEXT,
  ip_address TEXT,
  user_agent TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);
```

## Configuration

### Customize Security Settings

In `lib/user.ex`, you can modify:

```elixir
@max_failed_attempts 5                # Failed login attempts before lockout
@lockout_duration_seconds 900         # 15 minutes lockout
@session_duration_seconds 86400       # 24 hours session
```

### Customize Validation

Modify validation functions in User module:

```elixir
defp validate_password(password) do
  cond do
    String.length(password) < 12 ->  # Increase minimum
      {:error, "Password must be at least 12 characters"}

    not Regex.match?(~r/[A-Z]/, password) ->  # Require uppercase
      {:error, "Password must contain uppercase letter"}

    not Regex.match?(~r/[0-9]/, password) ->  # Require number
      {:error, "Password must contain a number"}

    not Regex.match?(~r/[!@#$%^&*]/, password) ->  # Require symbol
      {:error, "Password must contain a symbol"}

    true ->
      :ok
  end
end
```

## Testing

```elixir
# Create test user
{:ok, user_id} = User.create("test_user", "test@example.com", "TestPass123", [123456])

# Test authentication success
{:ok, user} = User.authenticate("test_user", "TestPass123")
assert user.username == "test_user"

# Test authentication failure
{:error, :invalid_credentials} = User.authenticate("test_user", "WrongPassword")

# Test account lockout
Enum.each(1..5, fn _ ->
  User.authenticate("test_user", "WrongPassword")
end)

{:error, :account_locked} = User.authenticate("test_user", "TestPass123")

# Test session management
{:ok, session_token} = User.create_session(user_id)
{:ok, user} = User.validate_session(session_token)
:ok = User.clear_session(user_id)
{:error, :invalid_session} = User.validate_session(session_token)

# Test permissions
{:ok, [123456]} = User.get_permissions(user_id)
{:ok, [123456, 11111]} = User.add_permissions(user_id, [11111])
{:ok, [123456]} = User.remove_permissions(user_id, [11111])
```

## Troubleshooting

### Database Not Found
```elixir
# Verify database is started
:observer.start()  # Check if :users_db is running

# Or restart manually
{:ok, db} = Sqler.start_link("users", register: :users_db)
User.setup_database(db)
```

### Argon2 Not Found
```bash
# Install dependencies
mix deps.get
mix deps.compile
```

### Permission Denied Errors
```elixir
# Check user permissions
{:ok, user} = User.get_user(user_id)
IO.inspect(user.permission_keys)

# Verify permission keys match Auth.Authorization
# In lib/auth/authorization.ex, check @permissions map
```

## Best Practices

1. **Never log passwords** - Even in debug mode
2. **Use HTTPS only** - Set `secure: true` on cookies
3. **Regular security audits** - Review audit logs
4. **Backup database** - Regular backups of `data/users.sqlite`
5. **Monitor failed logins** - Alert on unusual patterns
6. **Update dependencies** - Keep Argon2 and other deps current
7. **Test thoroughly** - Unit tests for all auth flows
8. **Document security decisions** - Why certain settings are chosen

## Migration from Old System

If migrating from the hardcoded user system:

```elixir
# Old system (in router)
@james 1_769_578_105_155_195_386
users = %{@james => %{123456 => true, 11111 => true}}

# Create equivalent user
{:ok, james_id} = User.create(
  "james",
  "james@example.com",
  "ChooseStrongPassword",
  [123456, 11111],
  metadata: %{original_id: @james}
)

# Update router's get_user/1 to use User module (see Integration section)
```

## API Reference

See module documentation: `h User` in IEx for complete API reference.

Key functions:
- `User.setup_database/1` - Initialize tables
- `User.create/5` - Create new user
- `User.authenticate/4` - Login with password
- `User.validate_session/2` - Check session token
- `User.create_session/2` - Generate session token
- `User.clear_session/2` - Logout
- `User.get_permissions/2` - Get user permissions
- `User.change_password/4` - Change password
- `User.get_audit_log/3` - Retrieve activity log