Sprint Design Notes: LDAP (OUD vs AD) and SQL Lookup Optimization

Today was a light but productive design session focused on aligning the team on upcoming sprint stories. While the implementation itself is not overly complex, the discussion surfaced two important architectural considerations:

• How we handle large identifier lookups in SQL queries
• How we manage LDAP-based authentication and group access control (OUD vs AD)

This post documents both topics for future reference and knowledge sharing.

---

SQL Lookup Design: Handling Large Identifier Lists

One of the stories requires supporting lookup by identifier list, where the input may contain a comma-separated list of values.

The Problem

Directly passing a large list of identifiers into SQL introduces several risks:

• SQL query length limitations (database dependent)
• Overly large IN (...) clauses
• Performance degradation on large parameter sets

SELECT * FROM orders WHERE identifier IN (...very long list...)

This approach becomes unstable as the input size grows.

Current Approach

The agreed solution is to:

• Split identifier list into batch chunks
• Execute multiple SQL queries in backend
• Aggregate results in application layer

This ensures safe query size and predictable performance without relying on database-specific features.

Better Alternatives (Future Improvements)

1. Temporary Table (Recommended)

SELECT o.*
FROM orders o
JOIN temp_identifiers t ON o.identifier = t.identifier;

Benefits:

• No query length limitation
• Better database optimization
• Cleaner SQL semantics

2. Table-Valued Parameters (SQL Server)

Pass structured arrays instead of string lists to avoid manual parsing.

3. JSON / Array Types (Modern Databases)

Some databases support native JSON or array parameters for cleaner implementation.

Decision for Now

We will proceed with backend batching for now while keeping the temporary table approach as a future improvement.

---

LDAP Authentication: OUD vs Active Directory (AD)

Another topic discussed was role-based access control using LDAP groups, specifically restricting access to users belonging to a specific AD group.

Current Setup

ldap:
  enabled: true
  tokenused: true
  url: ldap://xxx.com:636
  user:
    dn: uid={0},ou=People,ou=CA
    attribute: uid
    role:
      attribute: isMemberOf

• Authentication is currently tied to OUD (Oracle Unified Directory)
• User lookup is based on uid under a fixed DN
• Role mapping uses the isMemberOf attribute

Key Limitation

The current OAuth service does not use a service-account-based LDAP search model.

Instead:

• OUD supports direct bind authentication
• Active Directory typically requires a service account
• AD needs base DN search and group traversal logic

OUD vs Active Directory (Simplified Comparison)

Feature	OUD	Active Directory
Authentication style	Direct bind	Service-account search
Group lookup	Attribute-based (isMemberOf)	Directory traversal required
Configuration complexity	Simple	More complex
Ecosystem	Oracle-based	Microsoft-based

Why AD Group Integration is Difficult

• OAuth service is packaged as a fixed image
• Configuration-only (no source code control)
• LDAP integration assumes OUD-style bind

Therefore, introducing AD-style service account search would require modifying the OAuth service itself.

Final Decision

• Continue using OUD for now
• Defer AD group integration to future phases
• Revisit when OAuth service becomes customizable

---

Key Takeaways

• Large SQL IN clauses should be avoided due to performance and size limits
• Temporary tables or structured parameters are better long-term solutions
• LDAP strategy depends heavily on directory type (OUD vs AD)
• Packaged authentication services significantly constrain architecture decisions

Implementation is easy — integration boundaries are where design matters most.

❤️ Support This Blog

---

If this post helped you, you can support my writing with a small donation. Thank you for reading.

---

Coffee ☕ Thanks 🙏 Extra Support ❤️