SQL-MENU-035: Retrieve menu security users for user¶

Retrieve security groups for a user.

Classification¶

Field	Value
SQL ID	SQL-MENU-035
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsMenuSecurityUserCollection`
Criteria property	`retrieveForUserSQL`
Lifecycle method	`BeforeRetrieve`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:MenuUser`	Input	String	30	Menu user/login. SQL-declared in `retrieveForUserSQL`, but not code-bound on this path.

Tables¶

Table	Access	Columns used
MENU_SECURITY_USER	SELECT	`MENU_SECURITY_USER_SEQ_NUM`, `MENU_USER`, `SECURITY_GROUP_TXT`, `MENU_USER_EMPLID`

Sample SQL¶

SELECT
  MENU_SECURITY_USER.MENU_SECURITY_USER_SEQ_NUM as MenuSecurityUserSeq,
  MENU_SECURITY_USER.SECURITY_GROUP_TXT as SecurityGroupTxt,
  MENU_SECURITY_USER.MENU_USER as MenuUser,
  MENU_SECURITY_USER.MENU_USER_ID as MenuUserID
FROM MENU_SECURITY_USER
WHERE MENU_USER = :MenuUser

Source-defect note¶

clsMenuSecurityUserCollection.BeforeRetrieve branches on MyCriteria.MenuUser: when it is non-empty it sets DataRequest.SQLStatement = MyCriteria.retrieveForUserSQL — the statement shown above, which contains the :MenuUser bind token — but it does not call DataRequest.AddParm with the MenuUser value on that branch. The AddParm call supplying the MenuUser value only executes in the Else branch, which uses the unrelated RetrieveSQL statement instead. This is a confirmed source-level defect in clsMenuSecurityUserCollection.vb: the user-scoped retrieval path executes SQL containing an unbound :MenuUser bind variable, which will raise a missing-bind-variable error (e.g., Oracle ORA-01008) at execution time rather than returning the intended per-user security-group rows.

Operations and processes¶

Maintain reference data

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources¶

SAIF.Business.Menu\clsMenuSecurityUserCollection.vb