Authorization System Documentation

Table of Contents

• Authorization System Documentation
  • Table of Contents
  • System Overview
  • Core Components
    • Permissions
    • Roles
    • Database Schema
  • Authorization Process
    • Workflow Diagram
    • Implementation Details
  • Usage Examples
  • Special Cases

---

System Overview

Our authorization system implements a granular, role-based access control (RBAC) model with tenant isolation. Key features include:

• Multi-tenant architecture with tenant-specific permissions
• Hierarchical permission structure
• Global and tenant-scoped roles
• Admin override capabilities
• Wildcard permission matching

---

Core Components

Permissions

Structure:

// Standard format
"scope:resource:action"

// Examples
"tenant:database:table:create"
"tenant:role:manage"
"user:read"

Types:

1. Global Permissions

   • tenant:create
   • user:read

2. Tenant-Specific Permissions

   • tenant:database:table:create
   • tenant:role:manage

Hierarchy:

tenant
  ├── role
  │   ├── create
  │   ├── read
  │   └── update
  ├── database
  │   ├── table
  │   └── query
  └── member
      ├── add
      └── remove

Roles

Types:

Global Roles (tenantID=null)	Tenant-Specific Roles
DATABASE_DEVELOPER	Custom roles defined per tenant
DATA_ANALYST
QUERY_MANAGER
SUPPORT_AGENT

Role-Permission Mapping:

-- DATABASE_DEVELOPER
'metadata:read', 'schema:create', 'table:create'

-- DATA_ANALYST 
'metadata:read', 'table:read', 'stats:read'

Database Schema

model tblPermissions {
  permissionID              Int
  permissionName            String
  permissionDescription     String?
}

model tblRoles {
  roleID                    Int
  roleName                  String
  tenantID                  Int?
}

model tblRolePermissionMappings {
  roleID       Int
  permissionID Int
}

---

Authorization Process

Workflow Diagram

Implementation Details

1. Authentication Middleware

authMiddleware.authProvider = async function (req, res, next) {
  // Firebase token verification
  const decodedIdToken = await firebaseApp.auth().verifyIdToken(idToken);
  req.user = await authService.getUserFromFirebaseID(decodedIdToken.uid);
  next();
};

2. Permission Check Middleware

authMiddleware.checkUserPermissions = (requiredPermissions, options) => {
  return async (req, res, next) => {
    const permissionCheck = await authService.checkUserPermissions({
      userID: user.userID,
      tenantID: parseInt(tenantID),
      requiredPermissions,
    });
    
    permissionCheck.permission ? next() : res.sendStatus(403);
  };
};

3. Permission Verification Service

authService.checkUserPermissions = async ({ userID, tenantID }) => {
  if (userTenant?.role === "ADMIN") return { permission: true };
  
  const roleMappings = await fetchUserRolesAndPermissions(userID, tenantID);
  return checkPermissions(extractUserPermissions(roleMappings));
};

---

Usage Examples

Route Protection

// Single permission check
router.post("/tables",
  authMiddleware.checkUserPermissions(["tenant:database:table:create"]),
  controller.createTable
);

// Multiple permissions (ALL)
router.post("/complex-op",
  authMiddleware.checkUserPermissions(["perm1", "perm2"], { requireAll: true }),
  controller.complexOp
);

// Multiple permissions (ANY)
router.get("/reports",
  authMiddleware.checkUserPermissions(["perm3", "perm4"], { requireAll: false }),
  controller.getReports
);

---

Special Cases

1. Admin Privileges

   • Bypass all permission checks
   • Managed through tblUsersTenantsRelationship.role

2. Wildcard Support

   • tenant:database:* matches all database permissions
   • tenant:*:create matches create actions in any tenant resource

3. Tenant Isolation

   • Permissions only valid within assigned tenant context
   • Global permissions apply across all tenants

4. Role Management

   • Requires tenant:role:manage permission
   • Only tenant admins can modify role assignments