Audience: Oracle EBS DBAs, developers, and consultants

Introduction

Almost every Oracle EBS site eventually needs a read-only database account — for ad-hoc analysis, SQL Developer access, BI tools, integrations, or simply giving developers a safe way to query production. Most DBAs know how to create such an account, but in our experience the majority do it incompletely, leaving out one small but very important piece: a logon trigger that automatically sets the current schema to APPS.

The omission seems harmless. It is not. It quietly degrades query quality across the entire team that uses the account, and on EBS 12.2.x it can return silently incorrect, duplicated results due to edition-based redefinition (EBR).

1 The script

create user apps_query identified by apps_query;
create or replace trigger apps.apps_query_logon_trg
after logon on apps_query.schema
begin
  execute immediate 'alter session set current_schema=apps';
end;
/
grant connect to apps_query;
grant select any table to apps_query;
grant select any dictionary to apps_query;
grant select_catalog_role to apps_query;
grant select on sys.user$ to apps_query;
begin
  for c in (
    select null
    from dba_objects do
    where do.owner='APPS' and do.object_name='XXEN_UTIL' and do.object_type='PACKAGE'
  ) loop
    execute immediate 'grant execute on apps.xxen_util to apps_query';
  end loop;
end;
/

Run as SYS (or any user able to create users and a trigger in the APPS schema). Change the password if required.

What it does:

  • Creates the APPS_QUERY user
  • Creates an after-logon trigger in the APPS schema that runs alter session set current_schema=apps automatically every time APPS_QUERY logs in
  • Grants the minimum privileges needed to read data and dictionary metadata
  • If the Enginatics utility package XXEN_UTIL is installed, grants execute on it (used by many Blitz Report SQLs for things like flexfield and lookup translations)

2 Why the logon trigger matters

EBS keeps almost all its tables in product schemas — INV, GL, AP, HR, PO, and so on. The APPS schema does not own those tables; it owns synonyms that point to them, plus all the views, packages, and code. Code in EBS is written against the APPS synonyms, never against the underlying schema-qualified tables.

When a user connects without current_schema=apps, every unqualified table reference resolves against that user’s own (empty) schema and fails with ORA-00942: table or view does not exist. Faced with this, developers commonly do one of two things:

  1. Prefix every table with its owning schema, e.g. inv.mtl_system_items_b or hr.per_all_people_f. This requires them to know which schema each table lives in — extra friction, easy to get wrong, and noisy to read.
  2. Prefix every table with apps., e.g. apps.mtl_system_items_b. This goes through the synonym layer correctly, but the prefix is pure noise on every single query — repeated thousands of times in a developer’s career.

Both habits stick once they form. Code reviews fill up with schema-qualified tables. Snippets shared between colleagues carry the prefixes. Nobody wins.

The logon trigger removes the entire problem. After it runs, every unqualified table reference resolves through the APPS synonyms, exactly like inside an EBS package — select * from mtl_system_items_b just works.

3 The R12.2.x editioning trap

On EBS 12.2.x, leaving the logon trigger out is not just inconvenient — it can return silently wrong data.

EBS 12.2 uses edition-based redefinition (EBR) to enable online patching. Many EBS tables are editioned: behind the scenes Oracle maintains an editioning view in each edition (RUN and PATCH) that filters rows to the current edition. The APPS synonyms point at the editioning view in the active edition, so queries from APPS always see exactly one version of each row.

When you bypass APPS and query the underlying schema directly — inv.mtl_system_items_b instead of apps.mtl_system_items_b — you skip the editioning view. During and after online patching cycles, the base table can contain rows for both editions, and your query will return duplicated rows. The query looks fine, the count just doubles for some products. This bug class is genuinely hard to diagnose because the duplication is data-driven and can come and go.

Setting current_schema=apps via the logon trigger ensures every unqualified reference resolves through the APPS synonyms — and therefore through the correct editioning view. It is the only safe default on 12.2.x.

4 Does current_schema=apps grant additional access?

This is the concern we hear most often from DBAs reluctant to add the logon trigger. The answer is no — alter session set current_schema does not grant any privilege whatsoever.

It is purely a name-resolution setting. It tells Oracle: when the user references an object without specifying a schema, look in this schema first. The privilege check happens afterwards, against the calling user’s actual grants. If APPS_QUERY does not have SELECT on a given object, the query fails with ORA-00942 regardless of what current_schema is set to.

In our script APPS_QUERY can read EBS data because it has SELECT ANY TABLE — not because of the logon trigger. The trigger only changes how unqualified names are resolved; it cannot turn a SELECT privilege into an UPDATE or DELETE.

If you want to be even more restrictive, replace SELECT ANY TABLE with explicit SELECT grants on the specific tables and views the user is allowed to see. The logon trigger remains valid and useful regardless — it changes nothing about what the user can or cannot access.

5 Granted privileges explained

  • connect — allows the user to log in (the CREATE SESSION privilege)
  • select any table — read access to all application tables. EBS has thousands of tables across dozens of schemas; granting per-table is impractical for a general-purpose query account
  • select any dictionary — read access to DBA_* data dictionary views, needed for metadata browsing (object lists, column definitions, indexes, etc.)
  • select_catalog_role — additional dictionary-style access provided by Oracle as a packaged role
  • select on sys.user$ — needed by some tools that read the underlying user table directly
  • execute on apps.xxen_util (conditional) — grants access to Enginatics’ utility package if installed. Used by Blitz Report SQLs for translated lookups, flexfield values, formatted dates, and similar helpers

Author: