Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-itshop-entitlement-link-tables-db-evidence.md

> Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-itshop-entitlement-link-tables-db-evidence.md

Sandbox DB Evidence — IT Shop entitlement link tables + flags (2026-04-27)

Method

• Sandbox DB: OneIM on im.sandbox.local
• Tooling: scripts/sandbox/Invoke-SandboxSql.ps1 (SELECT-only)

Evidence

1) ITShopOrg + ITShopOrgHas* are views filtering to UID_OrgRoot='QER-V-ITShopOrg'

Query (object types)

SELECT o.name, o.type_desc
FROM sys.objects o
WHERE o.name IN ('ITShopOrg','ITShopOrgHasADSGroup','ITShopOrgHasESet','AccProductInITShopOrg')
ORDER BY o.name;

Result

Query (view definitions; excerpt)

SELECT v.name AS view_name, m.definition
FROM sys.views v
JOIN sys.sql_modules m ON m.object_id=v.object_id
WHERE v.name IN ('ITShopOrg','ITShopOrgHasADSGroup','ITShopOrgHasESet','AccProductInITShopOrg')
ORDER BY v.name;

Result (key fragments)

• ITShopOrg: from BaseTree where UID_OrgRoot = 'QER-V-ITShopOrg'
• ITShopOrgHasADSGroup: from BaseTreeHasADSGroup where exists (...) BaseTree.UID_OrgRoot = 'QER-V-ITShopOrg'
• ITShopOrgHasESet: from BaseTreeHasESet where exists (...) BaseTree.UID_OrgRoot = 'QER-V-ITShopOrg'
• AccProductInITShopOrg: selects from AccProductInBaseTree filtered to QER-V-ITShopOrg (note: AccProductInBaseTree is empty in this sandbox; see prior evidence).

2) IT Shop “placement” is table-backed via BaseTreeHas<EntitlementType>

Query (list the tables)

SELECT name
FROM sys.tables
WHERE name LIKE 'BaseTreeHas%'
ORDER BY name;

Result (sandbox; excerpt)

• BaseTreeHasADSGroup
• BaseTreeHasESet
• BaseTreeHasLDAPGroup
• BaseTreeHasQERResource
• BaseTreeHasQERReuse
• BaseTreeHasTSBAccountDef
• BaseTreeHasUNSGroupB*
• plus other BaseTreeHas* variants

3) Many entitlement tables have the “IT Shop flags” IsForITShop + IsITShopOnly

Query

SELECT
  t.name AS table_name,
  c.name AS column_name
FROM sys.columns c
JOIN sys.tables t ON t.object_id=c.object_id
WHERE c.name IN ('IsForITShop','IsITShopOnly','IsToHideFromITShop')
ORDER BY t.name, c.name;

Result (sandbox; excerpt)

Interpretation (DB shape): these flags are entitlement-local, and the actual “in the IT Shop structure” relationship is modeled separately via BaseTreeHas<type> (and exposed via ITShopOrgHas<type> views).

4) Current sandbox state: Reuse objects are placed; ADS groups and system roles are not (yet)

Query (counts in IT Shop subtree) — count link rows whose UID_Org points into the IT Shop subtree.

SELECT 'BaseTreeHasADSGroup' AS TableName, COUNT(*) AS Cnt
FROM BaseTreeHasADSGroup h
WHERE EXISTS (SELECT 1 FROM BaseTree o WHERE o.UID_Org = h.UID_Org AND o.UID_OrgRoot='QER-V-ITShopOrg')
UNION ALL
SELECT 'BaseTreeHasESet' AS TableName, COUNT(*) AS Cnt
FROM BaseTreeHasESet h
WHERE EXISTS (SELECT 1 FROM BaseTree o WHERE o.UID_Org = h.UID_Org AND o.UID_OrgRoot='QER-V-ITShopOrg')
UNION ALL
SELECT 'BaseTreeHasQERReuse' AS TableName, COUNT(*) AS Cnt
FROM BaseTreeHasQERReuse h
WHERE EXISTS (SELECT 1 FROM BaseTree o WHERE o.UID_Org = h.UID_Org AND o.UID_OrgRoot='QER-V-ITShopOrg');

Result