Full-Stack Role-Based & Claims Architecture Masterclass

End-to-End Enterprise Authorization System across SQL Server, .NET 9 Web API & Angular 21

1. Core Authorization Concepts & Real-Life Metaphors

Architecture Fundamentals

1.1 Authentication (AuthN) vs Authorization (AuthZ) vs Claims (CBAC)

🏨 The Hotel Passport & Keycard Metaphor:

• Authentication (AuthN) — "Who are you?"
Showing your Passport at the Hotel Reception desk. The receptionist verifies your identity and logs you in.

• Role-Based Authorization (RBAC) — "What group do you belong to?"
Your badge says Role: Guest, Role: Manager, or Role: Maintenance.

• Claims-Based Authorization (CBAC) — "What granular actions are you permitted to do?"
Your electronic keycard contains precise permissions: CanOpenRoom_304, CanAccessExecutiveLounge, CanUnlockSafe.

Concept	Definition	Enterprise Example	Where Enforced?
Authentication (AuthN)	Verifying user identity via credentials (Password, JWT, OAuth).	User inputs Email + Password ➡️ Receives JWT Token.	Auth Interceptor / API Gateway
RBAC (Role-Based)	Granting access based on high-level user groups (`Admin`, `Vendor`).	[Authorize(Roles = "Admin, Manager")]	Route Guards & API Controllers
CBAC (Claims-Based)	Granting fine-grained access based on specific key-value claims (`permission: OFFER_APPROVE`).	[Authorize(Policy = "CanApproveOffers")]	Custom Policy Handlers

2. SQL Server Database Architecture (5-Table RBAC & Permission Schema)

SQL Schema Architecture

2.1 The 5 Essential Relational Tables

To build a dynamic permission system where roles and permissions can be added via UI without recompiling code, use a 5-Table Normalized Schema:

-- 1. Users Master Table
CREATE TABLE Users (
  Id INT PRIMARY KEY IDENTITY(1,1),
  Email NVARCHAR(150) NOT NULL UNIQUE,
  PasswordHash NVARCHAR(500) NOT NULL,
  FullName NVARCHAR(100) NOT NULL,
  IsActive BIT NOT NULL DEFAULT 1
);

-- 2. Roles Master Table
CREATE TABLE Roles (
  Id INT PRIMARY KEY IDENTITY(1,1),
  RoleName VARCHAR(50) NOT NULL UNIQUE -- e.g. 'Admin', 'Vendor', 'Auditor'
);

-- 3. UserRoles Junction Table (Many-to-Many: 1 User can have multiple Roles)
CREATE TABLE UserRoles (
  UserId INT NOT NULL FOREIGN KEY REFERENCES Users(Id) ON DELETE CASCADE,
  RoleId INT NOT NULL FOREIGN KEY REFERENCES Roles(Id) ON DELETE CASCADE,
  PRIMARY KEY (UserId, RoleId)
);

-- 4. Permissions Master Table (Granular Actions)
CREATE TABLE Permissions (
  Id INT PRIMARY KEY IDENTITY(1,1),
  PermissionCode VARCHAR(100) NOT NULL UNIQUE, -- e.g. 'OFFER_CREATE', 'OFFER_APPROVE', 'OFFER_DELETE'
  Description NVARCHAR(200) NULL
);

-- 5. RolePermissions Junction Table (Many-to-Many: 1 Role has multiple Permissions)
CREATE TABLE RolePermissions (
  RoleId INT NOT NULL FOREIGN KEY REFERENCES Roles(Id) ON DELETE CASCADE,
  PermissionId INT NOT NULL FOREIGN KEY REFERENCES Permissions(Id) ON DELETE CASCADE,
  PRIMARY KEY (RoleId, PermissionId)
);

2.2 SQL Query to Fetch User Roles & Permissions for JWT Generation

-- Fast O(1) Indexed Query to fetch all User Roles & Granular Permission Codes during Login:
SELECT DISTINCT 
  u.Id AS UserId,
  r.RoleName,
  p.PermissionCode
FROM Users u
INNER JOIN UserRoles ur ON u.Id = ur.UserId
INNER JOIN Roles r ON ur.RoleId = r.Id
INNER JOIN RolePermissions rp ON r.Id = rp.RoleId
INNER JOIN Permissions p ON rp.PermissionId = p.Id
WHERE u.Id = @UserId AND u.IsActive = 1;

3. .NET Web API Implementation (JWT Claims & Custom Policy Handlers)

.NET Web API

3.1 Generating JWT Token with Roles & Permission Claims

