SQL-026: Find removed access groups from AD¶

Detects EXT_USER_ACCESS_GROUP rows that are AD-backed but whose AD group names are no longer in the caller-supplied set. Used during AD-group reconciliation to identify groups to remove. The NOT IN list is constructed at runtime from the caller's current AD group membership.

Classification¶

Field	Value
SQL ID	SQL-026
Operation type	Read
Execution method	Inline SQL + dynamic NOT IN list
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsUserAccessGroup`
Criteria property	Local SQL
Lifecycle method	`GetAllRemovedUserAccessGroups`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:UserTypeID`	Input	Decimal	N/A	`EXT_USER_TYPE_SEQ_NUM` to scope the query.
Runtime literal list	N/A	String	N/A	`UPPER('group1'), UPPER('group2'), ...` constructed from the caller's current AD groups.*

Tables¶

Table	Access	Columns used
EXT_USER	SELECT	`EXT_USER_SEQ_NUM`
EXT_USER_TYPE	JOIN	`EXT_USER_SEQ_NUM`, `EXT_USER_TYPE_SEQ_NUM`
EXT_USER_ACCESS_GROUP	JOIN	`EXT_USER_TYPE_SEQ_NUM`, `EXT_USER_ACCESS_GROUP_CODE`, `EXT_USER_ACCESS_GROUP_SEQ_NUM`
EXT_USER_ACCESS_GROUP_AD	JOIN	`EXT_USER_TYPE_CODE`, `EXT_USER_ACCESS_GROUP_CODE`, `EXT_USER_AD_GROUP_SEQ_NUM`
EXT_USER_AD_GROUP	JOIN	`EXT_USER_AD_GROUP_SEQ_NUM`, `AD_GROUP_NAME`

Sample SQL¶

SELECT ag.ext_user_access_group_seq_num AS AccessGroupID
FROM ext_user u
JOIN ext_user_type t
    ON t.ext_user_seq_num = u.ext_user_seq_num
JOIN ext_user_access_group ag
    ON ag.ext_user_type_seq_num = t.ext_user_type_seq_num
JOIN ext_user_access_group_ad agad
    ON agad.ext_user_type_code = t.ext_user_type_code
    AND agad.ext_user_access_group_code = ag.ext_user_access_group_code
JOIN ext_user_ad_group ad
    ON ad.ext_user_ad_group_seq_num = agad.ext_user_ad_group_seq_num
    AND upper(ad.ad_group_name) NOT IN (<runtime upper('group') list>)
WHERE t.ext_user_type_seq_num = :UserTypeID

Operations and processes¶

User access group management

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.External.Users\clsUserAccessGroup.vb