Custom tables/views are not accessible via REST API with integration user in IFS10

Hi ​@seadtaydin ,

The Core Problem: How IFS Resolves Database Objects

All database objects related to IFS Applications are deployed as the Application Owner user (usually IFSAPP). IFS Community When IFSAPP runs queries, Oracle resolves object names from its own schema automatically. When your integration user runs the same queries, Oracle looks in that user's schema first — and custom tables/views usually only exist as objects (or synonyms) in IFSAPP's schema, not the integration user's.

Answering Your Specific Questions

1. Is FND_FULL alone insufficient for custom objects?

Yes, it is insufficient. Access is granted through views and PL/SQL package methods — not directly to tables. When a user is granted read access to data in a particular logical unit, grants are given to the view(s) of that logical unit. Ifs FND_FULL covers standard IFS objects, but custom tables/views created outside of the standard IFS security framework are not automatically included. They need to be explicitly granted.

2. Do custom objects need additional grants or synonyms?

Yes, both are likely needed. You need to:

sql

-- Grant SELECT on the custom table/view to the integration user
GRANT SELECT ON IFSAPP.YOUR_CUSTOM_TABLE TO YOUR_INTEGRATION_USER;

-- Create a synonym so the user can reference it without schema prefix
CREATE SYNONYM YOUR_INTEGRATION_USER.YOUR_CUSTOM_TABLE
  FOR IFSAPP.YOUR_CUSTOM_TABLE;

Without the synonym, even with the grant in place, the user must prefix every reference with IFSAPP. — and even then it may behave unexpectedly depending on how the PL/SQL views internally resolve their own dependencies.

3. Known limitation with non-IFSAPP users in REST/OData?

Yes. In IFS Cloud (and Apps 10), end users are not given direct access to tables. The possibility to query data is given to users through Oracle views, which are created as Read Only. Ifs The REST/OData layer executes as the authenticated user, not IFSAPP. If the underlying view calls helper functions or sub-views that reference unqualified object names, Oracle resolves those names in the IFSAPP schema at compile time, not at runtime for your user. So the view may execute but return no rows because of definer rights security boundaries.

4. Why does IFSAPP.custom_object return no error but empty data?

This is the classic definer's rights vs. invoker's rights problem in Oracle. Custom views or functions compiled under IFSAPP use IFSAPP's schema context. When called by another user:

• The object is found (no table does not exist error) ✅
• But row-level security, VPD policies, or context-dependent WHERE clauses inside the view may filter all rows for a non-IFSAPP session context ❌

Also check if your custom view uses Installation_SYS context functions or Security_SYS row-level filtering — these can silently return empty sets for users without the right application context initialized.

Recommended Fix Checklist

Run these checks and actions as a DBA/IFSAPP:

sql

-- 1. Check what's missing — find objects not granted to your integration user
SELECT object_name, object_type
FROM dba_objects
WHERE owner = 'IFSAPP'
  AND object_name LIKE 'YOUR_CUSTOM%'
  AND object_name NOT IN (
      SELECT table_name FROM dba_tab_privs
      WHERE grantee = 'YOUR_INTEGRATION_USER'
  );

-- 2. Grant missing objects
GRANT SELECT ON IFSAPP.YOUR_CUSTOM_VIEW TO YOUR_INTEGRATION_USER;

-- 3. Create synonyms
CREATE OR REPLACE SYNONYM YOUR_INTEGRATION_USER.YOUR_CUSTOM_VIEW
  FOR IFSAPP.YOUR_CUSTOM_VIEW;

-- 4. Also grant the custom permission set in IFS Security Admin
-- Go to: Security > Permission Sets > [your custom perm set]
-- Add the custom DB objects under the "Database Objects" tab
-- Then refresh the security cache

5. Check if the custom permission set covers the new objects

In the Permission Set form, use "Refresh all Security Objects" — this action repopulates presentation objects, database objects, activities, system privileges and IAL objects from the server. Ifs After adding custom DB object grants to a permission set, you must also Refresh Security Cache so the changes take effect for active sessions.