public string GenerateJwtToken(User user, List<string> roles, List<string> permissions) {
  var claims = new List<Claim> {
    new Claim(ClaimTypes.NameIdentifier, user.Id.ToString()),
    new Claim(ClaimTypes.Email, user.Email)
  };

  // Append Role Claims:
  foreach (var role in roles) {
    claims.Add(new Claim(ClaimTypes.Role, role));
  }

  // Append Granular Permission Claims:
  foreach (var permission in permissions) {
    claims.Add(new Claim("permission", permission));
  }

  var key = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(_config["Jwt:Secret"]));
  var creds = new SigningCredentials(key, SecurityAlgorithms.HmacSha256);

  var token = new JwtSecurityToken(
    issuer: _config["Jwt:Issuer"],
    audience: _config["Jwt:Audience"],
    claims: claims,
    expires: DateTime.UtcNow.AddMinutes(15), // Short-lived 15 min expiry
    signingCredentials: creds
  );

  return new JwtSecurityTokenHandler().WriteToken(token);
}

3.2 Custom Permission Requirement & Policy Authorization Handler

// 1. Define Requirement
public class PermissionRequirement : IAuthorizationRequirement {
  public string Permission { get; }
  public PermissionRequirement(string permission) => Permission = permission;
}

// 2. Implement Authorization Handler
public class PermissionAuthorizationHandler : AuthorizationHandler<PermissionRequirement> {
  protected override Task HandleRequirementAsync(
    AuthorizationHandlerContext context, 
    PermissionRequirement requirement) 
  {
    // Check if user has the matching "permission" claim in their JWT token:
    var hasPermission = context.User.HasClaim(c => c.Type == "permission" && c.Value == requirement.Permission);

    if (hasPermission) {
      context.Succeed(requirement);
    }

    return Task.CompletedTask;
  }
}

3.3 Protecting Controller Endpoints in .NET

[ApiController]
[Route("api/[controller]")]
public class OffersController : ControllerBase {

  // 1. Role-Based Check: Requires Admin OR Vendor role
  [HttpGet]
  [Authorize(Roles = "Admin, Vendor")]
  public async Task<IActionResult> GetAllOffers() => Ok();

  // 2. Claims-Based Policy Check: Requires granular 'OFFER_APPROVE' permission!
  [HttpPost("{id}/approve")]
  [Authorize(Policy = "CanApproveOffers")] // Configured to require OFFER_APPROVE
  public async Task<IActionResult> ApproveOffer(int id) => Ok();
}

4. Angular 21 Frontend Architecture (Guards & Directives)

Angular 21 Implementation

4.1 Functional Route Guards (`roleGuard` & `permissionGuard`)

import { inject } from '@angular/core';
import { CanActivateFn, Router } from '@angular/router';
import { AuthService } from '@app/auth/services/auth.service';
import { NotificationService } from '@app/shared/services/notification.service';

// Functional Permission Guard in Angular 21:
export const permissionGuard = (requiredPermission: string): CanActivateFn => {
  return () => {
    const authService = inject(AuthService);
    const router = inject(Router);
    const notificationService = inject(NotificationService);

    if (authService.hasPermission(requiredPermission)) {
      return true; // Access Granted!
    }

    notificationService.showNotification('Access Denied: Missing required permission!', 'Close', 'error');
    router.navigate(['/access-denied']);
    return false;
  };
};

4.2 Angular Custom Structural Directive (`*hasPermission`)

Seamlessly show or hide UI buttons and UI sections based on user claims:

import { Directive, Input, TemplateRef, ViewContainerRef, inject } from '@angular/core';
import { AuthService } from '@app/auth/services/auth.service';

@Directive({
  selector: '[hasPermission]',
  standalone: true
})
export class HasPermissionDirective {
  private templateRef = inject(TemplateRef);
  private viewContainer = inject(ViewContainerRef);
  private authService = inject(AuthService);

  @Input() set hasPermission(permission: string) {
    if (this.authService.hasPermission(permission)) {
      this.viewContainer.createEmbeddedView(this.templateRef);
    } else {
      this.viewContainer.clear(); // Completely removes button from DOM!
    }
  }
}

Usage in HTML Templates:

<!-- Delete button rendered ONLY if user has 'OFFER_DELETE' permission -->
<button *hasPermission="'OFFER_DELETE'" (click)="deleteOffer(offer.id)" class="btn btn-danger">
  🗑️ Delete Offer
</button>

5. Fine-Grained Resource-Based Authorization (Data Ownership)

Advanced Data Security

5.1 Multi-Tenant Data Ownership Rules

