---
title: Menu authorization data lookup
summary: Records which security groups and users may see each menu item and resolves the authorized/favorited menu-item projection for a user.
status: draft
owner: SAIF
last_reviewed: 2026-07-30
library: SAIF.Business.Menu
reference_type: package
operation_id: OP-MNU-04
trigger: Caller requests the authorized menu-item projection for a user (or all users), or maintains a security-group-to-item or user-to-group mapping
---

# Menu authorization data lookup

Menu authorization data lookup is the read-heavy core of the library: it records the many-to-many mapping between menu items and security groups (`clsMenuSecurity`) and between users and security groups (`clsMenuSecurityUser`), then combines both with `MENU_ITEM`, `MENU_PAGE`, `BUSONLINE_APPS_CODE`, `MENU_CATEGORY_CODE`, and `MENU_FAVORITE` in a three-part UNION query (`clsMenuAuthorizedItemCollection`) that returns every menu item a given user is authorized to see, flagged as secured, unsecured, or favorited. A caller with no user context gets a two-part UNION covering all secured and unsecured items without per-user favorites.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-MNU-04 |
| **Trigger** | Caller requests `clsMenuAuthorizedItemCollection` with or without a `MenuUser`; caller retrieves/maintains `clsMenuSecurity`/`clsMenuSecurityCollection` or `clsMenuSecurityUser`/`clsMenuSecurityUserCollection` |
| **Inputs** | `MenuUser` (authorized-item lookup); `MenuItemSeqNum`, `SecurityGroup` (security-group mapping); `SecurityGroupTxt`, `MenuUser`, `MenuUserID` (user-to-group mapping) |
| **Outputs** | `clsMenuAuthorizedItemCollection` populated with the caller's authorized/favorited items (or all items); persisted `clsMenuSecurity`/`clsMenuSecurityUser` mapping rows; error messages on DML contention |
| **Primary components** | `clsMenuAuthorizedItemCollection`, `clsMenuAuthorizedItem`, `clsMenuSecurity`, `clsMenuSecurityCollection`, `clsMenuSecurityUser`, `clsMenuSecurityUserCollection` |
| **Entry point** | `Business\SAIF.Business.Menu\clsMenuAuthorizedItemCollection.vb` |

## Steps

1. **Request the authorized-item projection.** The caller invokes `clsMenuAuthorizedItemCollection.GetMenuAuthorizedItems(dataportal, menuUser)` for a specific user, or `GetAllMenuAuthorizedItems(dataportal)` for the unfiltered projection.
2. **Dispatch on user context.** `BeforeRetrieve` checks `MyCriteria.MenuUser > String.Empty`.
   - If a `MenuUser` is present: bind `:MenuUser` and execute the three-part UNION `RetrieveSQL` ([SQL-016](../sql/SQL-016-saif-business-menu.md)).
   - If no `MenuUser` is present: execute the two-part UNION `RetrieveAllSQL` ([SQL-015](../sql/SQL-015-saif-business-menu.md)), which takes no bind parameter.
