Skip to content

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.
  3. If a MenuUser is present: bind :MenuUser and execute the three-part UNION RetrieveSQL (SQL-016).
  4. If no MenuUser is present: execute the two-part UNION RetrieveAllSQL (SQL-015), which takes no bind parameter.
  5. 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).
  6. The join predicate compares upper(USR.SECURITY_GROUP_TXT) = upper(SEC.SECURITY_GROUP_TXT), so group matching is case-insensitive (BR-MNU-04).
  7. Each returned row is flagged UnsecuredFlag = 'N', FavoriteFlag = 'N'.
  8. 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).
  9. Each returned row is flagged UnsecuredFlag = 'Y'.
  10. 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; see Favorite menu management).
  11. 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'.
  12. 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.
  13. 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.*
  14. Maintain the security-group-to-item mapping. clsMenuSecurity.NewMenuSecurity obtains MENU_SECURITY_SEQ.NEXTVAL (SQL-004, DUAL); Save() executes InsertSQL (SQL-010, MENU_SECURITY) with the row-count contention check (BR-MNU-02). clsMenuSecurityCollection retrieves by sequence key (SQL-026), by menu item (SQL-027), or unfiltered (SQL-028).
  15. Maintain the user-to-group mapping. clsMenuSecurityUser.Save() executes InsertSQL (SQL-011, 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) 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) 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.

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).
  • Every insert on clsMenuSecurity/clsMenuSecurityUser enforces the rows-affected-equals-1 contention check (BR-MNU-02).
  • 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).
  • 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).

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