Search Results edw_sec_dim_access_n1

Overview

BIS.EDW_SEC_DIM_ACCESS is a security grants table within the Oracle E-Business Suite Business Intelligence (BIS) schema. It stores row-level dimension access definitions that map a responsibility (RESP_ID) to a specific dimension (DIM_ID) and hierarchy level (LEVEL_ID), together with the set of access values permitted for that combination. In effect, the table governs which dimensional members a given responsibility may view or query within the Enterprise Data Warehouse reporting layer.

The object is documented as VALID and owned by the BIS schema, with a physical table name of EDW_SEC_DIM_ACCESS and an FND Design Data entry of the same name. It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. The object is referenced under the APPS schema as APPS.EDW_SEC_DIM_ACCESS, which is the form typically addressed from application code. Based on the mined foreign-key structure, the table is classified heuristically as standalone in Data Vault terms — it presents no explicit parent hub reference from its own columns beyond the documented join to MSD_LEVELS, so it behaves more like a self-contained security mapping construct than a classic hub, link, or satellite. This classification is a modeling suggestion only and does not imply formal Data Vault implementation.

Key Information Stored

The table contains 11 columns in total. The most significant are described below.

• RESP_ID (NUMBER(15)) — the responsibility identifier granted the access. This is the leading column of the unique index and the primary correlate to the application responsibility definition.
• DIM_ID (NUMBER) — identifies the dimension governed by the grant.
• LEVEL_ID (NUMBER) — identifies the hierarchy level within the dimension. This column carries the documented foreign-key relationship to MSD_LEVELS.
• ACCESS_VALUE (VARCHAR2(2000)) — the encoded value or value list defining the actual access scope granted for the responsibility/dimension/level combination.
• DIM_SHORT_NAME (VARCHAR2(255)) — the short name of the dimension, denormalized for reporting convenience.
• LEVEL_SHORT_NAME (VARCHAR2(255)) — the short name of the hierarchy level, similarly denormalized.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — the Standard Who audit columns recording row creation and modification metadata, with LAST_UPDATED_BY and CREATED_BY referencing FND_USER.USER_ID.

No dedicated surrogate primary key column is documented. The business-key candidate is the unique index EDW_SEC_DIM_ACCESS_N1, defined on the composite of RESP_ID, DIM_ID, LEVEL_ID, and ACCESS_VALUE. This four-column uniqueness constraint is the recommended logical identifier for the row.

Common Use Cases and Queries

The primary use case is enforcing and reporting dimension-level security for BI responsibilities. Administrators query grants to audit which responsibilities can see which dimensional members, and reporting logic joins this table to filter results by the caller's active responsibility.

A typical retrieval pattern selects all grants for a responsibility:

SELECT RESP_ID, DIM_ID, LEVEL_ID, ACCESS_VALUE
FROM   BIS.EDW_SEC_DIM_ACCESS
WHERE  RESP_ID = :p_resp_id;

A security-enforcement join compares the grant to the responsibility currently in session, often joining through FND_USER_RESP_GROUPS or FND_RESPONSIBILITY to resolve the active RESP_ID. Because ACCESS_VALUE is a VARCHAR2(2000) field, filtering frequently uses INSTR or LIKE against a delimited value string, and administrators should be aware that this prevents straightforward index range scans beyond the leading columns of EDW_SEC_DIM_ACCESS_N1. Reporting queries commonly join LEVEL_ID to MSD_LEVELS and rely on the denormalized DIM_SHORT_NAME and LEVEL_SHORT_NAME columns to avoid extra lookups. Auditing use cases filter on the Standard Who columns to trace recent access-grant changes by user and date.

Related Objects

The documented dependency information is limited, so the following relationships derive from the FK structure and standard BIS/EDW modeling patterns.

• MSD_LEVELS — joined on EDW_SEC_DIM_ACCESS.LEVEL_ID = MSD_LEVELS.LEVEL_ID; the only explicit foreign-key relationship documented for this table.
• APPS.EDW_SEC_DIM_ACCESS — the APPS-schema synonym or view that references this BIS table and is the typical access point from application code.
• FND_USER — referenced indirectly through LAST_UPDATED_BY and CREATED_BY (foreign keys to FND_USER.USER_ID).
• FND_LOGINS — referenced through LAST_UPDATE_LOGIN (foreign key to FND_LOGINS.LOGIN_ID).
• FND_RESPONSIBILITY — the responsibility definition resolved via RESP_ID, used to translate grants into application responsibility context.

Because the metadata records no outbound references other than the MSD_LEVELS join, integrators should treat this table as a leaf security construct and validate any additional relationships against the actual instance.