Search Results substitute_predicate

Overview

The APPS.FND_DATA_SECURITY package body implements the core runtime engine of Oracle E-Business Suite's data security (also known as "data security" or "row-level security") framework. It is the PL/SQL layer that translates the security rules configured against objects (tables or views) and object instance sets into predicates that restrict which rows a user may see or modify. In Oracle EBS, objects such as FND_RESPONSIBILITY or application tables can be secured against instance sets defined per user, responsibility, or global grant. This package resolves the effective security predicate for a given object and instance set, applies it against the compiled function/menu hierarchy, and caches the results for performance.

In addition to pure predicate generation, the package validates menu and function access by checking the user's granted roles and responsibilities. It is referenced by 51 other database objects, making it one of the most load-bearing packages in the Applications Technology stack for access control. Its status is VALID in the ETRM registration, and it is owned by the APPS schema.

Key Procedures and Functions

The documented interface exposes 18 procedures and functions. Their purposes (parameter lists omitted) are:

• CHECK_FUNCTION — verifies whether a user can access a specific function.
• GET_FUNCTIONS — returns the set of functions available to the current user based on role/responsibility grants.
• GET_MENUS — returns the menu hierarchy authorized for the current user.
• GET_SECURITY_PREDICATE — builds the SQL predicate string that enforces data security for an object/instance set.
• GET_SECURITY_PREDICATE_W_BINDS — the bind-variable variant of the predicate builder, supporting reusable cursors.
• GET_INSTANCES — returns the instance values (e.g., organization IDs) permitted for the user.
• CHECK_INSTANCE_IN_SET — confirms whether a given instance value belongs to a specified instance set.
• GET_ORIG_KEY — resolves the originating key used in grant and predicate lookups.
• CHECK_GLOBAL_OBJECT_TYPE_GRANT — evaluates whether a global (non-user-specific) grant applies for an object type.
• UPGRADE_PREDICATE, UPGRADE_COLUMN_TYPE, UPGRADE_GRANTEE_KEY, SUBSTITUTE_PREDICATE — migration helpers that convert legacy predicate syntax into the current data-security schema.
• TO_INT, TO_DATE — type-conversion utilities for numeric and date instance values.
• CHECK_USER_ROLE — validates role membership via the WF directory.
• GET_FUNCTIONS_BY_PROMPT — returns functions filtered by their prompt text.

Tables Accessed

The package reads and writes through APPS synonyms. Its dependency list includes FND_FORM_FUNCTIONS and FND_COMPILED_MENU_FUNCTIONS (function/menu metadata), FND_GRANTS (grant definitions for users and responsibilities), FND_MENU_ENTRIES_TL (translated menu entries), FND_OBJECTS and FND_OBJECT_INSTANCE_SETS (the security object model), FND_RESPONSIBILITY and FND_RESP_FUNCTIONS (responsibility-to-function mappings), and FND_USER. User and role resolution leverages WF_LOCAL_ROLES, WF_USER_ROLES, and WF_DIRECTORY. Dynamic SQL is executed via DBMS_SQL, and utility access is made to V$INSTANCE and internal PL/SQL tables (PLITBLM, FND_TABLE_OF_NUMBER).

Usage Notes

FND_DATA_SECURITY is most commonly invoked indirectly. Oracle Forms calls it through the standard security event and FND_FUNCTION navigation paths to enforce menu and instance-set restrictions whenever a user opens a form or query. Concurrent programs and OAF pages that support data-secured objects rely on the generated predicate to append WHERE clauses at query time. Custom PL/SQL code may call GET_SECURITY_PREDICATE or GET_SECURITY_PREDICATE_W_BINDS to build secure SQL, and GET_INSTANCES to retrieve authorized instance values. Because the package is referenced by 51 other objects, changes to its behavior should be treated as system-wide; any customization should use the supported public API rather than modifying the package body, which would invalidate the ETRM registration.