GRANT USER ACCESS ON SCHEMA

user needs read only access on existing schema
----------------------------------------------
select 'grant '||decode(object_type,'TABLE','select','VIEW','select','SEQUENCE','SELECT ','PROCEDURE','EXECUTE ','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||owner||'.'||object_name||' to target user' from dba_objects where OWNER='source user' and object_type not in ('INDEX','PACKAGE BODY','DATABASE LINK','LOB');

user needs full access on existing schema
-----------------------------------------
select 'grant '||decode(object_type,'TABLE','select,insert,delete,update ','VIEW','select,insert,delete,update ','SEQUENCE','SELECT ','PROCEDURE','EXECUTE ','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||owner||'.'||object_name||' to targetuser' from dba_objects where OWNER='source user' and object_type not in
('INDEX','PACKAGE BODY','DATABASE LINK','LOB');

Related:
-------
Running the SQL*Plus script below (substituting &Owner and &NewUser) will produce a listing of all the permissions to allow the New User to access all the objects owned by OWNER. Review the output of the script and then run it to Grant the new permissions to NewUser.

Set pagesize 0
define OWNER=
define NEWUSER=

Spool new_grants.txt

Select
decode(OBJECT_TYPE,
'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON '||'&OWNER'||'.',
'VIEW','GRANT SELECT ON '||'&OWNER'||'.',
'SEQUENCE','GRANT SELECT ON '||'&OWNER'||'.',
'PROCEDURE','GRANT EXECUTE ON '||'&OWNER'||'.',
'PACKAGE','GRANT EXECUTE ON '||'&OWNER'||'.',
'FUNCTION','GRANT EXECUTE ON '||'&OWNER'||'.' )||object_name||' TO &NewUser;'
From USER_OBJECTS where OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')
Order By OBJECT_TYPE;

Spool Off;
exit