Results for “ams_act_access”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The AMS_ACT_ACCESS table resides in the AMS (Marketing) schema and holds access information for all marketing objects. It functions as the central authorization registry within Oracle Marketing, defining which users or resource groups may view, administer, or approve a given marketing entity such as a campaign, list, event header, event offer, or deliverable. Because access control in Oracle Marketing is applied uniformly across object types, a single table with a polymorphic object reference is used rather than per-entity security tables.

From a Data Vault modeling perspective, the mined relationship structure suggests classifying AMS_ACT_ACCESS as a link entity. It resolves a many-to-many relationship between marketing objects (referenced through ACT_ACCESS_TO_OBJECT_ID together with the discriminator ARC_ACT_ACCESS_TO_OBJECT) and the user or role principals (USER_OR_ROLE_ID together with ARC_USER_OR_ROLE_TYPE). The descriptive flags and effective dates carried in the row act as link-level attributes rather than a separate satellite key.

Key Information Stored

The table contains 18 documented columns. The most important of these are:

• ACTIVITY_ACCESS_ID — the surrogate primary key enforced by AMS_ACT_ACCESS_PK. It uniquely identifies each access grant row.
• ACT_ACCESS_TO_OBJECT_ID — the identifier of the secured marketing object. This is the polymorphic foreign key, its meaning qualified by ARC_ACT_ACCESS_TO_OBJECT.
• ARC_ACT_ACCESS_TO_OBJECT — the archetype discriminator indicating which object type the ID refers to (campaign, list, event header, event offer, or deliverable).
• USER_OR_ROLE_ID — the principal granted access, resolved against JTF_RS_GROUPS_B for role/resource-group grants.
• ARC_USER_OR_ROLE_TYPE — the discriminator distinguishing whether the grantee is an individual user or a role.
• ADMIN_FLAG — indicates administrative access rights to the object.
• APPROVER_FLAG — indicates whether the principal may approve the object.
• OWNER_FLAG — marks the principal as owner of the secured object.
• ACTIVE_FROM_DATE and ACTIVE_TO_DATE — the effective date range during which the access grant is valid.
• DELETE_FLAG — soft-delete indicator for logical removal of a grant.
• SECURITY_GROUP_ID — the operating unit / security group context, foreign-keyed to FND_SECURITY_GROUPS and supporting Multi-Org filtering.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the marketing application framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

Two unique indexes are documented. AMS_ACT_ACCESS_U1 covers ACTIVITY_ACCESS_ID, while AMS_ACT_ACCESS_U2 spans ACT_ACCESS_TO_OBJECT_ID, ARC_ACT_ACCESS_TO_OBJECT, USER_OR_ROLE_ID, and ARC_USER_OR_ROLE_TYPE, making this the natural business key that prevents duplicate grants for the same principal on the same object.

Common Use Cases and Queries

Typical usage centers on security auditing and entitlement reporting for marketing objects. A common query retrieves all principals granted access to a specific campaign:

• List all active grants for a campaign: select USER_OR_ROLE_ID, ARC_USER_OR_ROLE_TYPE, ADMIN_FLAG, APPROVER_FLAG from AMS_ACT_ACCESS where ACT_ACCESS_TO_OBJECT_ID = :campaign_id and DELETE_FLAG = 'N'.
• Identify objects a given resource group may administer, joining on USER_OR_ROLE_ID.
• Audit expired entitlements by filtering ACTIVE_TO_DATE against the current date.
• Reconcile ownership by selecting rows where OWNER_FLAG = 'Y'.
• Multi-Org reporting restricted by SECURITY_GROUP_ID.

Related Objects

AMS_ACT_ACCESS participates in foreign-key relationships with several marketing and shared entities:

• AMS_CAMPAIGNS_ALL_B — via ACT_ACCESS_TO_OBJECT_ID; campaign-level access grants.
• AMS_LIST_HEADERS_ALL — via ACT_ACCESS_TO_OBJECT_ID; list access grants.
• AMS_EVENT_HEADERS_ALL_B — via ACT_ACCESS_TO_OBJECT_ID; event header access.
• AMS_EVENT_OFFERS_ALL_B — via ACT_ACCESS_TO_OBJECT_ID; event offer access.
• AMS_DELIVERABLES_ALL_B — via ACT_ACCESS_TO_OBJECT_ID; deliverable access.
• JTF_RS_GROUPS_B — via USER_OR_ROLE_ID; resolves the grantee resource group or role.
• FND_SECURITY_GROUPS — via SECURITY_GROUP_ID; provides Multi-Org security context.