Search Results local_printer

Overview

APPLSYS.FND_USER is the Oracle E-Business Suite foundation table that stores information about application users. Each row represents a single application user account and contains the credentials and profile details Oracle Application Object Library requires to authenticate a sign-on and establish a session. The table holds the username typed at the Oracle Applications sign-on screen, the encrypted password, password lifecycle attributes such as when the password was last changed and how many accesses or days remain before expiry, and validity dates that determine when a username may be used. Auxiliary columns record the last successful logon, a description, and links to the employee, customer, supplier, and person party records that the account represents.

FND_USER is central to identity and access management in both 12.1.1 and 12.2.2. It underpins sign-on validation, responsibility and menu assignment through FND_USER_RESP_GROUPS, and integration with Oracle User Management (UMX). Its ETRM design classification is a hub, mined heuristically from the foreign-key structure; that classification is a modeling suggestion rather than a physical constraint. It is owned by APPLSYS and resides in the APPS_TS_SEED tablespace, the seed-data tablespace used for setup and reference information.

Key Information Stored

The table contains 27 documented columns. The most significant are:

• USER_ID — the surrogate primary key (FND_USER_PK), a NUMBER(15) that uniquely identifies each user. It is the join key referenced throughout the applications schema.
• USER_NAME — VARCHAR2(100), the application username entered at sign-on. This is the primary business-key candidate and is enforced unique by index FND_USER_U2 (the object the user searched for). FND_USER_U1 enforces uniqueness of USER_ID.
• ENCRYPTED_USER_PASSWORD and ENCRYPTED_FOUNDATION_PASSWORD — encrypted credentials used for application sign-on and for the foundation database connection respectively.
• START_DATE and END_DATE — the effective validity window for the username. Oracle Application Object Library does not consider a user active outside these dates.
• LAST_LOGON_DATE, PASSWORD_DATE, PASSWORD_ACCESSES_LEFT, PASSWORD_LIFESPAN_ACCESSES, and PASSWORD_LIFESPAN_DAYS — password policy and audit attributes.
• EMPLOYEE_ID, CUSTOMER_ID, SUPPLIER_ID, and PERSON_PARTY_ID — consumer of the account as an HR employee, customer contact, supplier contact, or trading partner party.
• EMAIL_ADDRESS, USER_GUID, and DESCRIPTION — notification address, the globally unique identifier used in multi-instance and SSO scenarios, and a free-text description.

The documented schema notes that USER_PASSWORD and LOCAL_PRINTER are not used by Oracle Application Object Library; the encrypted password columns should be treated as opaque.

Common Use Cases and Queries

FND_USER supports user administration, sign-on auditing, and responsibility reporting. Typical queries include:

• Look up a user by the unique business key: SELECT user_id FROM fnd_user WHERE user_name = :username; — this query drives FND_USER_U2.
• List all currently active users: SELECT user_name, start_date, end_date FROM fnd_user WHERE TRUNC(SYSDATE) BETWEEN NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE);
• Audit password and logon hygiene, for example identifying accounts that have not signed on recently using LAST_LOGON_DATE and PASSWORD_DATE.
• Report on the DBA look-up used by the System Administrator responsibility in the Users form, filtering on START_DATE, END_DATE, and DESCRIPTION.
• Find accounts created by an administrator using CREATED_BY and CREATION_DATE, remembering that these columns themselves reference FND_USER rows.
• Driver queries joining to responsibility assignments in FND_USER_RESP_GROUPS and to FND_RESPONSIBILITY to produce access certification or security review reports.

Because USER_GUID is populated for every account in recent releases, it is a reliable key for cross-instance and SSO-centric reconciliation.

Related Objects

FND_USER is referenced by a vast number of tables, reflecting its role as the identity hub. The most significant relationships include:

• FND_LOGINS and FND_UNSUCCESSFUL_LOGINS — sign-on success and failure history, joined on USER_ID, essential for security auditing.
• FND_USER_DESKTOP_OBJECTS — per-user desktop (Favorites) configuration, joined on USER_ID.
• FND_USER_PREFERENCES — user-level profile preferences, joined on USER_NAME rather than USER_ID.
• FND_CONCURRENT_REQUESTS — concurrent programs requested by a user, joined via REQUESTED_BY.
• FND_PROFILE_OPTION_VALUES — user-level profile option values, joined via LEVEL_VALUE.
• FND_REQUEST_SETS and related request-set tables — ownership and audit of reporting request sets, via OWNER and CREATED_BY.
• UMX_REG_REQUESTS and JTF_UM_APPROVERS — user provisioning and approval workflow tables.
• HZ_PARTIES — the trading-community party behind CUSTOMER_ID and PERSON_PARTY_ID; PSB_EMPLOYEES backs EMPLOYEE_ID.
• AP_WEB_PROXY_ASSIGNMENTS and SO_ORDER_APPROVALS — examples of the many transactional tables that reference users as approvers or proxies.

For modifications, the supported interfaces are the Users form, the FND_USER_PKG PL/SQL API, and the UMX provisioning framework, rather than direct DML against APPLSYS.FND_USER.