Thanks for the question, Hemanth.
Asked: April 08, 2017 - 2:04 pm UTC
Last updated: April 09, 2017 - 9:11 am UTC
Version: 11gr2
Viewed 1000+ times
You Asked
QUESTION 1
------------
I have two user i.e. user1 and user2.
user1 have 1000 object. This all objects need to be accessed by user2, for that i have to grant access privilege to user2. after that i want user2 to access user1 objects without using schemaname i.e. user1 before objectname.
i can't understand how to write....Can you please help me?
QUESTION 2
-----------
I WANT TO CREATE SYNONYM FOR ALL THE OBJECTS IN MY SCHEMA DYNAMICALLY AT RUN TIME...HOW TO CREATE???
and Connor said...
"This all objects need to be accessed by user2" is different to "i want user2 to access user1 objects without using schemaname"
So for the first thing, you need to do grants. You could script it, eg:
connect user1
select 'grant insert,update,delete,select on '||object_name||' to '||:user2||';'
from user_objects
where object_type in ('TABLE','VIEW')
union all
select 'grant select on '||object_name||' to '||:user2||';'
from user_objects
where object_type in ('SEQUENCE','MATERIALIZED VIEW')
union all
select 'grant execute on '||object_name||' to '||:user2||';'
from user_objects
where object_type in ('PROCEDURE','FUNCTION','PACKAGE');
which will generate a script to do a set of grants
For the second one, you either do synonyms, or you could issue:
alter session set current_schema = user1;
and this could (for example) be done with a logon trigger for user2. If you wanted synonyms, simply copy the model in the grant script above, eg
connect user1
select 'create or replace synonym '||:user2||'.'||object_name||' for '||object_name||';'
from user_objects
where object_type in ('TABLE','VIEW','SEQUENCE','MATERIALIZED VIEW','PROCEDURE','FUNCTION','PACKAGE');
Is this answer out of date? If it is, please let us know via a Comment