1. create role called apps_user
2. grant select, insert, update, delete on tablename to apps_user (for every table in apps schema);
3. create user new_user_name
4. grant connect, apps_user to new_user_name
Well this not easy APPS schema itself is composed or say have access to another 10 schema GL PO AP ( This is Oracle Applications) so APPS schema might have more than 1000 tables This approach u said seems very difficult any other suggestions
What's so difficult? Create a script off dba_tables, run the script, you're done.
set trimspool on
set pages 0
set linesize 222
select 'grant select, insert, update, delete on ' || table_name || ' to apps_user;'
<edit grants.sql to get rid of extra stuff>