Search Results edw_sec_lvl_info_t

Overview

EDW_SEC_LVL_INFO_T is a reference table owned by the BIS schema within Oracle E-Business Suite, delivered as part of the Applications BIS (Business Intelligence System) product family. Its documented status is VALID in both Oracle EBS 12.1.1 and 12.2.2. The object serves as a security-level information repository, holding denormalized descriptors for the security levels that the Enterprise Data Warehouse (EDW) layer uses when applying row-level data access restrictions across BI Publisher reports, discoverer workbooks, and other BIS-managed analytical content.

The table stores one record per security dimension level and supplies the human-readable names, long names, and the physical column identifiers that the security framework uses to translate a numeric level identifier into a filterable column reference at runtime. In this capacity it functions as the metadatastore that bridges abstract level IDs (from MSD_LEVELS) to concrete column names used in generated SQL.

From a heuristic Data Vault modeling perspective, the table is classified as standalone, meaning it exhibits no parent-child dependencies other than the outbound reference to MSD_LEVELS and is not dependent on another hub for its identity. In Data Vault terms it most closely resembles a reference satellite attached to a level hub, since it carries descriptive attributes (names and column mappings) keyed by LEVEL_ID rather than transactional or relationship data.

Key Information Stored

The documented physical schema for 12.1.1 contains five columns. The most important are:

• DIM_ID – identifies the dimension to which the security level belongs, linking the level definition to a specific analytic dimension in the EDW model.
• LEVEL_ID – the numeric business key of the security level. This is the foreign key pointing to MSD_LEVELS.LEVEL_ID and is the primary join column for downstream consumers.
• LEVEL_NAME – the short display name of the level, typically used as the label in security setup forms and report parameter pickers.
• LEVEL_LONG_NAME – the descriptive long name presented in administrative and audit-facing screens.
• STAR_LEVEL_NAME_COL_NAME – the physical column name in the star-schema fact or dimension table that holds the value compared against the user's authorized level at query time. This is the operative mapping that the security engine reads to inject a WHERE predicate into generated SQL.

The metadata provided does not document a separate surrogate primary key or unique index; LEVEL_ID, constrained by the foreign key to MSD_LEVELS, is the effective business-key candidate for joining and lookup purposes.

Common Use Cases and Queries

The primary use case is resolving a level identifier to the column name required for data-security filtering. A typical query joins the table to MSD_LEVELS to retrieve the display name and column mapping for a given level:

• SELECT s.level_id, s.level_name, s.star_level_name_col_name FROM bis.edw_sec_lvl_info_t s, msd_levels m WHERE s.level_id = m.level_id.
• Reporting scenarios list all levels for a dimension using DIM_ID to populate a security assignment LOV.
• Diagnostic queries identify levels whose STAR_LEVEL_NAME_COL_NAME does not match an actual column in the target fact table, a common cause of missing row-level security in BI reports.

Related Objects

The documented relationship data identifies MSD_LEVELS as the sole referenced parent via EDW_SEC_LVL_INFO_T.LEVEL_ID → MSD_LEVELS.LEVEL_ID. In practice, the BIS security framework consumes this table alongside MSD_LEVELS to construct row-level predicates. Administrators should treat MSD_LEVELS as the master level definition table and EDW_SEC_LVL_INFO_T as its descriptive extension for EDW column mapping. Any change to level definitions in MSD_LEVELS should be validated against the corresponding row here to ensure the STAR_LEVEL_NAME_COL_NAME mapping remains accurate.