SQL-025: Find newly-added access groups from AD

Detects access-group entries that exist in the AD group mapping for a user type but do not yet have a corresponding row in EXT_USER_ACCESS_GROUP for the given user type ID. Used during AD-group reconciliation to identify groups to add.

Classification

Field	Value
SQL ID	SQL-025
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsUserAccessGroup
Criteria property	Local SQL
Lifecycle method	GetAllNewUserAccessGroups

Parameters

Parameter	Direction	Type	Max length	Description
:UserTypeCode	Input	String	5	User type code to match AD group entries.
:ADGroup	Input	String	100	AD group name to check.
:UserTypeID	Input	Decimal	N/A	EXT_USER_TYPE_SEQ_NUM for LEFT JOIN absence check.
:CultureCode	Input	String	5	Culture code for globalized description.

Tables

Table	Access	Columns used
EXT_USER_AD_GROUP	SELECT	EXT_USER_AD_GROUP_SEQ_NUM, AD_GROUP_NAME, EXT_USER_TYPE_CODE
EXT_USER_ACCESS_GROUP_AD	LEFT JOIN	EXT_USER_AD_GROUP_SEQ_NUM, EXT_USER_ACCESS_GROUP_CODE
EXT_USER_ACCESS_GROUP	LEFT JOIN	EXT_USER_ACCESS_GROUP_CODE, EXT_USER_TYPE_SEQ_NUM, EXT_USER_ACCESS_GROUP_SEQ_NUM
EXT_USER_ACCESS_GROUP_GCODE	LEFT OUTER JOIN	CODE, CULTURE_CODE

Sample SQL

SELECT 'Admin' AS AccesType, g.ext_user_access_group_code AS AccessGroupCode
FROM ext_user_ad_group ad
LEFT JOIN ext_user_access_group_ad g
    ON AD.EXT_USER_AD_GROUP_SEQ_NUM = G.EXT_USER_AD_GROUP_SEQ_NUM
LEFT JOIN ext_user_access_group ag
    ON AG.EXT_USER_ACCESS_GROUP_CODE = G.EXT_USER_ACCESS_GROUP_CODE
    AND AG.EXT_USER_TYPE_SEQ_NUM = :UserTypeID
LEFT OUTER JOIN ext_user_access_group_gcode aggc
    ON (g.ext_user_access_group_code = aggc.code
        AND upper(AGGC.CULTURE_CODE) = :CultureCode)
WHERE upper(ext_user_type_code) = :UserTypeCode
  AND upper(ad_group_name) = :ADGroup
  AND ag.ext_user_access_group_seq_num IS NULL

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