User wants to query objects without prefix

Problem:

We have granted user DUMMY with select on all tables and views of APPS schema. But the user wants to access the tables of APPS schema without using prefix.

SQL> select count(*) from fnd_user;

ORA-00942: table or view does not exist

Fix:

Create a trigger for schema to set current_schema at session level to apps and user need not use the prefix anymore.

SQL> conn apps/apps

SQL> CREATE OR REPLACE TRIGGER dummy_logon_trg
AFTER logon ON DUMMY.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
END;
/

SQL> conn dummy/dummy

SQL> select count(*) from fnd_user;

COUNT(*)
----------
43