3. **Per-user UNION, part 1 — secured items.** Joins `MENU_ITEM`, `MENU_SECURITY`, `MENU_SECURITY_USER`, `MENU_PAGE`, `BUSONLINE_APPS_CODE`, and `MENU_CATEGORY_CODE`, matching the item's `MENU_SECURITY` record to the user's `MENU_SECURITY_USER` record on security-group text ([BR-MNU-01](../business-rules.md#br-mnu-01-menu-item-visibility-determined-by-security-group-or-user-mapping)).
   - The join predicate compares `upper(USR.SECURITY_GROUP_TXT) = upper(SEC.SECURITY_GROUP_TXT)`, so group matching is case-insensitive ([BR-MNU-04](../business-rules.md#br-mnu-04-case-insensitive-security-group-matching)).
   - Each returned row is flagged `UnsecuredFlag = 'N'`, `FavoriteFlag = 'N'`.
4. **Per-user UNION, part 2 — unsecured items.** Selects every item with `NOT EXISTS (SELECT 1 FROM menu_security SEC WHERE SEC.MENU_ITEM_SEQ_NUM = ITM.MENU_ITEM_SEQ_NUM)` — items with no `MENU_SECURITY` row at all are visible to every user regardless of group ([BR-MNU-01](../business-rules.md#br-mnu-01-menu-item-visibility-determined-by-security-group-or-user-mapping)).
   - Each returned row is flagged `UnsecuredFlag = 'Y'`.
5. **Per-user UNION, part 3 — active favorites.** Joins `MENU_FAVORITE` on `MENU_ITEM_SEQ_NUM`, filtering `FAV.MENU_USER = :MenuUser AND FAV.ITEM_INACTIVE_DATE IS NULL` — only the requesting user's own non-deactivated favorites are folded in, flagged `FavoriteFlag = 'Y'` ([BR-MNU-05](../business-rules.md#br-mnu-05-favorite-active-status-enforcement); see [Favorite menu management](favorite-menu-management.md)).
6. **All-user UNION (no `MenuUser`).** Runs the same secured/unsecured pattern as steps 3–4 but without a `:MenuUser` bind or a favorites leg — the secured leg returns `USR.MENU_USER AS MenuUser` for every user/item pairing rather than filtering to one user, and the unsecured leg tags rows `MenuUser = 'UNSECURED'`.
7. **Load the results.** `AfterRetrieve` reads every row of the UNION result set and loads one `clsMenuAuthorizedItem` per row, including the resolved `ApplicationPath`, `Category`, `PageName`, `PageLink`, and every toolbar flag/tooltip needed for menu rendering without a further join by the presentation layer.
8. **In-memory lookup helpers.** Once loaded, the collection exposes `Item(appCode, category, pageLink)` and `ItemByCategory(category, pageLink)` indexers that match on trimmed, upper-cased page-link text — optionally normalized via `LinkOnly` to strip query-string parameters — letting the presentation layer resolve a specific authorized item without a further database round trip.*
9. **Maintain the security-group-to-item mapping.** `clsMenuSecurity.NewMenuSecurity` obtains `MENU_SECURITY_SEQ.NEXTVAL` ([SQL-004](../sql/SQL-004-saif-business-menu.md), [DUAL](../database-tables.md#dual)); `Save()` executes `InsertSQL` ([SQL-010](../sql/SQL-010-saif-business-menu.md), [MENU_SECURITY](../database-tables.md#menu-security)) with the row-count contention check ([BR-MNU-02](../business-rules.md#br-mnu-02-dml-row-count-enforcement)). `clsMenuSecurityCollection` retrieves by sequence key ([SQL-026](../sql/SQL-026-saif-business-menu.md)), by menu item ([SQL-027](../sql/SQL-027-saif-business-menu.md)), or unfiltered ([SQL-028](../sql/SQL-028-saif-business-menu.md)).
10. **Maintain the user-to-group mapping.** `clsMenuSecurityUser.Save()` executes `InsertSQL` ([SQL-011](../sql/SQL-011-saif-business-menu.md), [MENU_SECURITY_USER](../database-tables.md#menu-security-user)) — the sequence for this table is generated inline in the insert statement itself (`MENU_SECURITY_USER_SEQ.NEXTVAL`) rather than via a separate scalar sequence call, unlike the other four tables in this library.* `clsMenuSecurityUserCollection.BeforeRetrieve` branches on whether `MyCriteria.MenuUser` is populated:
    - **Likely source defect:** the branch is inverted relative to every other collection in this library — when `MenuUser` is non-empty, it selects `retrieveForUserSQL` ([SQL-029](../sql/SQL-029-saif-business-menu.md)) but does **not** call `AddParm("MenuUser", ...)`, even though `retrieveForUserSQL` appends `WHERE MENU_USER = :MenuUser`; when `MenuUser` is empty, it selects the unfiltered `RetrieveSQL` ([SQL-030](../sql/SQL-030-saif-business-menu.md)) but **does** call `AddParm("MenuUser", ...)`, even though that SQL text has no bind variable to receive it.** This would cause a missing-bind-variable error on the filtered path and a harmless unused parameter on the unfiltered path, rather than the intended per-user filtering behavior.

## Process flow

The following diagram visualizes the primary authorized-menu-item retrieval flow.

```mermaid
flowchart TD
	Start([Start: request authorized menu items]) --> CheckUser{MenuUser provided?}
	CheckUser -->|Yes| SQL016[Execute per-user 3-part<br>UNION query with favorites]
	CheckUser -->|No| SQL015[Execute all-user 2-part<br>UNION query without favorites]
	SQL016 --> Load[Load one clsMenuAuthorizedItem per row]
	SQL015 --> Load
	Load --> Result([Return authorized item collection])
	Result --> Presentation[Presentation layer enforces<br>final visibility/authorization decision]
```

## Failure and edge-case behavior

- The security decision itself — whether to actually hide or show a menu item to the end user — is made by the consuming presentation layer using the `UnsecuredFlag`/`FavoriteFlag` data this library returns; this library only assembles and returns the candidate data set ([BR-MNU-01](../business-rules.md#br-mnu-01-menu-item-visibility-determined-by-security-group-or-user-mapping)).
- Every insert on `clsMenuSecurity`/`clsMenuSecurityUser` enforces the rows-affected-equals-1 contention check ([BR-MNU-02](../business-rules.md#br-mnu-02-dml-row-count-enforcement)).
- `clsMenuSecurityUserCollection.BeforeRetrieve` has an inverted parameter-binding branch (see step 10) that is a likely source defect; querying security users scoped to a specific `MenuUser` may fail or silently return the wrong result set until corrected.**
- Items with no `MENU_SECURITY` row are unsecured by default and visible to all users — there is no "deny by default" posture in this data model; an administrator who forgets to add a `MENU_SECURITY` row for a new sensitive item leaves it visible to everyone ([BR-MNU-01](../business-rules.md#br-mnu-01-menu-item-visibility-determined-by-security-group-or-user-mapping)).
- The all-user (`GetAllMenuAuthorizedItems`) projection has no favorites leg at all, so favorite bookmarks never appear when no `MenuUser` is supplied.
- Security-group matching is case-insensitive via `UPPER()`, so `SecurityGroupTxt` values that differ only in case are treated as the same group across `MENU_SECURITY` and `MENU_SECURITY_USER` ([BR-MNU-04](../business-rules.md#br-mnu-04-case-insensitive-security-group-matching)).

## Related documentation
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts. Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Sources
- `Business\SAIF.Business.Menu\clsMenuAuthorizedItem.vb`
- `Business\SAIF.Business.Menu\clsMenuAuthorizedItemCollection.vb`
- `Business\SAIF.Business.Menu\clsMenuSecurity.vb`
- `Business\SAIF.Business.Menu\clsMenuSecurityCollection.vb`
- `Business\SAIF.Business.Menu\clsMenuSecurityUser.vb`
- `Business\SAIF.Business.Menu\clsMenuSecurityUserCollection.vb`