🚨 The IDOR Vulnerability (Insecure Direct Object Reference):
Vendor A has permission OFFER_EDIT. Vendor A changes the URL ID in Angular to /offers/edit/999 (which belongs to Vendor B). If your backend only checks OFFER_EDIT without checking WHO OWNS THE OFFER, Vendor A can edit Vendor B's data! 💥

💡 Senior Fix (Resource-Based Authorization in .NET):
Always check ownership in service layer or EF Core global query filters:

public async Task<bool> UpdateOfferAsync(int offerId, OfferUpdateDto dto, int currentUserId, bool isAdmin) {
  var offer = await _context.Offers.FindAsync(offerId);
  if (offer == null) return false;

  // 🔐 RESOURCE-BASED OWNERSHIP CHECK:
  if (!isAdmin && offer.VendorUserId != currentUserId) {
    throw new UnauthorizedAccessException("You do not own this offer record!");
  }

  offer.Price = dto.Price;
  await _context.SaveChangesAsync();
  return true;
}

6. Security Pitfalls & Real-Time Token Revocation

Production Pitfalls

6.1 Top 3 Security Traps

• 1. Client-Side Security Fallacy: Hiding a button in Angular with `*hasPermission` does NOT secure your backend! Anyone can inspect element or call the REST API via Postman. **API Controller authorization is mandatory!**
• 2. Stale Claims in Long-Lived JWT Tokens: If an Admin revokes a user's role, their JWT token is still valid until expiration (e.g. 8 hours!).
• 3. Storing JWT Secrets in Client Code: Never store JWT secret signing keys in Angular `environment.ts`! Secrets stay strictly in .NET server `appsettings.json` / Azure Key Vault.

6.2 Real-Time Role Revocation Architecture

⚡ How Senior Architects Handle Instant Access Revocation:
1. Short-Lived Access Tokens (15 mins) + Refresh Tokens.
2. Redis Token Blacklist: When an admin revokes a user's access, add `UserId` to Redis blacklist.
3. SignalR Real-Time Push: Send a SignalR WebSocket event to the revoked user's Angular client to force immediate logout and clear local storage!

7. Senior Interview Q&A (7-8 YOE Level)

Interview Preparation

❓ Interviewer Question: "How do you design a Role-Based Access Control system that allows Admins to dynamically create new Roles and assign Permissions at runtime without restarting or redeploying the .NET Web API?"

💡 Best Senior Answer:
• We implement a 5-Table DB Schema (`Users`, `Roles`, `UserRoles`, `Permissions`, `RolePermissions`).
• In .NET, we register a custom IAuthorizationPolicyProvider that dynamically generates policy requirements from database permission codes on-the-fly.
• When login occurs, permissions are embedded as "permission" claims in the JWT token. The custom PermissionAuthorizationHandler evaluates these claims seamlessly without requiring code re-compilation or API restarts!

❓ Interviewer Question: "What is the difference between RBAC (Role-Based), CBAC (Claims-Based), and ABAC (Attribute-Based) Authorization?"

💡 Best Senior Answer:
• RBAC: Access granted by broad job title (`Admin`, `Vendor`). Coarse-grained.
• CBAC: Access granted by specific user capabilities or assertions (`permission: OFFER_DELETE`, `email_verified: true`). Fine-grained.
• ABAC: Access granted based on context attributes (`TimeOfDay < 5PM`, `IPAddress == OfficeNetwork`, `Department == Finance`). Highly dynamic context-aware security.

8. SQL Server Row-Level Security (RLS) & Role Matrix

SQL Row-Level Security

8.1 SQL Server Row-Level Security (RLS)

Row-Level Security enforces data access restriction at the SQL Server Engine level, so every query automatically filters rows based on execution context:

-- 1. Create Security Predicate Function
CREATE FUNCTION Security.fn_OfferTenantAccessPredicate(@TenantId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
  SELECT 1 AS AccessResult
  WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT)
     OR CAST(SESSION_CONTEXT(N'IsSuperAdmin') AS BIT) = 1;

-- 2. Apply Security Policy to Table
CREATE SECURITY POLICY Security.OfferSecurityPolicy
ADD FILTER PREDICATE Security.fn_OfferTenantAccessPredicate(TenantId)
ON dbo.Offers
WITH (STATE = ON);

8.2 Enterprise Role vs Permission Matrix

Permission Code	SuperAdmin	Manager	Vendor	Auditor (Read-Only)
OFFER_VIEW	✅ YES	✅ YES	✅ YES (Own Only)	✅ YES
OFFER_CREATE	✅ YES	✅ YES	✅ YES	❌ NO
OFFER_APPROVE	✅ YES	✅ YES	❌ NO	❌ NO
OFFER_DELETE	✅ YES	❌ NO	❌ NO	❌ NO